Resetting TempDB data files

I'm Back!

Have you ever missed a personal deadline for something (like a making a doctor's or dentist appointment) and the further you get past it, the harder it it is to do it? That happened it me and I found that I can go a LONG time before I finally break down and do it, 2 years and 8 months, apparently. Well in that time I have learned quite a bit about SQL and hope to be able to post something informative about every two weeks that isn't just a rehash of what you can find on other peoples blogs. Anyway, on with the post..

TempDB Configuration

Lately I have been doing some research and testing into the configuration of Tempdb. I won't rehash all the details of how or why you should change the default settings since there are people who I would just be paraphrasing:

• Demystify Tempdb Performance and Management - Robert Davis
• TempDB Performance and Configuration - Kendra Little
• Building Perfect SQL Servers, Every Time - Joey D'Antoni
• Correctly Adding data Files to TempDB - Paul Randall

What I am going to do is tell you about an error I kept getting when I was testing out some different configurations and how I resolved it.

Almost all of the information that you find on the internet about TempDB configuration tells you that you need to have more than one data file for TempDB so you don't suffer performance issues related to page allocation contention. There are some varying opinions on how many you need to have. Some say 1 file per core up to 8, some say 1 file for every 2 cores. There are also opinions on how to properly size the data files and the log file. You can read about them in the links above or with a google search. One important thing to note is that most everyone says you need to right size your data files and turn off auto-growth. This allows TempDB to allocate space in a round robin fashion. If one file is much bigger and has more free space, it will get the writes until the free space is reduced to the size of the other files.

My current task at work is to come up with a script that can automatically create the TempDB files and size them. In our current build doc we just create 8 TempDB files (even if we only have 4 cores) and size them at 1GB with auto grow at 1GB. My plan was to mess around with some different configurations and try out different numbers of data files. I the first test, I just took our existing 8 file standard and sized them to 80% of the disk that they are on.

--- Set the number of files
SET @number_of_files = 8
--- Get the Drive size for TempDB
SELECT @drive_size_mb=total_bytes/1024/1024 FROM sys.dm_os_volume_stats (2,1)
--- Calculate 80% of the total drive size and divide that by the number of files to get individual file size
SELECT @individ_file_size = (@drive_size_mb*.8)/@number_of_files

Once, I was finished with that script I wanted to reset TempDB so that it only had one file so I could try something different. I used DBCC SHRINKFILE to empty out each TempDB file and then REMOVE FILE to remove them.

USE TempDB
GO
DBCC SHRINKFILE (tempdev8,EMPTYFILE);
GO

ALTER DATABASE TempDb
REMOVE FILE tempdev8;
GO

The problem is that when I tried to do that I got an error message:

DBCC SHRINKFILE: Page 9:32 could not be moved because it is a work table page.
Msg 2555, Level 16, State 1, Line 4
Cannot move all contents of file "tempdev8" to other places to complete the emptyfile operation.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Msg 5042, Level 16, State 1, Line 6
The file 'tempdev8' cannot be removed because it is not empty.

It looks like something is using my TempDB files so I can't get rid of them. (This is a test server but it does have some other non-prod stuff running on it). I tried restarting SQL service and running the script as soon as it started but I still got the error on most of the files. Something started using the TempDB data files right when SQL started and I couldn't get them deleted. I started thinking about how I could force everything to use just one of the TempDB files so I could delete the other ones. Can you figure out what I did based on something I have already said in this article?

The solution that worked was I increased the size of the main TempDB file so that it was much larger than the other files. Then when I restarted SQL Service all the TempDB writes went to that files since it had more free space then the other ones. Right after restart I ran my delete script and was left with one TempDB datafile so I could start working with another configuration.

Sum Up

I know this isn't a groundbreaking post (gimme a break, it's my first one back) but I love it when you can take some information that you are learning or just read about and then you apply it in a real life situation like this. I'll be back in two weeks, no idea of a topic yet but I'll think of something.

Hasta Luego,
RutherfordTX