TempDB and SQL 2005

Recently I found some more evidence in support of pre-sizing TempDB and turning off auto-grow. We have a server that has starting having disk issues with TempDB every time it reboots. The server is SQL 2005 SP3 CU11 and when it reboots the TempDB drive fills up and we get this message in the error log:

      X:\TempDB_Data\TempDev8.NDF: Operating system error 112(There is not enough space on the disk.) encountered.

The server starts up but only one TempDB file is used. The problem started after tempdb expanded and filled up the drive one time and I shrunk some files and grew the rest so they were all the same size. Then i turned off autogrow thinking the problem was fixed. After the next reboot we got the error message and the size of the one file that did get created was larger than it should have been. Also, when I looked at the properties in SSMS, all of the file sizes were different and some were larger than what I had shrunk them to. What the heck was going on?

To test it out, I installed SQL 2005 on a test machine and used a script to pre-size TempDB so that all the files were the same size.

I picked one file (tempdev8) and increased the size to 2000 MB. I verified the increase by checking the actual file size on disk and by running a query against sys.master_files

SELECT database_id, type_desc, name, physical_name, state_desc, size, size /128 AS sizemb FROM sys.master_files WHERE database_id = 2 order by type_desc, name

I rebooted the server just to set everything and checked again to make sure the file was still 2000MB.
Next I shrunk the file back down to 1800

USE [tempdb] GO DBCC SHRINKFILE (N'tempdev8' , 1800) go

I verified the size change by looking at the actual file size on disk and was happy with the result.

Then I restarted the SQL service and when it came back up the file size on disk was back up to 2000MB. What the heck!
I ran my shrink again and this time checked sys.master_files as well as the file size on disk. While the file size on disk was the same as all the other files, my query from above showed the same results as it did right after I expanded the file the first time

Ah, now I could see what was happening, even though I am shrinking the file, SQL isn't updating sys.master_files so when the service is restarted the size of tempdev8 goes right back to 2000MB.  Now that I knew the problem I had to figure out how to fix it. I had to alter the file after I shrunk it so that the entry in sys.master_files changes. This can be accomplished with the following code

USE [tempdb] GO DBCC SHRINKFILE (N'tempdev8' , 1700) go ALTER DATABASE [tempdb]    MODIFY FILE ( NAME = N'tempdev8', SIZE = 1800MB ) GO

Notice that I shrunk it down to 1700MB then grew it up to 1800MB. When I tried to use the same number for both commands I got an error "MODIFY FILE failed. Specified size is less than current ".  After these commands completed, I checked sys.master_files again and everything looked good. I restarted SQL again and the file stayed at the correct size.

 

The problem on the original server (the one with the error) is that a couple of tempdb files had gotten quite big so that when I shrunk them and then increased the size of all the remaining files, their combined file size from sys.master_files was larger than the drive capacity even though the file size on disk still left 10% of the drive free. I did this same procedure on the affected server and everything looks good. I'll keep an eye on it after the next reboot to verify but I don't expect to have any issues.

Sum Up

While SQL 2005 is no longer supported and everyone should be either off of it or planning to migrate soon, I think this is a good example of unintended consequences. Since we don't currently presize TempDB at work and don't have a max size set, we end up doing a bit of manual adjustment of file sizes. If we had just gone forward with the recommendation then we would have saved ourselves quite a bit of troubleshooting time.

Hasta Luego,RutherfordTX