Moving TempDB

I have only had to do this a few times in a VM but want to capture the steps. Note that with Azure VM – The New datafiles will be the same size as the Old datafiles. 

/* MovingTempDB.sql */
 
/* 1 save the current catalog paths to "rollback" (just-in-case) */
 
    USE master;
    GO
	select space(4) + 'USE master;' cmd
	union
	select space(4) + 'GO'
	union
    SELECT space(4) + 'ALTER DATABASE tempdb MODIFY FILE (NAME = [' + name + '], FILENAME = [' + physical_name + ']);'
    FROM sys.master_files 
    WHERE database_id = DB_ID(N'TempDB')
    ORDER BY cmd desc;

/* 2 manually create the "new" destination folder(s) */
  
/* 3 change the catalog */
 
    USE master;
    GO
    ALTER DATABASE tempdb MODIFY FILE (NAME = [tempdev], FILENAME = [D:\TempDB\tempdb.mdf]);
    ALTER DATABASE tempdb MODIFY FILE (NAME = [temp2], FILENAME = [D:\TempDB\tempdb_2.ndf]);
    ALTER DATABASE tempdb MODIFY FILE (NAME = [temp3], FILENAME = [D:\TempDB\tempdb_3.ndf]);
    ALTER DATABASE tempdb MODIFY FILE (NAME = [temp4], FILENAME = [D:\TempDB\tempdb_4.ndf]);
    ALTER DATABASE tempdb MODIFY FILE (NAME = [temp5], FILENAME = [D:\TempDB\tempdb_5.ndf]);
    ALTER DATABASE tempdb MODIFY FILE (NAME = [temp6], FILENAME = [D:\TempDB\tempdb_6.ndf]);
    ALTER DATABASE tempdb MODIFY FILE (NAME = [temp7], FILENAME = [D:\TempDB\tempdb_7.ndf]);
    ALTER DATABASE tempdb MODIFY FILE (NAME = [temp8], FILENAME = [D:\TempDB\tempdb_8.ndf]);
 
    /* confirmation message
    "The file "tempdev" has been modified in the system catalog. The new path will be used the next time the database is started." */
   
/* 4 manually reboot the server */
 
/* 5 manually check the new files have been created */
  
/* 6 manually delete old files, and empty the recycle-bin */

/* 7 ideally pre-grow all tempdb data files */

/* x rollback if needed */

    USE master;
    GO
	ALTER DATABASE tempdb MODIFY FILE (NAME = [templog], FILENAME = [F:\SQLLogs\templog.ldf]);
	ALTER DATABASE tempdb MODIFY FILE (NAME = [tempdev], FILENAME = [H:\TempDB\tempdb.mdf]);
	ALTER DATABASE tempdb MODIFY FILE (NAME = [temp2], FILENAME = [H:\TempDB\tempdb_2.ndf]);
	ALTER DATABASE tempdb MODIFY FILE (NAME = [temp3], FILENAME = [H:\TempDB\tempdb_3.ndf]);
	ALTER DATABASE tempdb MODIFY FILE (NAME = [temp4], FILENAME = [H:\TempDB\tempdb_4.ndf]);
	ALTER DATABASE tempdb MODIFY FILE (NAME = [temp5], FILENAME = [H:\TempDB\tempdb_5.ndf]);
	ALTER DATABASE tempdb MODIFY FILE (NAME = [temp6], FILENAME = [H:\TempDB\tempdb_6.ndf]);
	ALTER DATABASE tempdb MODIFY FILE (NAME = [temp7], FILENAME = [H:\TempDB\tempdb_7.ndf]);
	ALTER DATABASE tempdb MODIFY FILE (NAME = [temp8], FILENAME = [H:\TempDB\tempdb_8.ndf]);

Leave a comment