201908 – SQL – move tempdb files

I join a new company recently and which the database was managed by different person in application developing team. In someway, the database is not managed well especially their DEV/UAT environment. I am tasked to take over the maintenance job.

I target on one of the BI UAT server and tried to move its tempdb out from C drive.

I use Brent Ozar’s script, and just modify the file location.

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

This script will generate a query to modify the tempdb files location.

ALTER DATABASE tempdb MODIFY FILE (NAME = [tempdev], FILENAME = 'D:\DATA\tempdev.mdf');
ALTER DATABASE tempdb MODIFY FILE (NAME = [templog], FILENAME = 'D:\DATA\templog.ldf');

Run this query in Microsoft SQL Server Management Studio, and then restart the SQL services. Boh! You will have the tempdb data and log files moved to your destination.

This script is even more helpful when there are multiple data files of tempdb.

What is interesting is Brent shared in his blog that he come out of above script when he was facing the same need and he tried to google online. In the end, he prepare his own script and share it. 🙂

I did the same, whenever I face a task which is boring and repeated. I will try to get it resolved by a script. I will google a script first. Thanks google that most of the time i can get one.

refer to:
https://www.brentozar.com/archive/2017/11/move-tempdb-another-drive-folder/

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 )

Google photo

You are commenting using your Google 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