SQL Server Needs Tempdb

Steve Jones, 2013-07-15

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.

Filed under: Blog Tagged: administration, syndicated

Rate

Share

Share

Rate

Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

Robert Davis

2009-02-23

1,567 reads

Networking – Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I’d like to talk about social networking. We’ll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let…

Andy Warren

2009-02-17

1,530 reads

Speaking at Community Events – More Thoughts

Last week I posted Speaking at Community Events – Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I’ve got a few more thoughts on the topic this week, and I look forward to your comments.

Andy Warren

2009-02-13

360 reads