Shrinking tempdb without restarting SQL Server

Ok, so even if you’re a seasoned veteran T-SQL coder, at some time you will write a query that runs away and supersizes the tempdb database. This, in turn, might fill up your disk and cause other server-related problems for you. At that point, you may find out the hard way that shrinking tempdb isn’t like shrinking any other database.

Here are some tricks that I’ve tried successfully – but bear in mind that your mileage may vary.

Tempdb stores temporary tables as well as a lot of temporary (cached) information used to speed up queries and stored procedures. For the best chances in shrinking tempdb, we’re going to clear these different caches (except for the temp tables, which you should drop manually).

First off, the easy way out

It’s worth mentioning. If you’re not running a production-like environment, your best bet is to restart the SQL Server service. This will return tempdb to its default size, and you won’t have to worry about all the potential pitfalls of this article. But since you’re reading this, chances are you can’t just restart the server. So here goes:

Warning: These operations remove all kinds of caches, which will impact server performance to some degree until they’ve been rebuilt by the SQL Server. Don’t do this stuff unless absolutely neccessary.


Clears the clean buffers. This will flush cached indexes and data pages. You may want to run a CHECKPOINT command first, in order to flush everything to disk.



Clears the procedure cache, which may free up some space in tempdb, although at the expense of your cached execution plans, which will need to be rebuilt the next time. This means that ad-hoc queries and stored procedures will have to recompile the next time you run them. Although this happens automatically, you may notice a significant performance decrease the first few times you run your procedures.



This operation is similar to FREEPROCCACHE, except it affects other types of caches.



Flushes the distributed query connection cache. This has to do with distributed queries (queries between servers), but I’m really not sure how much space they actually take up in tempdb.


.. and finally, DBCC SHRINKFILE

DBCC SHRINKFILE is the same tool used to shrink any database file, in tempdb or other databases. This is the step that actually frees the unallocated space from the database file.

Warning: Make sure you don’t have any open transactions when running DBCC SHRINKFILE. Open transactions may cause the DBCC operation to fail, and possibly corrupt your tempdb!

DBCC SHRINKFILE (TEMPDEV, 20480);   --- New file size in MB

Don’t set the new size too low! Make a realistic estimate of the largest “normal” size that tempdb will assume during normal day-to-day operation.

That’s it. If everything works the way it should, you should now be able to verify the new size of tempdb.

A word about shrinking database files

Best practice is to try to minimize the use of file or database shrinking as much as possible. Whenever you shrink a database file and it re-grows later on, you are potentially creating fragmentation on your physical storage medium. This is because the sectors that the file used to occupy may now very well be occupied by other information (just a few bytes are enough). When SQL Server wants to grow that database file, the newly added portion of the file will need to be placed elsewhere on the disk, thus creating fragmentation.

The number one mortal sin in this context is “autoshrink“, because it may very well add to the drive fragmentation every time it runs, which could be very frequently.

As a rule of thumb, never ever autoshrink a database. And try to be very restrictive when it comes to shrinking databases or files in general, unless it’s a one-off operation to fix the aftermath of a runaway query.

11 thoughts on “Shrinking tempdb without restarting SQL Server

  1. Pingback: Mobilo » Blog Archive » DBCC SHRINKFILE: Page 1:123456 could not be moved because it is a work file page.

  2. Pingback: SQL Server 2005 TempDB Distilled | SQL Jana

    • No, like the post says, “Best practice is to try to minimize the use of file or database shrinking as much as possible. Whenever you shrink a database file and it re-grows later on, you are potentially creating fragmentation”

  3. Hi, this works like a charm, but i still have a doubt, How does the below queries helps in shrinking the tempdb file when these cache’s exist in the memory?. Or am i wrong. How are these caches and TEMPDB related?

    • You’d have to google the specifics, but generally speaking these buffers and caches can allocate soace in tempdb and prevent it from shrinking.
      Remember that a single log record or page in tempdb could hold up the shrink. Therefore the brute force approach.

      • Hi Daniel,
        Could you please confirm, How to find that holding page file in tempdb that causing to stop shrink db. please advise me.

        • I’m afraid that finding a specific page that is blocking tempdb from shrinking is a bit out of my depth. Also, there’s a difference between how a database (the .mdf files) and the log (the .ldf files) shrinks.

          Your best bet is to read up on this article:

          You should also check for open transactions to identify what process might be holding an open transaction. Try executing “sp_who2”, or try my “sessions and locks” script, which is available from the download page.

  4. Thanks Dan,
    It worked well for me. I have been trying the various DBCC shrink commands but unable to shrink it other than restarting the SQL instance.

Let me hear your thoughts!

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

You are commenting using your 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