SQL server

Moving the tempdb.

Moving the tempdb to a dedicated (faster) drive will improve performance. The tempdb is used extensively by SQL server to do sorting operations and to create other temporary data. It is used for all of your user and system databases, and hence giving it a hardware boost helps with the overall SQL server performance. Using the below TSQL scripts you can change the location of your tempdb:

USE master

ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = ‘d:\data\tempdb.mdf’);
GO

ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = ‘d:\logs\templog.ldf’);
GO

As noted, the scripts will have to be run in the context of the master database. Also specify the location of your choice. The last step is to restart the SQL server, and the new location will kick in. The above example uses a tempdb with only one data file.

Advertisements
Standard

Thinking about someting? Leave a Reply...

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s