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.

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.

• SQL SERVER – SHRINKDATABASE For Every Database in the SQL Server
• SQL SERVER – What the Business Says Is Not What the Business Wants
• SQL SERVER – Shrinking Database is Bad – Increases Fragmentation – Reduces Performance
• SQL SERVER – Database Worst Practices – New Town and New Job and New Disasters
• SQL SERVER – SHRINKFILE and TRUNCATE Log File in SQL Server
• SQL SERVER – Shrinking NDF and MDF Files – Readers’ Opinion

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)