Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

So ideally you want to pre-size your TempDB data and log files appropriately so that this isn't a concern, but sometimes a rogue developer runs a crazy huge query on a production server during work hours, causing the TempDB data files to blow up huge.

If TempDB was literally the only thing on the drive, then I could probably just leave it like that, but on some servers I have several SQL instances that all share the same TempDB drive.

So how can I shrink these TempDB data files without restarting the instance?

I normally try:

DBCC SHRINKFILE (name = 'tempdev', size = 5000)

This worked fairly consistently in SQL 2000 and 2005 (assuming the actual tempdb activity had tapered off), but seems to not work very often in 2008 (on my current server, it only worked on 1 of 4 data files, the others continue to remain 2-3x larger).

share|improve this question
4  
Have you seen this MSDN whitepaper? One of the things the article indicates is tempdb needs to have NO activity during the shrink. –  JNK Feb 16 '12 at 18:40
    
@JNK, it seems to be warning that you'll get certain consistency errors when you try to shrink TempDB while it is in use. But I never see those errors, the shrink simply completes successfully, leaving the file much larger than I need it to be. –  BradC Feb 16 '12 at 19:11
    
I'm assuming it shrinks it as far as it can before it runs into a page in use, but its frustrating that it doesn't seem to work the same as shrinking a busy log (it doesn't appear to round-robin back to the beginning of the file even after you shrink it as far as it can go) –  BradC Feb 16 '12 at 19:24
    
Well it's different than a log file. It's got objects in it that need to stay a certain size and/or are locked. –  JNK Feb 16 '12 at 19:25
    
I had a similar problem. TempDB wouldn't shrink no matter what until I ran a few steps as seen here: calyansql.blogspot.com/2012/04/… –  SomeGuy Jun 17 at 16:07

1 Answer 1

You've got two different questions in here:

Q: Sometimes a rogue developer runs a crazy huge query on a production server during work hours, causing the TempDB data files to blow up huge.

A: When that happens, all of the TempDB data files will grow roughly equally, so you won't have to worry about shrinking specific ones. Frankly, you don't want to shrink them - if you've got drive space set aside for TempDB, just leave these files in place. Why keep re-fighting the same battle? You'll have a rogue developer run another query in a few weeks. Just leave this in place. You don't get bonused based off empty drive space.

Now, having said that, if you've got TempDB's data and log files on the same volume as your user databases (or heaven forbid, the boot drive), then that's the real root cause, and you need to fix that. Even if you don't put them on separate spindles, TempDB should be on a separate logical volume to mitigate this exact problem. When it fills up, it fills up - but it doesn't take user databases (or the entire server, in the event of a full C drive) offline.

Q: (DBCC SHRINKFILE) seems to not work very often in 2008

It's more a function of how SQL Server relies more and more on TempDB in each release. When something's active in TempDB, you can't move its data around, and each new version of SQL Server works more in TempDB. For example, when you enable Read Committed Snapshot Isolation, the version store it uses lives in TempDB. When you use AlwaysOn Availability Groups, it tracks user database statistics in TempDB too. This is just another reason why you set aside a logical volume for TempDB, size the data files to fill it up, and then walk away - your work here is done, and don't try to shrink those files.

share|improve this answer
    
Using the original authors command DBCC SHRINKFILE (name = 'tempdev', size = 5000) and scheduling at night (low activity) this corrected my issue quite nicely. –  DarrenMB 21 hours ago

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.