I just wanted to post this as a “lessons learned” for me, a very green DBA, as I wish I would have known this before I ran into an issue in production. Yes, production. I hear all the people screaming “You Monster! How could you!” Believe me, I was in the same boat. Thankfully, many other people have done the same!
So, let us get to the point. There will come a time when someone runs a query that bloats your tempdb (and any of the separate data files) and you begin to receive alerts. For some people, the first response should ALWAYS be to just extend the drive, then look at why your tempdb expanded to fill the drive.
However, some of us may not be as lucky and have to find a way to make space. In this case, we shrink-responsibly!
My first move was to make sure I could even shrink the files.
USE [tempdb]; GO DBCC SHRINKFILE (Tempdev, 20480);----remember this number is megabytes. Make sure to be exact. GO DBCC SHRINKFILE (Temp02, 20480); GO
If all goes well, you should be good right? Some people suggest that you do not need to restart SQL Service. However, in my case I needed to as I had another piece to the problem I have not touched on yet. Regardless, I would strongly suggest if you have already scheduled the downtime to shrink the tempdb files, it would not hurt for the restart.
BUT! My problem was not a rouge transaction that expanded the tempdb files to max out the drive. No, the problem was my stupidity in a previous scheduled downtime to add more tempdb files to match the rest of our database servers. In doing so, I added TOO MANY. Yes…TOO many. So I was going back and fixing my mistake. In this case, the SQL gods were making it a point to teach me a lesson and here is why.
I had to shrink all of the files down, and delete the extra one. So I shrank the files, and went to delete the extra tempdb file. In order to do so, a file needs to be “empty” in order to be removed. So….
DBCC SHRINKFILE (LogicalFilename, EMPTYFILE); GO
And then the problem hit me:
DBCC SHRINKFILE: Page 3|44101 could not be moved because it is a work table page. Msg 2555, Level 16, State 1, Line 1
There are work tables present. Of course!
Now, you could try and restart the instance to see if the worktables magically go away, but I want to reduce the amount of times we restart the service. Therefore, I took to the community and came across some suggestions about clearing various caches(sp?). Let’s give it a shot!
DBCC DROPCLEANBUFFERS; GO DBCC FREEPROCCACHE; GO DBCC FREESESSIONCACHE; GO DBCC FREESYSTEMCACHE ( 'ALL'); GO
But life isn’t easy right? Why would it be! I tried again to empty the file and it did not work. Thankfully, I have a FANTASTIC mentor, who suggested “Why don’t you just try to shrink it down to 1MB, do a restart, and delete it.” At this point, I was happy for suggestions. So….
DBCC SHRINKFILE (Temp05, 1); GO
Then the restart of the service. So far so good, as the file DID shrink to close to 1MB. Let’s try to delete via SSMS…and SUCCESS! I could not believe it. I spent two nights, during off hours, to try and fix this and the answer was as simple as just shrinking the file to 1mb, and delete.
Moral of the story: Don’t shrink, add space. Because, should you ever really shrink your SQL Server database? Andy has the answer for you