Move SQL Tempdb files – Common errors
October 5, 2015
Run query on the correct database
EXEC sp_helpfile GO
Tip: you can also view it under properties.
USE master; GO ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = 'F:\MSSQL\DATA\tempdb.mdf'); GO
ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME = 'F:\MSSQL\DATA\templog.ldf'); GO
Restart sql services: 'stop and start'
The file "tempdev" has been modified in the system catalog. The new path will be used the next time the database is started.
The file "templog" has been modified in the system catalog. The new path will be used the next time the database is started.
rerun the sp query to verify the correct location fort he tempdb files
Now delete the old files or in my case rename them before finally remove them.
Next we are gonna change the properties for the autogrowth.
Error cant acces properties of tempdb:
Important Note on common ERRORS: SQL Server doesn’t support moving TempDB Database using backup/restore and by using detach database methods.
Error Message Received when you try Backup and Restore Method
Msg 3147, Level 16, State 3, Line 1
Backup and restore operations are not allowed on database tempdb.
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.
Error Message Received when you try Detach Method
Msg 7940, Level 16, State 1, Line 1
System databases master, model, msdb, and tempdb cannot be detached.