tl;dr; Re-start the instance in safe mode (-f startup parameter) and move tempdb. Then re-start without the parameter.

While at the 2015 Summit I walked into the last few minutes of Russ Thomas’s (b/t) session on Stress Inoculation: Maintaining Performance Under Pressure. As a side note I was under some heavy stress myself at that particular moment (I was waiting for my session to start) which explains why I was wandering around the hall aimlessly.

From what I saw it was a great session. The premis is that you won’t be nearly as stressed out during a disaster if you’ve practiced resolving that particular disaster. Sounds reasonable right? As part of his session he did these great interactive demos. He brought people up from the audience and had them try to solve a difficult problem while he harassed them (gently) in the background. Anyone who has dealt with a server crash (for example) will realize this is a pretty good way to demonstrate the type of stress you might be experiencing. At the moment I walked in one of the audience members was being challenged. He was being asked to re-start a SQL Instance where the drive that held tempdb was no longer available. As Russ put it this is a low occurance high liability issue. (It doesn’t happen very often but when it does it’s a big deal.)

So in case it ever happens to you (or me) here we go:

Starting a SQL Server without tempdb

The situation: Your server is down. The drive/directory where tempdb is supposed to be doesn’t exist. Who knows why. Maybe those evil SAN guys forgot to re-attach your storage during a DR situation. You may or may not realize it but SQL Server will not start without tempdb. Which is fine. Just move it to a location that exists right? Well, yes. That is an important step. So here is how we

Move tempdb

1. Run an alter database

   -- Pulled straight out of MSDB
   USE master;
   GO
   ALTER DATABASE tempdb 
   MODIFY FILE (NAME = tempdev, FILENAME = 'E:\SQLData\tempdb.mdf');
   GO
   ALTER DATABASE tempdb 
   MODIFY FILE (NAME = templog, FILENAME = 'F:\SQLLog\templog.ldf');
   GO

2. Stop and re-start SQL Server

Ok, so if you can tell me how to run an ALTER DATABASE when the instance is down I’ll be hugely impressed. Actually beyond impressed. Stunned even. We are going to have to move tempdb though. We will need it once we are back up again.

Step one is to bring the instance back up in some form or another. There are two ways (well that I can think of off the top of my head) to start up SQL Server. We can use the sqlservr.exe command to run from a command shell or use SSCM (SQL Server Configuration Manager).

Either way we need to start up SQL Server with minimal configuration (use parameter -f). One of the benefits of minimal configuration is that it doesn’t require tempdb. Be warned it also puts the instance in single user mode. So if you start the instance up and the Agent starts up with it you will need to shut it down before you can connect to the instance. Also as with any time you are in single user mode you only want to connect via SQLCMD or a query window in SSMS (no object explorer).

Note: You can also use trace flag 3608 which tells SQL Server not to start up or recover any database but Master.

Now that SQL Server is started up we can use the above code to move tempdb to an existing location and then re-start SQL Server without the startup flag or trace flag.

And after that nice long winded explanation here is the quick and easy demo.

The first thing I did was to move the tempdb of one of my instances to it’s own directory.

USE master;
GO
ALTER DATABASE tempdb 
MODIFY FILE (NAME = tempdev, FILENAME = 'c:\tempdb\tempdb.mdf');
GO
ALTER DATABASE tempdb 
MODIFY FILE (NAME = templog, FILENAME = 'c:\tempdb\templog.ldf');
GO

Next I shut down that instance and deleted the directory. Now when I try to start the instance I get these errors in the event log.

CREATE FILE encountered operating system error 3(The system cannot find the path specified.) while attempting to open or create the physical file ‘c:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS2012\MSSQL\DATA\tempdb\tempdb.mdf’.
FCB::Open failed: Could not open file c:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS2012\MSSQL\DATA\tempdb\tempdb.mdf for file number 1. OS error: 3(The system cannot find the path specified.).
The SQL Server (SQLEXPRESS2012) service terminated with the following service-specific error:

The specified resource name cannot be found in the image file.

Not terribly surprising right? I do want to point out that this tells us exactly what the error is. There are any number of reasons an instance might not start, this error tells us exactly what we need to do.

Now in this particular case we can fix the problem by just re-creating the missing directory but let’s pretend that isn’t an option (the whole drive is missing for instance). So let’s re-start the instance using the -f flag. First I open SSCM and go to the properties of the instance.

Then on the Startup Parameters tab I add a parameter -f.

Hit the Apply button then re-start the instance.

Now we move tempdb back where it belongs. Just to make the whole process fun I got this nifty error when connecting to the instance:

I’m not sure why but I connected anyway. Once connected we run our fix script:

USE master;
GO
ALTER DATABASE tempdb 
MODIFY FILE (NAME = tempdev, FILENAME = 'c:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS2012\MSSQL\DATA\tempdb.mdf');
GO
ALTER DATABASE tempdb 
MODIFY FILE (NAME = templog, FILENAME = 'c:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS2012\MSSQL\DATA\templog.ldf');
GO

Now back to SSCM and the instance properties where we remove the -f flag.

Restart the instance again.

And we are back to normal.

Alright so maybe that wasn’t as quick as I said. It was easy though wasn’t it 🙂

Filed under: Microsoft SQL Server, Problem Resolution, SQL Services, SQLServerPedia Syndication, System Databases Tagged: disaster recovery, microsoft sql server, tempdb database