I am fortunate to work on different challenges every week for SQL Server Performance Tuning. The other day I ended up a very interesting situation while working with Comprehensive Database Performance Health Check, with TempDB files. Let us understand the performance tuning story.
Recently customer hired me to help them performance tuning issue. While we went through various health check items, we realized that they have a performance issue on one of the drive. The best possible solution was to move one of their busy databases to another drive. Now moving the database to another drive was not possible because the other drive had 32 tempdb files.
After careful investigation, we realized that over 97% of the TempDB was empty and they were just occupying big space as they were pre-sized initially. Additionally, looking at the 32 TempDB was absolutely not needed.
Lots of people think that they need to have one TempDB per Core or CPU thread. I have no idea where this started but it is not accurate all the time. The reality is that we need to have TempDB files as many as needed by our workload and not one per core/CPU thread.
I will write a detail blog post in the future, let us get back on the original topic of this blog about how to remove the TempDB.
Removing Extra TempDB Files
If you want to remove the TempDB files, you can use the following script. Please note that in SQL Server, you can’t remove any file if it is not empty. This is the reason, I am emptying the TempDB File first and right after that, I am removing the TempDB file. You have to run the entire script in a single batch.
USE [tempdb] GO DBCC SHRINKFILE (N'tempdev02', EMPTYFILE) GO ALTER DATABASE [tempdb] REMOVE FILE [tempdev02] GO
This worked perfectly fine for my client and I am confident that if you are facing any error while removing the TempDB file, you may also use the above script to remove the files.
Reference: Pinal Dave (https://blog.sqlauthority.com)