Log Shipping – NORECOVERY vs. STANDBY mode

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.