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.
Thanks, helped me fix my issue.
Thank you very much you have saved a ton of work. We were looking at having to rebuild our SQL server from scratch.
The cause is not the master/msdb db corruption and not the update as such – there are other CUs and/or upgrades which end up with the same. It’s exactly one of those cases when an error message gives wrong hints and guids a wrong direction for fixing the situation, as a result. The solution is much, much simplier – it’s all about fixing values in a couple of registry keys.
The cause is that the local directory for data file and transaction log file, specified in registry differs from what it is in DefaultData and DefaultLog under HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.\MSSQLServer
Either values for DefultData or DefaultLog or both have non-existing directories.
The solution: either create the dirs specified in DefultData/DefaultLog or change the values to the exising direcories for Data and Transaction Log Files. This will do it. Now the SQL Server service will start as usual; no need to change any startup parameters.
Stan, thanks for that information. I have another machine (a desktop development computer) that is also affected by this same issue but doesn’t have any master or msdb backups available. When I tried your solution
In that folder my DefaultData parameter was pointing to C:\Data\SQL2008R2\ — a folder that does exist. The same value (with a trailing backslash) also existed for the DefaultLog folder. I removed the trailing backslash from both, started the SQL Service and it STAYED UP!!!. Unbelievable!
System is back up now. Thank you for your help!
But what to do with KB2494088 going forward? Do you just not install it ever again?
The registry fix worked great for me. Thanks for taking the time to post this!!!
We have a clustered environment and we had to add the drive that contains the master DB as a sql server dependency in order for the update to work.