We’ve had this Windows update: “GDR 1617 for SQL Server 2008 R2 (KB2494088)” take down three sites so far:
This SQL Server update 1617 changes the schema of the master, msdb and other system databases used by SQL Server. It changes the version of SQL from 10.50.someversion.0 to 10.50.1617.0
If there is ANY corruption in those databases prior to the update being applied, the update fails and leaves the server in an UNUSABLE state. The SQL Service starts, then stops.
When the SQL Server starts again, the SQL Service will start, then stop. The event viewer will show error 912:
Script level upgrade for database ‘master’ failed because upgrade step ‘sqlagent100_msdb_upgrade.sql’ encountered error 15281, state 1, severity 16. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the ‘master’ database, it will prevent the entire SQL Server instance from starting. Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion.
UPDATED: 2011-06-23. My original solution (see below) was to remove the update, restore from backup which worked. Another reader of my Blog was able to find the real root cause of the error – the DefaultData and DefaultLog registry settings located under HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQLServer
If either of those paths do not exist, or they exist but in the registry the values end in a backslash, then the SQL service will not start. On one of my dev machines the path was there, but ended in a backslash and removing the backslash was all I needed to do to get it working again!
UPDATED: 2011-06-24: After learning about the path issue above, I went back and checked the Registry Paths on my other customer’s SQL Servers that I had fixed with the method described below. It turns out that on those customers the paths already were correct and valid, so while the registry paths issue could be one cause, sometimes the solution I originally outlined below is still required.
Here’s the original solution that worked for me also – but did not address the registry issue:
To solve the problem, here’s what I did. Your solution may be different:
Since this update changes the schema of the master and msdb databases, you won’t be able to restore any backup you have of them unless you first remove the update. If you skip this step your restore operations will fail.
Programs and Features – choose the option to view installed updates. Sort the list by Install Date.
Select the GDR 1617 update and choose the uninstall button.
2. Put SQL into special recovery mode:
Open the SQL Server Configuration Manager, right-click on the instance in question, properties:
Add -T902;in front of the other startup parameters – DO NOT FORGET THE SEMICOLON! ;
3. Start the SQL Service
4. Connect using Management Studio, then open Query Window
5. Run SQL Command DBCC CheckDB() for each of these databases:
6. If any of these report consistency errors, then restore them from backup. Then run DBCC CheckDB() again.
a. If the files are still corrupt, then you may have to run DBCC CheckDB(msdb, allowdataloss option here syntax in checkdb results from previous step). (Only do this if you have no choice – i.e. because you don’t have a non-corrupt backup).
On my server, neither master nor msdb reported any corruption. I just had to restore the master database from backup.
To do that:
1. Go back into the service properties startup parameters and also add a -m;
So now it starts with:
Open management studio
Restore the master database:
Note: with master it won’t appear in the list – you have to type “master” yourself.
Turn on the option to Overwrite the existing database (WITH REPLACE).
When the master database is done restoring, the SQL Service will automatically stop. Since you’re doing this from the graphical user interface it will appear as if it failed. IT DID NOT FAIL.
Open the startup parameters again:
Remove the -m;-T902; parameters you added earlier.
Start the SQL Service.
If the SQL Service stays up you’re done. You’ve recovered from the issue.