Fixing tempdb: Growing, shrinking, and removing data files

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 tempdb files, 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!

Three fixes

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

Running the 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 affect 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

The 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.

Now I have to remember to run this after restart–except… what if I could just schedule this to happen on the next restart so I don’t have to remember.

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 msdb.dbo.sp_delete_job:

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

Dropping tempdb files like they’re hot

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:

Msg 5042, Level 16, State 1, Line 7  
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!

DBCC SHRINKFILE: Page 4:130 could not be moved because it is a work table page.  
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';

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.

The post Fixing tempdb: Growing, shrinking, and removing data files appeared first on Andy M Mallon - AM².

Comments

[Bryan H.]

Under "Notifications" for an Agent job, there is an option to have it delete itself on Success/Failure/Completion. Makes your setup even easier!