SQL Server 2000 cannot start after windows update reboot

Last night we had an incident with one of our customers’  old SQL Server 2000 instances. The machine in question had had it’s WSUS windows update run last night and had been forced to reboot after this had occurred. After this happened the SQL Server service refused to start and just got stuck in a cycle of permanent restarts. When we looked at the error logs we saw the following repeated symptom.

2011-09-12 11:33:00.59 server    Microsoft SQL Server  2000 – 8.00.2039 (Intel X86)
May  3 2005 23:18:38
Copyright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

2011-09-12 11:33:00.59 server    Copyright (C) 1988-2002 Microsoft Corporation.
2011-09-12 11:33:00.59 server    All rights reserved.
2011-09-12 11:33:00.59 server    Server Process ID is 1284.
2011-09-12 11:33:00.59 server    Logging SQL Server messages in file ‘C:\Program Files (x86)\Microsoft SQL Server\MSSQL\log\ERRORLOG’.
2011-09-12 11:33:00.59 server    SQL Server is starting at priority class ‘normal'(4 CPUs detected).
2011-09-12 11:33:00.60 server    SQL Server configured for thread mode processing.
2011-09-12 11:33:00.62 server    Using dynamic lock allocation. [2500] Lock Blocks, [5000] Lock Owner Blocks.
2011-09-12 11:33:00.68 server    Attempting to initialize Distributed Transaction Coordinator.
2011-09-12 11:33:02.84 spid3     Starting up database ‘master’.
2011-09-12 11:33:02.85 server    Using ‘SSNETLIB.DLL’ version ‘8.0.2039’.
2011-09-12 11:33:02.85 spid5     Starting up database ‘model’.
2011-09-12 11:33:02.85 spid3     Server name is ‘XXXXXXXXXX’.
2011-09-12 11:33:02.85 spid8     Starting up database ‘msdb’.
2011-09-12 11:33:02.85 spid9     Starting up database ‘removedforsecurity’.
<SNIP for brevity>
2011-09-12 11:33:02.85 spid23    Starting up database ‘removedforsecurity’.
2011-09-12 11:33:02.85 spid5     Bypassing recovery for database ‘model’ because it is marked IN LOAD.
2011-09-12 11:33:02.85 server    SQL server listening on <removedforsecurity>: xxxx.
2011-09-12 11:33:02.85 server    SQL server listening on <removedforsecurity>: xxxx.
2011-09-12 11:33:02.87 spid20    Starting up database ‘removedforsecurity’.
2011-09-12 11:33:02.89 spid5     Database ‘model’ cannot be opened. It is in the middle of a restore.

The important line here is the last one. Model cannot be opened as it’s marked IN LOAD, this in turn means that tempdb can’t be created which in turn means that the service cannot start.

Quite how model became to be status IN LOAD we’ll never know. I went back through the logs and there was nothing suspicious and no-one has actually attempted to restore it. Circumstantially the evidence points to something being corrupted by windows update, as this is when the problem started, but retrospectively we’ll never be able to say. The MDF and LDF files themselves were intact and seemingly OK, so it was time for a manual attempt to try and get the service back.

The way to recover this (and to troubleshoot it initially) is rather dirty. You have to start the server in single user mode at the console and pass it a couple of trace flags to get you into the system catalogues. You should only ever do this as a last result, but this was a last resort, as it was either do this, or system state restore the entire machine. (I couldn’t restore the SQL Server database as it wouldn’t actually start.) The nature of starting a service in console / single user mode is common enough for SQL machines that won’t start, as you get stuck in a chicken and egg scenario of needing to start it to see why it won’t start! However update the system tables are your peril. In later versions of SQL Server (2005 upwards) the system tables are actually obscured behind views in a separate protected database to make this harder to do, although you can still do it.

The following KB article has a good description of how to do this.

The symptoms are the same but the cause was different. That said the solution is also the same in that you have to manually update the status of the database in sysdatabases and then restart the server. There’s no guarantee of success here as it could have been that the MDF file was actually corrupted, but luckily for me it did work and I was saved during a full system restore of the server.