How to move TempDB from one drive to another?

Suppose tempdb database files are located on drive C and you want to move it to drive E.

To get the current location of the tempdb database files run the following script.

USE TempDB
GO
EXEC sp_helpfile
GO

The result is like below


Name
FileID
FileName
FileGroup
Size
Maxsize
Growth
usage
tempdev
1
C:\Program Files\Microsoft SQL Server\
MSSQL10_50.MSSQLSERVER\MSSQL\
DATA\tempdb.mdf
PRIMARY
18240 KB
Unlimited
10%
data only
templog
2
C:\Program Files\Microsoft SQL Server\
MSSQL10_50.MSSQLSERVER\MSSQL\
DATA\templog.ldf
NULL
768 KB
Unlimited
10%
log only

The Name field in above result will be used in 'ALTER DATABASE' statement to move mdf and ldf files

USE master
GO
ALTER DATABASE TempDB
MODIFY FILE (NAME = tempdev, FILENAME = 'e:TempDB\tempdb.mdf')
GO
ALTER DATABASE TempDB
MODIFY FILE (NAME = templog, FILENAME = 'e:TempDB\templog.ldf')
GO

The definition of the TempDB is changed. However, no changes are made to TempDB till SQL Server restarts. Please stop and restart SQL Server and it will create TempDB files in new locations.

NOTES:
1. Reasons why tempdb needs to be moved
       a. No Enough space on existing drive for Tempdb to grows big in size.
       b. Moving Tempdb to another drive helps to improve disk I/O.
2. Make sure that TempDB is set to autogrow and do not set a maximum size for TempDB.
3. It is recommended to create the tempdb files equal to the number of CPU cores in the server.

No comments:

Post a Comment