Script level upgrade for database ‘master’ failed because upgrade step ‘sqlagent100_msdb_upgrade.sql’ encountered error 5831 – During SQL 2008 R2 SP2 installation
September 17, 2012 4 Comments
I recently installed SP2 on a SQL 2008 R2 Instance, after which it wouldn’t startup anymore. Below a relevant portion of the error log:
2012-09-15 07:22:23.26 spid7s Database 'master' is upgrading script 'sqlagent100_msdb_upgrade.sql' from level 0 to level 2. 2012-09-15 07:22:23.26 spid7s ---------------------------------------- 2012-09-15 07:22:23.26 spid7s Starting execution of PREINSTMSDB100.SQL 2012-09-15 07:22:23.26 spid7s ---------------------------------------- 2012-09-15 07:22:24.19 spid7s Setting database option COMPATIBILITY_LEVEL to 100 for database msdb. 2012-09-15 07:22:24.47 spid7s Configuration option 'allow updates' changed from 1 to 1. Run the RECONFIGURE statement to install. 2012-09-15 07:22:24.47 spid7s Configuration option 'allow updates' changed from 1 to 1. Run the RECONFIGURE statement to install. 2012-09-15 07:22:24.47 spid7s Error: 5831, Severity: 16, State: 1. 2012-09-15 07:22:24.47 spid7s Minimum server memory value (2048) must be less than or equal to the maximum value (1023). 2012-09-15 07:22:24.47 spid7s Error: 912, Severity: 21, State: 2. 2012-09-15 07:22:24.47 spid7s Script level upgrade for database 'master' failed because upgrade step 'sqlagent100_msdb_upgrade.sql' encountered error 5831, 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. 2012-09-15 07:22:24.49 spid7s Error: 3417, Severity: 21, State: 3. 2012-09-15 07:22:24.49 spid7s Cannot recover the master database. SQL Server is unable to run. Restore master from a full backup, repair it, or rebuild it. For more information about how to rebuild the master database, see SQL Server Books Online. 2012-09-15 07:22:24.49 spid7s SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.
I resolved this issue by following the steps below:
- Uninstall the entire SQL Instance from the machine.
- Remove the remaining system MDF’s and LDF’s, but leave the user database files where they are.
- Install a fresh SQL Instance, use the same options as used during the original installation.
- Upgrade the Installation to the build version prior to the installation of the SP.
- Restore the master database from backup. (See previous blog post on instructions)
- Restore the msdb database from backup. (Don’t forget to enable the Service Broker and Trustworthy options)
At this point, the SQL Instance is up again with all the user databases and with the original build version.
Now it’s time to fix the issue which caused the SP2 installation to break the instance. In my case, as you can read in the error log, this was caused by the Max Memory setting being higher than the Min Memory settings. This causes the RECONFIGURE statement to fail during the rollup script. After changing the configuration, the installation of SP2 succeeded.
Before doing the above, you could give trace flag 902 a try, see below in the comments….