Move SQL Tempdb files – Common errors

Do you keep getting errors and timeouts when doing queries with sort?
My first guess wil be to check out the location of my tempdb files, is the disc space running out?db

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:

DBCC UPDATEUSAGE(tempdb)
temp_1

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.

Leave a Reply

Your email address will not be published. Required fields are marked *

%d bloggers like this: