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.
http://support.microsoft.com/kb/822852
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.