Tempdb needs a doctor

In the past, we’ve run into issues with contention and the -T1118 flag, hotfix 2040, and multiple tempdb files that led me to go ahead and put this info out there if nothing more than for my future reference.

I ran into a machine today that had the tempdb files in the wrong location. They were also the wrong size and there were not enough of them.

So that presents three problems that had to be solved. The first problem I decided to fix was to get rid of the current files that were sized inappropriately and in the wrong location. No need to double my work.

I’m sure it can just as easily be done by script, but I went into the GUI and deleted each tempdb data file that I didn’t want anymore for the time being. You will get an error message about deleting the file and restarting SQL Server. Once you’ve done that you are left with only one data file and one temp file, even though they may be in the wrong location and the wrong size.

Let’s go ahead and tackle the size issue first. After we’ve just restarted SQL Server lets do something like this:

dbcc shrinkfile (‘tempdev’,1024,TRUNCATEONLY)

Now it’s time to go ahead and move the files to the place we want them:

USE master
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = ‘I:\tempdb.mdf’)
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = ‘I:\templog.ldf’)
GO

Now it’s time to add the additional tempdb data files:
USE [master]
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N’tempdev2′,
FILENAME = N’I:\tempdb2.ndf’ , SIZE = 1048576KB , FILEGROWTH = 204800KB )
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N’tempdev3′,
FILENAME = N’I:\tempdb3.ndf’ , SIZE = 1048576KB , FILEGROWTH = 204800KB )
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N’tempdev4′,
FILENAME = N’I:\tempdb4.ndf’ , SIZE = 1048576KB , FILEGROWTH = 204800KB )
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N’tempdev5′,
FILENAME = N’I:\tempdb5.ndf’ , SIZE = 1048576KB , FILEGROWTH = 204800KB )
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N’tempdev6′,
FILENAME = N’I:\tempdb6.ndf’ , SIZE = 1048576KB , FILEGROWTH = 204800KB )
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N’tempdev7′,
FILENAME = N’I:\tempdb7.ndf’ , SIZE = 1048576KB , FILEGROWTH = 204800KB )
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N’tempdev8′,
FILENAME = N’I:\tempdb8.ndf’ , SIZE = 1048576KB , FILEGROWTH = 204800KB )
GO

This will create 8 files (for our # of processors) that are 1024MB in size and autogrow at 200MB. At this point it probably wouldn’t hurt to restart the SQL Server. We had a few small issues but I’m not sure they were related.

The links that helped me with this are as follows:
http://www.sqlteam.com/article/moving-the-tempdb-database
http://weblogs.sqlteam.com/tarad/archive/2008/05/20/60604.aspx
http://weblogs.sqlteam.com/tarad/archive/2008/08/05/How-to-get-physical-CPU-count-on-a-server.aspx
http://sql-server-performance.com/Community/forums/p/23813/135241.aspx
http://support.microsoft.com/kb/328551