Recovering TempDB

In a previous post “Moving TempDB” I provided step by step instructions on how you can move your TempDB database with an emphasis on reducing disk IO contention. If you have read this post you will notice that I did not change the path for the TempDB database files from the USB stick back to their default location, the reason for this is that I want to show you how you can recover SQL Server when TempDB is not available.

After starting up my machine this morning the instance tried to start up and not surprisingly failed, I have added a subsection of the SQL Log which provides the clues as to why the instance did not start:

2010-08-27 08:45:08.89 Server      Dedicated administrator connection support was not started because it is disabled on this edition of SQL Server. If you want to use a dedicated administrator connection, restart SQL Server using the trace flag 7806. This is an informational message only. No user action is required.
2010-08-27 08:45:08.97 spid10s     Error: 5123, Severity: 16, State: 1.
2010-08-27 08:45:08.97 spid10s     CREATE FILE encountered operating system error 3(failed to retrieve text for this error. Reason: 15100) while attempting to open or create the physical file ‘J:\SQLTest\tempdb.mdf’.
2010-08-27 08:45:09.15 spid10s     Error: 17204, Severity: 16, State: 1.
2010-08-27 08:45:09.15 spid10s     FCB::Open failed: Could not open file J:\SQLTest\tempdb.mdf for file number 1.  OS error: 3(failed to retrieve text for this error. Reason: 15105).
2010-08-27 08:45:09.17 spid10s     Error: 5120, Severity: 16, State: 101.
2010-08-27 08:45:09.17 spid10s     Unable to open the physical file “J:\SQLTest\tempdb.mdf”. Operating system error 3: “3(failed to retrieve text for this error. Reason: 15105)”.

After looking at the data in the log file you can clearly see the error is that it can not find the TempDB data file. In my case this was because I did not have the USB stick plugged in, but let’s use our imagination and pretend that this drive is actually a single physical disk that has failed to come back online after some kind of maintenance.

The first step is pretty obvious we need to start the instance up, however in reality this could be quite tricky as one of the things a SQL instance does when it starts up is perform a recovery of each database – including TempDB. Luckily the trace flag 3608 allows you to bypass this for every database baring the master database. It is not recommended to use this as a standard startup parameter, every time you start your instance you will want to ensure that each database is transactionally consistent. You will also need to add the /f parameter this will allow you to start up the instance in minimal configuration mode.

To start the instance again you should open up the command prompt (running as an administrator) and run the NET START command. In my environment the service I wanted to start was MSSQL$MAPS. The full command needed to run from the command prompt is shown below:

You should then see that the instance has been started successfully. Similar to the image below:

NET START MAPS

At this point we break out the old trusty SQLCMD utility which you can call from the same command prompt session. The command you will need is:

SQLCMD –S Machine\Instance

At this point you are connected to the instance and can run the ALTER DATABASE statement as we did in “Moving TempDB”, I’ve included below the code I used to bring my TempDB database back to it’s default settings.

To execute a batch of commands in sqlcmd you must use the GO command. At this point you will receive confirmation that the command has completed successfully and you will be informed that the new path will be used the next time the database is started. Exit out of the SQLCMD session and restart the SQL instance.

Once you have restarted the service you will be able to log in to the instance using Management Studio, users will fall at your feet, managers will give you promotions, pay rises and anything that your heart desires.  Either that or someone will walk up to you and say “why did that take so long?”