//
you're reading...
SQL Server

Moving tempdb

Moving the tempdb database in SQL Server is a simple process but does require the service to be restarted.

By default, the tempdb database is created in the same location as master, model and msdb.  tempdb is used to store temporary user created objects (e.g. temp tables), temporary internal objects (e.g. work tables for sorting) and any row version data.  Each time SQL Server is started all the objects in tempdb are deleted but the file sizes are retained. 

The primary reasons for wanting to move the tempdb are to increase performance or deal with file size issues.

Step 1: Determine\confirm the logical file names of of the data files for tempdb.  You can run the following command in SQL Server 2000 \2005\2008:

USE tempdb GO

EXEC sp_helpfile

GO

name fileid filename

——— —— ——————————-

tempdev 1 c:\Data\MSSQL\data\tempdb.mdf

templog 2 c:\Data\MSSQL\data\templog.ldf

Unless modified the default will be 1 data file and 1 log file with the standard names tempdev and templog.

Step2: ALTER DATABASE Setting the FILENAME parameter to the location where you’d like each file.

USE master GO

ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = ‘D:\tempdb\Data\tempdb.mdf’)

GO

ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME = ‘D:\tempdb\Log\tempdb.ldf’)

GO

Step 3: Restart SQL Server (Services)

Step 4: Delete old tempdb files

Volla you are done.

Advertisements

About ldgaller

Accomplished Data Warehouse Architect, DBA and Software Architect with over 15 years of professional experience and demonstrated success designing and implementing solutions that improve business functionality and productivity. Highly diverse technical background with proven ability to design, develop and implement technology on an enterprise level. I approach all projects with passion, diligence, integrity, and exceptional aptitude.

Discussion

No comments yet.

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

%d bloggers like this: