How to Shrink TempDB in SQL 2005

Introduction

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, but I am going to show you how.

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

I got back the following:

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.”…grrr. 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.

I think I got lucky that the shrink worked on the first try. There will certainly be times when you have to try freeing the procedure cache and shrinking multiple times to get a file to shrink, but eventually it will get the job done.

This entry was posted in File Management and tagged , , , , . Bookmark the permalink.

7 Responses to How to Shrink TempDB in SQL 2005

  1. Vishu says:

    Is it safe to run DBCC FreeProcCache on Production systems ? Would there be any negative performance impact after cleaning cache?

    • David Levy says:

      I have never had a problem with running DBCC FREEPROCCACHE on a production server even in the middle of the day. That said, my preference is to do any maintainance work in times of very low activity.

  2. Uday says:

    Hi Thanks for your Information & Iam Just sharing KB article for shrinking TEMPDB

    http://support.microsoft.com/kb/307487

  3. eyechart says:

    you should never shrink the tempdb like this.

    http://support.microsoft.com/kb/307487/en-us

    doing so can lead to corruption. there is a reason the tempdb has grown to the size it has, add the appropriate space (use new drive letters, mountpoints, whatever) to isolate tempdb and templog from other databases.

    • David Levy says:

      I have read that article several times but have not found anywhere in it where it says never to shrink TempDB.

      Is shrinking TempDB my first choice? As I stated in my post: No, but it was the choice I had.

      You have to make the choices that are right for your situation, I can only tell you what has worked for me in that past.

  4. karthik says:

    How to avoid temp db shrink.Because weeekly once we are doing it with downtime….Please assist.

    • David Levy says:

      Ouch! That is way too much. This is the sort of thing that you might do once in a very great while because you can’t restart SQL to fix a problem.

      The plan here would be to monitor to see what is using the space and address it accordingly. If nothing can be done to keep TempDB from growing then more disk space will have to be added.

      Here is a great write-up to help figure out what is going on: http://technet.microsoft.com/en-us/library/cc966545.aspx

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>