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)
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.
DBCC SHRINKFILE (TEMPDEV, 1024)
When the users were running only Shrinkfile, they were not able to shrink the database. However, when they ran DROPCLEANBUFFERS it worked just fine.”
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.