I saw a post recently where someone noted they had moved tempdb like this:
USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = 'c:\tempdb.mdf');
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = 'c:\templog.ldf')
This gives you the message the tempdb has been moved, and it will take effect on restart. If you restart, however, you’ll often find the service doesn’t restart and you get a “network error” when you try to connect. This is because the service is down.
If you check the error log, you might see this:
2013-07-05 13:20:48.65 spid9s Clearing tempdb database.
2013-07-05 13:20:48.65 spid9s Error: 5123, Severity: 16, State: 1.
2013-07-05 13:20:48.65 spid9s CREATE FILE encountered operating system error 5(Access is denied.) while attempting to open or create the physical file ‘c:\tempdb.mdf’.
2013-07-05 13:20:48.65 spid9s Error: 17204, Severity: 16, State: 1.
2013-07-05 13:20:48.65 spid9s FCB::Open failed: Could not open file c:\tempdb.mdf for file number 1. OS error: 2(The system cannot find the file specified.).
2013-07-05 13:20:48.65 spid9s Error: 5120, Severity: 16, State: 101.
2013-07-05 13:20:48.65 spid9s Unable to open the physical file "c:\tempdb.mdf". Operating system error 2: "2(The system cannot find the file specified.)".
2013-07-05 13:20:48.65 spid9s Error: 1802, Severity: 16, State: 4.
2013-07-05 13:20:48.65 spid9s CREATE DATABASE failed. Some file names listed could not be created. Check related errors.
You might also see an operating system error 5 (access is denied) if the files exist. In all likelihood, the problem is security for your service account. The SQL Server service account shouldn’t have rights to all folders and files on the system. If it does, you’re doing something wrong.
I had thought (incorrectly) that SQL might start, but be in a read-only state without tempdb. However Gail Shaw pointed out this was incorrect, and when I tested this, she was right. SQL Server won’t start.
What can you do?
A few options here.
- The brute force approach
- The more elegant approach
The more elegant approach is specified in Books Online, in Move System Databases. In the failure recovery procedure, you start SQL Server with Trace Flag 3608, issue the alter commands, and then restart the instance without the trace flag.
The brute force approach, which I have tested, is to move the tempdb files to this location (they didn’t exist in my situation). You’ll need admin permissions to do this. You can then change the service account to one with permissions to see the files (like an admin), and restart the instance. From there, connect, and issue the ALTER DATABASE commands as shown above, with the correct path.
If you need help configuring permissions, use this article.
Next week I travel to Sacramento, CA for the SQL in the City Seminar – Stairway to Deployment on Friday, July 26, 2013. It’s a half day event where Grant Fritchey and I will look to lead you through the idea of implementing continuous integration and continuous deployment for databases (as well as other code).
