Search This Blog

Moving the system database "tempdb"

If the system database "tempdb" is defined on several data files as recommended in general, each data file is moved by a ALTER DATABASE statement.

USE master;
GO
ALTER DATABASE tempdb 
MODIFY FILE (NAME = tempdb_data, FILENAME = 'H:\MSSQL\tempdb.mdf');
GO
ALTER DATABASE tempdb 
MODIFY FILE (NAME = tempdb_01, FILENAME = 'I:\MSSQL\tempdb_01.ndf');
GO
ALTER DATABASE tempdb 
MODIFY FILE (NAME = tempdb_02, FILENAME = 'J:\MSSQL\tempdb_02.ndf');
GO
ALTER DATABASE tempdb 
MODIFY FILE (NAME = tempdb_03, FILENAME = 'K:\MSSQL\tempdb_03.ndf');
GO
ALTER DATABASE tempdb 
MODIFY FILE (NAME = tempdb_log, FILENAME = 'L:\MSSQL\templog.ldf');
GO


The output of each statement is a message that the system catalog is modified and that the new path will be used after a restart of the database instance.

The file "tempdbXXXX" has been modified in the system catalog. The new path will be used the next time the database is started.




Please notice that you have to remove the old tempdb files manually after the restart.


If the size of the files is configured before the move, a side benefit is de-fragmented files.

1 comment:

Alwin Co Daan said...

This is an amazing blog,it gives very helpful messages to us.Besides that Wisen has established as Best Dot Net Training in Chennai. or learn thru ASP.NET Online Training . Nowadays Dot Net has tons of job opportunities on various vertical industry.