Moving tempdb files

A common task for a DBA is to move tempdb files to a seperate drive.  During a SQL Server installation, the tempdb files (.mdf and .ldf) are stored in the same drive the sql binaries are installed along with master, msdb and model files.  As a best practice, tempdb files should always be moved to a seperate drive. The following provides the steps involved in moving tempdb files:

1) Get the logical and physical name for tempdb files.

SELECT d.name
, mf.name logical_name
, mf.physical_name
, mf.type_desc
FROM sys.master_files mf
INNER JOIN sys.databases d ON mf.database_id = d.database_id
WHERE d.name = ‘tempdb’
ORDER BY mf.type, mf.name

2) Modify the “NAME” adn “FILENAME” with the new value for tempdb

USE master;
GO

ALTER DATABASE tempdb
MODIFY FILE ( NAME = tempdb, FILENAME = ‘T:\SQLDATA\tempdb.mdf’ )
GO

3) Validate changes took place.

SELECT d.name
, mf.name logical_name
, mf.physical_name
, mf.type_desc
FROM sys.master_files mf
INNER JOIN sys.databases d ON mf.database_id = d.database_id
WHERE d.name = ‘tempdb’
ORDER BY mf.type, mf.name

4) Restart the sql server service

After SQL Server is restarted, the new files will be created in the new location. The old tempdb files will remain in previous location and they will need to be deleted manually.

Advertisements

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s