Advertisements

Archive

Archive for June 27, 2013

SQL DBA – Move tempDB to another drive in simple steps

June 27, 2013 Leave a comment

 
Today we will see how to move your SQL Server tempDB database from its default location to an another drive. Reason could be anything like Disk maintenance, Failure recovery, etc. This can be done by using the normal ALTER DATABASE statement, we will see how.
 


 

–> Script: Let’s first check the location of master DB:

USE tempdb
GO

SELECT * FROM sys.database_files

Move TempDB 01
 

–> Now execute the ALTER DATABASE statement with MODIFY FILE option:

USE master;  
GO

ALTER DATABASE tempdb   
MODIFY FILE (
	NAME = tempdev, 
	FILENAME = 'E:\SystemDatabases\tempDB\tempdb.mdf'
);
GO

ALTER DATABASE tempdb   
MODIFY FILE (
	NAME = templog, 
	FILENAME = 'E:\SystemDatabases\tempDB\templog.ldf'
);
GO

The file “tempdev” has been modified in the system catalog. The new path will be used the next time the database is started.
The file “templog” has been modified in the system catalog. The new path will be used the next time the database is started.
 

–> Now Restart the SQL Server services from SSMS Object Explorer or by going to RUN –> services.msc.

As soon as the SQL Server services are started, the SQL engine re-creates the tempDB database files. So there is no need to physically move them to the new location.
 

–> To confirm the new location, just execute following query and check the path:

Move TempDB 02
 


Advertisements
Categories: DBA Stuff Tags: