Sunday, July 3, 2011

Moving TEMPDB files

TEMPDB files cannot be moved when SQL Server is online.  The way to move the files is to execute an ALTER DATABASE command which modifies the TEMPDB FILENAME (and location) in the catalog.  The next time the SQL Server is started, the files are created in the new location.   See the MSDN reference here.

The following script creates an ALTER DATABASE command which then I submit to move the TEMPDB files:  

SELECT 'ALTER DATABASE tempdb MODIFY FILE (NAME = ' +
name +
', FILENAME = ''M:\MSSQLDATA\' +
name +
'.ndf'');'
FROM sys.master_files
WHERE database_id = DB_ID(N'tempdb');
GO 

The resultant query becomes the script to move the files:

ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = 'M:\MSSQLDATA\tempdev.ndf');
ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME = 'M:\MSSQLDATA\templog.ndf');
ALTER DATABASE tempdb MODIFY FILE (NAME = TempD2, FILENAME = 'M:\MSSQLDATA\TempD2.ndf');
ALTER DATABASE tempdb MODIFY FILE (NAME = TempD3, FILENAME = 'M:\MSSQLDATA\TempD3.ndf');
ALTER DATABASE tempdb MODIFY FILE (NAME = TempD4, FILENAME = 'M:\MSSQLDATA\TempD4.ndf');

Be sue to modify the FILENAME part of the statement to match your environment drive/folders and modify the resultant code to change the .ndf to .mdf/.ldf file extension as appropriate for that data file (proper file extension name is not required for SQL Server, but helps DBAs quickly identify file types).

The old files are still there and should be deleted to conserve space as they are no longer used.

No comments:

Post a Comment