TempDB Issues – Shrinking TempDB in SQL Server 2005


From time to time you find yourself needing to shrink some space out of TempDB. Shrinking database files is never my first choice but sometimes it is the best I have. Many people think that you cannot shrink TempDB in SQL 2005

Why would I need to shrink TempDB? 

Yesterday afternoon my pager started going crazy because an Ad-Hoc query that needed some tuning filled TempDB on a server. Luckily, the user only impacted their own query so it was easy to quickly identify them and work with the right people to get the query rewritten. 

Once the immediate problem was resolved there had to be some cleanup. On this server, TempDB has 32 files (1 per processor) all on the same disk. The full database condition caused all kinds of alerts in our monitoring tools, from drive space alerts to too few growths remaining. There were 3 possible solutions to quiet the alerts: 

1.       Reboot – There is never a good time to reboot a production server 

2.       Turn off the Alerts – Not really an option. My preference would be for increasing the sensitivity 

3.       Shrink TempDB – Not a great option, but the best of the 3 

Shrinking TempDB 

Once we had decided that we would go ahead and shrink the files in TempDB it seemed like the hard part was done, but after running the following command: 

USE [tempdb] 

GO 

DBCC SHRINKFILE (N’tempdev’ , 5000) 

GO 

Result :

DBCC SHRINKFILE: Page 1:878039 could not be moved because it is a work file page. 

DbId   FileId      CurrentSize MinimumSize UsedPages   EstimatedPages 

—— ———– ———– ———– ———– ————– 

2      1           878040      640000      4672        4672 

(1 row(s) affected) 

DBCC execution completed. If DBCC printed error messages, contact your system administrator. 

“Page could not be moved because it is a work file page.”. This is a new thing in SQL 2005 caused by the caching that is done in TempDB. I am not going to try to explain here how objects are cached in TempDB, but Kalen Delaney’s Inside Sql Server Series is a great place to learn about it if you are interested (http://www.insidesqlserver.com/books.html).  What is important is that the cached objects are tied to a query plan and that by freeing the procedure cache you can make those objects go away, allowing you to shrink your files. 

Trying again: 

DBCC FREEPROCCACHE 

GO 

USE [tempdb] 

GO 

DBCC SHRINKFILE (N’tempdev’ , 5000) 

GO 

This time it worked: 

DBCC execution completed. If DBCC printed error messages, contact your system administrator. 

DbId   FileId      CurrentSize MinimumSize UsedPages   EstimatedPages 

—— ———– ———– ———– ———– ————– 

2      1           640000      640000      264         264 

(1 row(s) affected) 

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Advertisements

5 responses

  1. danieladeniji | Reply

    Thanks. Bailed me out of a big jam today. Saw similar entries on the .Net, but none as well written, nor concise, as yours.

    Definitely, a nice to have.

    1. Thank you..
      Happy to see that my script helped you from a big trouble..

      1. Yes, Thanks much for placing it in the public domain.

    2. Thanks for the solution…It’s a perfect way to handle the tampdb issues.Thanks for your great blogs. Keep on the good work!

      1. Thanks for replying on TempDB issues..

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: