5

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).

Improve this question
5
  • 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, 2012 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, 2012 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, 2012 at 19:24
  • 1
    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, 2012 at 19:25
  • 1
    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, 2014 at 16:07

2 Answers 2

Reset to default
5

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.

Improve this answer
1
  • Using the original authors command DBCC SHRINKFILE (name = 'tempdev', size = 5000) and scheduling at night (low activity) this corrected my issue quite nicely.
    – DarrenMB
    Aug 20, 2014 at 1:30
-2

Via @SomeGuy and http://calyansql.blogspot.com.by/2012/04/shrink-tempdb-data-file-without-sql.html:

DBCC FREEPROCCACHE
GO
DBCC DROPCLEANBUFFERS
go
DBCC FREESYSTEMCACHE ('ALL')
GO
DBCC FREESESSIONCACHE
GO
DBCC SHRINKDATABASE(tempdb, 5) 
GO

See also https://support.microsoft.com/en-us/help/307487/how-to-shrink-the-tempdb-database-in-sql-server.

Improve this answer
2
  • 2
    You probably don't want to run this on production. Dropping all caches will have a significant impact on performance for a while. You should at least include a warning
    – Tom V
    Apr 3, 2018 at 9:29
  • Nor would I ever use SHRINKDATABASE, since you can't control the degree to which it will shrink files at different rates; using SHRINKFILE with specific target sizes is a much better idea.
    – BradC
    Apr 3, 2018 at 13:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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