I recently came across a server where
tempdb was breaking all the rules.
- Data files were all different sizes. I want all data files to be the same size so that proportional fill uses them evenly.
- There were 10
tempdbfiles, with 8 files on a dedicated disk, and 2 on a slower disk shared with other data files. And free space was sky high, so the extra files aren’t needed anymore! (They were probably added to fix a short-term emergency, and never cleaned up.)
- The log file was tiny, and didn’t have room to grow. For a 500GB tempdb, the log file was only 4GB!
There are three problems I’ve got to fix. I need to (1) remove those two extra files, (2) grow the
tempdb log file, and (3) even out the size of the data files (and shrink them a little to make room for the larger log file. We’re going to tackle these in the reverse order than I listed them–partially out of necessity, and partially because it’s going to be easier.
1. Resize the data files
Have you ever tried to shrink
tempdb data files? Yeah–it sucks. They don’t like to shrink. One of the things that makes
tempdb special is it doesn’t like to shrink. (Which honestly is usually a good thing–why are you shrinking your database anyway?) SQL Server won’t move a page that contains an internal worktable object, so on a production server there’s nearly always some immovable page in tempdb.
The most effective way to shrink
tempdb is to ensure the size metadata is set properly, then restart the SQL Server instance. Yeah, that means downtime, but if you can afford the downtime to restart the service, it’s the best option.
After looking at current utilization, I decided that I want the eight data files to all be 40GB each, with 1GB autogrowth. This is smaller than the current file sizes. I am going to run this right now… and it’s not going to do anything:
USE master GO ALTER DATABASE tempdb MODIFY FILE ( NAME = N'tempdev', SIZE = 40GB , FILEGROWTH = 1GB ); ALTER DATABASE tempdb MODIFY FILE ( NAME = N'temp2', SIZE = 40GB , FILEGROWTH = 1GB ); ALTER DATABASE tempdb MODIFY FILE ( NAME = N'temp3', SIZE = 40GB , FILEGROWTH = 1GB ); ALTER DATABASE tempdb MODIFY FILE ( NAME = N'temp4', SIZE = 40GB , FILEGROWTH = 1GB ); ALTER DATABASE tempdb MODIFY FILE ( NAME = N'temp5', SIZE = 40GB , FILEGROWTH = 1GB ); ALTER DATABASE tempdb MODIFY FILE ( NAME = N'temp6', SIZE = 40GB , FILEGROWTH = 1GB ); ALTER DATABASE tempdb MODIFY FILE ( NAME = N'temp7', SIZE = 40GB , FILEGROWTH = 1GB ); ALTER DATABASE tempdb MODIFY FILE ( NAME = N'temp8', SIZE = 40GB , FILEGROWTH = 1GB ); GO
ALTER DATABASE tempdb MODIFY FILE with a smaller size will only adjust the size metadata stored in the
master database, and this won’t take effect until the next time the service starts up, when SQL Server will fix up file sizes. There’s no need to wait for a maintenance window–in fact, you definitely should not wait. You should run those
ALTER DATABASE tempdb MODIFY FILE statements right now. If there’s a restart between now and your next maintenance window, then the change will take effect on that (unscheduled) restart!
2. Grow the
tempdb log file
tempdb disk is full, so I can’t do this yet. I need to shrink those data files before I can grow the log file, and I can’t shrink the data files until I restart the service.
Can I do the
ALTER DATABASE tempdb MODIFY FILE preemptively like I did for the data files, and have it take effect on restart? No, unfortunately not. When you modify a
tempdb file to shrink, it won’t take effect until restart, but if you modify a
tempdb file to grow, SQL Server will grow it immediately. Since I don’t have space to grow the file right now, that modify will fail.
Hot damn, SQL Agent has a scheduling option to run a job when Agent starts. I can schedule this job to run when Agent starts… which might happen independently, but will definitely happen on the next service restart.
I can just schedule a job with a single step, and that will ensure that it runs on restart & I don’t have to remember to do it:
USE master GO ALTER DATABASE tempdb MODIFY FILE ( NAME = N'templog', SIZE = 24GB , FILEGROWTH = 4GB ); GO
Ugh. Except now that job will run every time SQL Server restarts, and I’m going to have to remember to delete the job. Unless I can automate deleting the job… Really, I just need to trigger this one-line of T-SQL to run after my job runs successfully.
EXEC msdb.dbo.sp_delete_job @job_name = 'Grow temp log file on startup';
SQL Agent can do that, too! When I define my job, I create the first step to run that
ALTER DATABASE tempdb MODIFY FILE statement so that on failure, the job quits reporting failure, but on success it runs a second step to delete itself using
That’s great! Now I just need to get a successful restart in (planned or not), and I’ve got everything in place to magically shrink my data files, and grow my log file. That just leaves one thing on my to-do list.
3. Drop extra tempdb files
I just want to drop two of the files in
tempdb. This should be pretty easy, right?
ALTER DATABASE tempdb REMOVE FILE temp9; ALTER DATABASE tempdb REMOVE FILE temp10;
Running this will throw an error:
The file ‘temp9’ cannot be removed because it is not empty.
Oh, right. I’ve gotta run
EMPTYFILE before I can remove the file. That makes sense.
USE tempdb GO DBCC SHRINKFILE(temp9,EMPTYFILE); DBCC SHRINKFILE(temp10,EMPTYFILE);
This throws an error, too!
Msg 2555, Level 16, State 1, Line 1
Cannot move all contents of file “temp9” to other places to complete the emptyfile operation.
Darn, that’s the same issue that is requiring the reboot related to step 1 to shrink the data files I want to keep. I did a quick Google search, and found this article by Erin Stellato (blog|twitter). Erin suggests restarting the service using the
-f parameter for minimal configuration, which then allows the
REMOVE FILE to be run, then you can start the service normally and you’re back in business.
Except, I went through all that work to make steps 1 & 2 happen magically without needing to do any manual work during a maintenance window. I’d rather be able to prep this file removal, too. I always prefer to do work before a maintenance window, so that the window itself is as simple as possible.
What if… What if I schedule a SQL Agent job to run at startup, and remove the file before anyone or anything is able to start working & create those work tables? And then if that works, I can have the job delete itself! I can just follow the same pattern I used for #2 above, but to remove those files.
I have two data files that I want to remove, so I created two SQL Agent jobs–one for each file. The three steps look something like this:
--Step 1 USE tempdb GO DBCC SHRINKFILE(temp9,EMPTYFILE); --Step 2 USE master GO ALTER DATABASE tempdb REMOVE FILE temp9; --Step 3 EXEC msdb.dbo.sp_delete_job @job_name = 'Remove temp9 file on startup';
EDIT: Dan Clemens (blog|twitter) pointed out to me that SQL Server natively supports deletion of a job on completion, without having to do it “the hard way” of calling
sp_delete_job via code from the job step. Thanks for the tip, Dan!
Time for maintenance
I did the above prep work earlier in the week, then last night I had a scheduled maintenance window to restart the SQL Server service. At the start of my maintenance window, I opened a PowerShell prompt and ran these four commands:
Enter-PsSession MyServerThatNeedsSomeLove Stop-Service SQLSERVERAGENT Stop-Service MSSQLSERVER Start-Service SQLSERVERAGENT
Note: I don’t actually start SQL Server–I just start SQL Agent, and because it has a dependency on the database engine, that implicitly starts the SQL Server service, and also ensures a minimal delay between the engine & Agent services starting, and ensures my
REMOVE FILE commands run as quickly as possible before any work tables get created.
Sure enough, as soon as things were started back up, I looked at
tempdb, and everything was perfectly cleaned up without me having to do any more work.
- There were only 8 data files: files 9 & 10 had been deleted.
- All of the remaining data files had been reduced to 40GB each.
- The log file had been grown to 24GB.
- The three SQL Agent jobs I had created were all gone.
My maintenance window was super quick, super easy, and I had no cleanup work left to do afterwards. Time to go make myself a drink.