SQL 2008 R2: Temp Database Configuration (Part 6)

One of the last things I do during a SQL installation is configure the temp databases. 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 multiple TempDB 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 vCPUs, 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 ServerMSSQLDatatempdb2.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
—–

Print Friendly, PDF & Email

Related Posts

1
Leave a Reply

avatar
1 Comment threads
0 Thread replies
0 Followers
 
Most reacted comment
Hottest comment thread
1 Comment authors
The Data Builder Man Recent comment authors
  Subscribe  
newest oldest most voted
Notify of
The Data Builder Man
Guest

Hi Derek,

Please can you elaborate on separate LUN for TempDB in a virtual environment, as all storage aggregated together.

Thanks,
Phil

Scroll to Top