May 30, 2008 at 7:16 am
Some of the Tempdb log files on SQL 2000 database servers I support have, one at a time (there are now 5) over the last few weeks, started to increase in size and never truncate causing the entire disk to fill. I have scheduled jobs to check and truncate them periodically. But how could this be happening, isn't Tempdb supposed to free space continuously? There are no persistent locks or long running tasks that may be holding temporary tables open. Are there any SQL monitoring tasks that maybe responsible?
May 30, 2008 at 8:01 am
Well here are some of my thoughts on the issue and you might already be aware of the same....
TEMP DB is RECREATED everytime you recycle SQLServer Services or BOX.
This will redo all allocation maps etc and start fresh.
Best Practise is to Keep the LOG files on Diff Volumes.( if you can afford to)
Many reasons for the TEMPDB to Grow:
..incomplete transactions/uncommitted trans..
.. frequent use of DBCC on large DB's
..if Excessive sorting in Queries
Or Try to SHRINK the LOG Regularly(Manually), if it grows beyond Limits(your planned limits)
DBCC SHRINKFILE or DBCC SHRINKDB
Here are several commands to check transaction in the tempdb
DBCC OPENTRAN('tempdb')
then check if there is enough space in TEMPDB
EXEC sp_spaceused
Check Recovery model for TEMPDB and see if it is set to SIMPLE.
Maninder
www.dbanation.com
May 30, 2008 at 9:22 am
Thanks Mani,
- There's no open trans on any of the tempdbs
- All are Simple recovery model.
- I do run an automated check + DBCC shrinkdatabase ('TEMPDB', 10) when the
tempdb log reaches 5Gbytes and write to a log for each check.
How does excessive use of DBCC cause Tempdb log file size issues? What do class as excessive?
A
May 30, 2008 at 9:45 am
Clark,
Database files won't get shrinked by themselves unless Auto Shrink option is enabled(which normally shldn't be).
Have you tried other options like compress pages and other available under enterprise manager shrink window.
Manu
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply