Recently, we had a scheduled down time for a production server to perform some maintenance tasks. One of them was to move the tempdb to a dedicated drive of its own. This is was the first step and then implement a bunch of optimization steps. But guess what !! There was a bug (mistake) in the code prepared for this maintenance window.

Note: Before running any non-SELECT query (ex: ALTER DATABASE, ALTER TABLE, UPDATE, etc) on Production, write-out the complete code ahead and test it thoroughly on a Development/Staging environment.  In this case, even after doing all of that , somehow, this code-bug was not caught.

So, I ran the first query (ALTER DATABASE) and restarted the instance. But Sql Server would not come up. Production instance of Sql Server could not start !!! After banging my head on my laptop for a while and replaying the recent steps I performed, I realized the bug in the ALTER DATABASE query for tempdb.

To be exact, the mistake was this: I gave the same file name extension for both the data and log file for tempdb in the new location.

[sourcecode language=”sql”]
—
—  Incorrect Query
—
ALTER DATABASE [tempdb]
MODIFY FILE (NAME = ‘tempdev’, FILENAME = ‘E:\Data\tempdb.MDF’)
GO
ALTER DATABASE [tempdb]
MODIFY FILE (NAME = ‘templog’, FILENAME = ‘E:\Data\tempdb.MDF’)
GO

—
—  Correct
—
ALTER DATABASE [tempdb]
MODIFY FILE (NAME = ‘tempdev’, FILENAME = ‘E:\Data\tempdb.MDF’)
GO
ALTER DATABASE [tempdb]
MODIFY FILE (NAME = ‘templog’, FILENAME = ‘E:\Data\templog.LDF’)
GO
[/sourcecode]

As you can see, same path and file extention was given for both the data file and log files !!  (No, it was not a copy-paste query). So, once I realized this, I took a step back and re-assessed the complete situation.

When Sql Server starts, it writes out its progress in ERRORLOG file. So, reading the ERRORLOG file shows the extent to which Sql start up was able to progress. On start up, Sql Server goes through a lot of steps. I’ll cover those steps in detail in a separate post, but for now, let’s look at the high level steps.

1. Shows the Edition and Version information and windows process ID
2. Indicated the registry start up parameters for location for master database and errorlog file.
3. Starts master, mssqlsystemresource, model, msdb and tempdb databases
4. Starts database mirroring and server broker protocols
5. Start user databases
6. …
7. (This is an over simplification, but a good high level picture relevant for our discussion)

So after running my incorrect query, Sql Server could not allocate files for tempdb as it was competing to create the same file twice in the same location. So it kept failing. There are very limited options to go about undoing it now.

A few weeks ago, I wrote a post that talks about moving system databases. In that I mention about some start up parameters that allow to start Sql Server in minimal configuration by limiting recovery to just master database.

• NET START MSSQLSERVER /f /T3608 or
• NET START MSSQL$instancename /f /T3608

So, I started a batch file with these start up parameters and immediately followed it with with correct ALTER DATABASE query for tempdb. So as soon as master database is started, I ran

[sourcecode language=”sql”]
—
—  Correct
—
ALTER DATABASE [tempdb]
MODIFY FILE (NAME = ‘tempdev’, FILENAME = ‘E:\Data\tempdb.MDF’)
GO
ALTER DATABASE [tempdb]
MODIFY FILE (NAME = ‘templog’, FILENAME = ‘E:\Data\templog.LDF’)
GO
[/sourcecode]

After correctly assigning files for the tempdb, Sql Server came up correctly.

Lesson re-learnt: Test and re-test any major code before running on production and write technical blogs to help yourself (if not others).

Hope this helps,
_SqlTimes