Thursday, February 2, 2012

SQL Server 2005/2008 : How to shrink TEMPDB?

On certain occasions you might see a necessity to shrink the data files for TEMPDB.
Before we proceed to answer the question, please investigate why do you want to shrink TEMPDB.

Firstly, it is never a good idea to shrink TEMPDB. There must be a reason/query/process that needs TEMPDB to be this large so if you shrink the TEMPDB then there is every chance that it might grow to the same size again.

So I would check the current open transaction in TEMPDB using the following command


This should give you the SPID of the transaction and you can use that to find the query text. Lets say that the SPID of the current active transaction is 65 then run the following command and get the info.


Take a look at the query and see it is going to get executed again. If yes, then shrinking the TEMPDB might not be the best course of action.

At this point if you still want to shrink the tempdb then try the dbcc shrinkfile command first. If that does not do the trick and you are running out of disk space then the best way to reduce the size of tempdb is to RESTART SQL Server instance. 
This will immediately reduce the size of TEMPDB. I would choose this action rather than running out of disk space and causing other problems with the database. 

No comments:

Post a Comment