Shrinking tempdb no longer prohibited

For the longest time the guidance around tempdb is that if you shrink it on a live system then it could cause tempdb corruption.

A few months ago I was discussing this with my good friend Bob Ward from Product Support and neither of us could remember the last time we’d seen a case of tempdb corruption that had been caused by shrinking. So we both did some investigations, including looking through the internal bug databases, to find the root cause of the long-running advice.

The bottom line is that tempdb corruption hasn’t been a problem with shrink since early builds of SQL Server 2000. There was also some extensive testing done to verify this.

As such, the KB article that discusses shrinking tempdb has been updated and I got notification last night from the author that it’s been published.

KB 307487 (How to shrink the tempdb database in SQL Server) now explains that even though you may see messages from shrink that look like corruption, they’re not.

Remember though, shrinking should be a rare operation, whether data or log file shrinking – and never a regular operation.

Enjoy!

6 thoughts on “Shrinking tempdb no longer prohibited

  1. Thank you for finally dispelling this myth. Since 2005 I’ve been giving the advice that “the experts say it can cause corruption”…” but I’ve not seen it since…. “. Advise like that sounded a little woolly, and I’m glad I can give more specific advice. My preference is for clients to pre-size tempdb to the drive/LUN capacity in the first place, but obviously not always possible in certain configurations. Thanks again for sharing.

  2. I have a shrink tempdb job which runs every hour on sql server 2000 system. The job is failing with the errors:
    Server: Msg 845, Level 17, State 1, Line 1
    Time-out occurred while waiting for buffer latch type 4 for page (1:770952), database ID 2.
    Server: Msg 3140, Level 16, State 1, Line 1
    Could not adjust the space allocation for file ‘tempdev’.
    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

    Could you help with this?

    1. Nope. Firstly, you shouldn’t be shrinking tempdb regularly – leave it at the size it needs to be. Especially on 2000, where there’s no instant file initialization. Secondly, shrinking tempdb on 2000 can still cause corruption.

  3. Hi Paul

    Thanks for article(s).

    I have an issue in my production box.

    I am getting below error message while trying to shrink tempdb

    Could not cleanup worktable IAM chains to allow shrink or remove file operation. Please try again when tempdb is idle.
    DBCC execution completed. If DBCC printed error messages, contact your system administrator. (Microsoft SQL Server, Error: 5054)

    FYI:- Nothing is running tempdb now. And 99% free space on all tempdb datafiles

    How to shrink tempdb without restart.

    1. You can’t in your case. There are some cases where shrink cannot move certain page types and you have no choice but to perform a restart if you need to shrink tempdb. The ability to shrink tempdb without a restart isn’t guaranteed.

Leave a Reply

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

Other articles

Some thoughts on courage

(This is also the Ponderings – editorial – in today’s SQLskills newsletter.) I want to start out this post by sincerely thanking everyone who emailed

Explore

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.