Tell me more ×
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.
up vote 1 down vote favorite

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
3  
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

1 Answer

up vote -1 down vote 
DBCC FREESYSTEMCACHE('ALL')

After executing the above command, execute the shrink file operation

use tempdb
go
DBCC SHRINKFILE (TEMPDEV,1024)
go

The result of the FREESYSTEMCACHE could have a significant performance impact, I'd hate to see that run against a production system during the day as a result of trying to modify tempdb size. – SQLRockstar

share|improve this answer
4  
Won't that clear the plan cache? The result of the FREESYSTEMCACHE could have a significant performance impact, I'd hate to see that run against a production system during the day as a result of trying to modify tempdb size. – SQLRockstar Jul 9 '12 at 16:23
hi @sqlrockstar, yes i hate to see that run against any sql system :D, but this was the question .... :D – ElDoroCapitano Jul 9 '12 at 17:40
Sure thing, just felt it was worth noting here in case someone decided to run it blindly. – SQLRockstar Jul 9 '12 at 21:06

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.