Moving the tempdb database

On 27 Jan, 2016

MS SQL DBA

By : Charith Silva

No Comments

Tempdb is re-created each time the instance of SQL Server is started; it’s not required to physically move the data and log files. The files are created in the new location when the service is restarted. Until the service is restarted, tempdb continues to use the existing data and log files.

1.Identify the logical file names and location of TempDB Data and Log Files

SELECT name as [logical file names], physical_name as [File location],type_desc,state_desc

FROM sys.master_files

WHERE database_id = DB_ID(N'tempdb');

SELECT name as [logical file names], physical_name as [File location],type_desc,state_desc

FROM sys.master_files

WHERE database_id = DB_ID(N'tempdb');

2.Change the location of the .mdf and .ldf files.

Modify .mdf file location

ALTER DATABASE tempdb

MODIFY FILE (NAME = tempdev, FILENAME = 'E:\MSSQL\tempdb.mdf');

ALTER DATABASE tempdb

MODIFY FILE (NAME = tempdev, FILENAME = 'E:\MSSQL\tempdb.mdf');

Modify .ldf file location

ALTER DATABASE tempdb

MODIFY FILE (NAME = templog, FILENAME = 'F:\MSSQL\templog.ldf');

ALTER DATABASE tempdb

MODIFY FILE (NAME = templog, FILENAME = 'F:\MSSQL\templog.ldf');

3.Stop and restart the instance of SQL Server.

4.Verify the file change

SELECT name as [logical file names], physical_name as [File location],type_desc,state_desc

FROM sys.master_files

WHERE database_id = DB_ID(N'tempdb');

SELECT name as [logical file names], physical_name as [File location],type_desc,state_desc

FROM sys.master_files

WHERE database_id = DB_ID(N'tempdb');

5.Delete the tempdb.mdf and templog.ldf files from the original location.

About The Author

Charith Silva

Charith Silva is a Microsoft certified SQL Server developer and database administrator who was graduated at Buckinghamshire New university in the UK. His career was started in 1998, primarily into Web application development, and later diversified into database development. He has got a vast experience in SQL Server database development, Database administration and Business Intelligence development. He believes that sharing the knowledge is key to the success.

Number of Posts : 87

All Posts by : Charith Silva