web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / Problem with TempDB si...
Microsoft Dynamics AX (Archived)

Problem with TempDB size growing

(0) ShareShare
ReportReport
Posted on by Community Member

 

We are using SQL 2005.  Generally once a month the Tempdb will grow from 10 meg to over 260 gig in size.  Other wise it stays around 1 gig or samller in size.  The tempdb.mdf is over 260 gig, templog.ldf is under 1 meg in size. 

I am getting the following error:

‘Insufficient space in tempdb to hold row versions.  Need to shrink the version store to free up some space in tempdb. Transaction (id=32588619 xsn=22898859 spid=92 elapsed_time=9494) has been marked as victim and it will be rolled back if it accesses the version store. If the problem persists, the likely cause is improperly sized tempdb or long running transactions. Please refer to BOL on how to configure tempdb for versioning.’

 

I am unable to shrink the TempDB , even though  SQL say’s that 99% Available free space.

 

DBCC OPENTRAN('tempdb')

Gives me a message of  â€˜No active open transactions.’

 

USE tempdb

GO

SELECT name

    FROM tempdb..sysobjects

 

SELECT OBJECT_NAME(id), rowcnt

    FROM tempdb..sysindexes

    WHERE OBJECT_NAME(id) LIKE '#%'

    ORDER BY rowcnt DESC

Results of rowcnt = 0

 

The only way to solve the problem is to stop SQL Server (will be trying this tonight) or reboot the computer. Then tempDb shrinks to less then 1meg in size.

How do I shrink the TempDB without taking AX down and/or how to stop the growing problem?

Thanks

Abraham Z.

*This post is locked for comments

I have the same question (0)
  • ac604207e7bf49ecb72cb695c8b5bdb5 Profile Picture
    ac604207e7bf49ecb72... 250 on at

    You could try shrinkfile or shrinkdatabase if there is no other activity in the tempdb, but otherwise restarting the SQL Server seems to be the only other option:

    dbcc shrinkfile (tempdb, 'target size in MB')
    dbcc shrinkdatabase (tempdb, 'target percent')

    How to shrink the tempdb database in SQL Server

     

    Thanks for this information.  I'll pay more attention to Tempdb from now on. :)

  • Verified answer
    Community Member Profile Picture
    Community Member on at

    The problem is I can not Shrink the TempDB without first taking down SQL.  It may work just fine for over a month then start to grow, so looking at it every day is tedious.  I have put a upper limit to the tempDB of a 100Gig for the DB and 100gig for the log files.  See if that works:)

    Thanks for your thoughts

    Abraham Z.

  • Community Member Profile Picture
    Community Member on at

    Abraham - Is this still a problem for you?  We are experincing the same issue.  I'd like to know what is causing the tempdb to grow so much.  This was not an issue for us until after I completed an executable upgrade of NAV 4.0 SP3 from an older build to a newer build.   I don't want to be bothered with having to shutdown and restart SQL services manually every night in order to shrink the tempdb.

  • Community Member Profile Picture
    Community Member on at

    I think it was a runaway process.  It happens about once a quarter.  To solve the problem i limited the TempDB  to 70% of the hard drive.  When I start getting the error message I allow the TempDB to expand up to 95% of the hard drive.  This gives me time to  shutdown and restart SQL services manually  at night when no one is working.  After the reset I set the TempDB max size to about 70% of the hard drive.  this way the system stays up during working hours:)

    The TempDB is on its own drive.  

    Hope this helps

  • Community Member Profile Picture
    Community Member on at

    Thanks, Abraham.

    It turns out our issue is that the new build of NAV 4. SP3 that I installed is growing the tempdb at night during an automated run of a NAV backup using an Expandit-IT script kicked off by Windows Task Scheduler.   The backup does not grow the tempdb when run manually.

    I was able to move the tempdb to a larger partition.  I think that we are ok now.

    Here's how I moved tempdb from C: to H:  (using SQL 2000):

    Open SQL Query Analyzer and connect to the NAV server. Enter and run the following script:

    USE Tempdb

    GO

    EXEC sp_helpfile

    GO

    Write down NAME and FILENAME for database and log files and enter and run the following script using SQL Query Analyzer:

    USE master

    ALTER DATABASE Tempdb MODIFY FILE

    (NAME = tempdev, FILENAME = 'h:\Program Files\Microsoft SQL Server\MSSQL\data\tempdb.mdf')

    GO

    ALTER DATABASE Tempdb MODIFY FILE

    (NAME = tempLOG, FILENAME = 'h:\Program Files\Microsoft SQL Server\MSSQL\data\templog.ldf)'

    GO

    Delete old tempdb.mdf and templog.ldf files (or move them until you are sure it is safe to delete them)

    Stop and then restart the MSSQLSERVER and SQLSERVERAGENT services

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

#1
Martin Dráb Profile Picture

Martin Dráb 4 Most Valuable Professional

#1
Priya_K Profile Picture

Priya_K 4

#3
MyDynamicsNAV Profile Picture

MyDynamicsNAV 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans