SQL 2008 R2: Temp Database Configuration

Temp databases are very important to some applications, as they are used as a scratch or buffer space. Other applications may not use them hardly at all, so it really depends on your environment.

One rule of thumb I use for VMware environments is one TempDB for every vCPU presented to your SQL server. Since many virtualized SQL environments will have multiple processors, you want multipleTempDB files for SQL to use.

Using SQL studio, you can run the script below to automatically add a second TempDB file, and also expand the default TempDB. Of course, adjust the size and growth parameters to fit your situation.

If you want 8GB of TempDB space and have two CPUs, then change the existing TempDB to 4GB and create a second that is also 4GB. You want the TempDBs all of equal size since SQL weights their usage based on their size.

-----
USE master;
GO
ALTER DATABASE tempdb
ADD FILE
(NAME = tempdev2,
FILENAME = 'T:\Microsoft SQL Server\MSSQL\Data\tempdb2.mdf',
SIZE = 2048MB,
FILEGROWTH = 512MB);

ALTER DATABASE tempdb
MODIFY FILE
(NAME = tempdev,
SIZE = 2048MB,
FILEGROWTH = 512MB);

ALTER DATABASE tempdb
MODIFY FILE
(NAME = templog,
SIZE = 512MB,
FILEGROWTH = 128MB);
GO