SQL SERVER – How to Shrink tempDB Database?

Just yesterday, I received an email from a client who had hired me last year for Comprehensive Database Performance Health Check. While I worked with them last year, they were struggling with the performance of the server. We scientifically and strategically worked on their server and got them multi-fold performance. Yesterday’s email confirmed me to that after one year still they are experiencing the amazing performance as they now know the tricks to tune their server themselves. In the email yesterday they had a small question about how to shrink tempdb database.

SQL SERVER - How to Shrink tempDB Database? shrink-tempdb-800x237

Before we continue this blog post, please read my following six blog posts which talks about why Shrinking is not a good idea in general and you may end up with performance problem after shrinking the database.

Now that there is enough disclaimers about Shrinking, let us see how we can shrink the tempDB without restarting it.

USE TEMPDB
GO
DBCC SHRINKFILE (tempdev, '100')
GO
DBCC SHRINKFILE (templog, '100')
GO

The reason, I use Shrinkfile instead of Shrinkdatabase is very simple. There are quite a few limitations (related to the how much you can shrink your database whereas shrinking the file is much more relaxed compared to it. If you have multiple TempDB files, you will have to repeat the command multiple times giving a different filename.

Please note that you can’t make the database smaller than the size of the model database. This means the smallest size you can provide for your file has to be larger than what you have specified in the model database. If due to any reason, you can’t shrink your tempdb files, please check your model database as well. There is a good chance that a higher value in your Model database may be preventing your tempdb files to shrink.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Shrinking Database, SQL Scripts, SQL Server, SQL Server DBCC, SQL TempDB
Previous Post
SQL SERVER – Show Primary Key for a Single Table – sp_pkeys
Next Post
SQL SERVER – Building Date and Time with DATETIMEFROMPARTS()

Related Posts

2 Comments. Leave new

  • Just like that? Wow! So shrinikg tempdb is no different then shrinking a regular database? Then why write a whole article about how to shrink tempdb without restart?

    And how about that piece of advice?
    “Now, let us see how we can shrink the TempDB database.

    CHECKPOINT
    GO
    DBCC FREEPROCCACHE
    GO
    DBCC SHRINKFILE (TEMPDEV, 1024)
    GO
    When the users were running only Shrinkfile, they were not able to shrink the database. However, when they ran DROPCLEANBUFFERS it worked just fine.”

    Reply
    • Thanks Alexander for your kind note.

      The reason, this blog post is because I get lots of emails with this question. This blog is my diary of consulting engagement.

      Reply

Leave a Reply