How to Move TempDB to Another Drive

I figured i will store this on my blog since each time i scramble to google for the script to move the files. So here we are:

SELECT 'ALTER DATABASE tempdb MODIFY FILE (NAME = [' + f.name + '],'
	+ ' FILENAME = ''T:\TempDB\' + f.name
	+ CASE WHEN f.type = 1 THEN '.ldf' ELSE '.mdf' END
	+ ''');'
FROM sys.master_files f
WHERE f.database_id = DB_ID(N'tempdb');

Copy the output of the above script and paste it in SSMS. I kind of like using the script to dynamically generate the script I need to run. Sometimes, depending of available space, I may have to move log file to another drive.

Sample Output (run this on a new query window)

Please be sure to verify the file path used above. Otherwise, you would fail to start up the SQL server instance after you bounce the services.

If you need up in that situation. Here is what you do:

Re-start the instance in safe mode (-f startup parameter) and move tempdb. Then re-start without the parameter.

Here is an example of what that would look like:

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: