Script level upgrade for database ‘master’ failed because upgrade step ‘sqlagent100_msdb_upgrade.sql’ encountered error 5831 – During SQL 2008 R2 SP2 installation

The problem

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.

The Solution

I resolved this issue by following the steps below:

  1. Uninstall the entire SQL Instance from the machine.
  2. Remove the remaining system MDF’s and LDF’s, but leave the user database files where they are.
  3. Install a fresh SQL Instance, use the same options as used during the original installation.
  4. Upgrade the Installation to the build version prior to the installation of the SP.
  5. Restore the master database from backup. (See previous blog post on instructions)
  6. 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….

About these ads

About sqlsmurf
MS SQL Administrator

4 Responses to Script level upgrade for database ‘master’ failed because upgrade step ‘sqlagent100_msdb_upgrade.sql’ encountered error 5831 – During SQL 2008 R2 SP2 installation

  1. Interesting. So min server memory can’t really be set to a value more than max server memory either through GUI or sp_configure. GUI resets it to be equal to max server memory if you specify a higher value for mi server memory. But the hack is – use sp_configure to just configure min server memory a higher value than max server memory. Configured value should show as higher however run_value remains <= max server memory. The next time SQL server would start up (in this case, it seems to be a con-incidence that SP2 was applied hence a SQL restart needed) SQL would try to reconfigure the min server value and the error 5831.

    I'm wondering why below won't work. This should avoid a re-install (if works)

    i. stop sql services, start with minimal configuration
    net start mssql$instance /f /T3608
    ii. Reset min server memory
    sqlcmd -E -Sserver\instance -A -Q"EXEC sp_configure 'min server memory (MB)', 0"
    iii. Stop and restart SQL services

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: