SQL Server 2008 R2 does not start when applying certain hotfix updates

We have noticed that when you install certain hotfixes (this includes Cumulative Update 1 and certain versions of security update MS11-049 etc. ) on an SQL Server 2008 R2 RTM Instance on which Utility Control Point is configured, the installer fails to apply the upgrade scripts and SQL Server does not start. You will see the following messages in the SQL Server error log:

2011-06-17 14:35:11.94 spid7s Executing [sysutility_mdw].sysutility_ucp_core.sp_initialize_mdw_internal
2011-06-17 14:35:13.08 spid7s SQL Server blocked access to procedure 'sys.xp_qv' of component 'Agent XPs' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Agent XPs' by using sp_configure. For more information about enabling 'Agent XPs', see "Surface Area Configuration" in SQL Server Books Online.
2011-06-17 14:35:13.08 spid7s Error: 15281, Severity: 16, State: 1.
2011-06-17 14:35:13.08 spid7s SQL Server blocked access to procedure 'sys.xp_qv' of component 'Agent XPs' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Agent XPs' by using sp_configure. For more information about enabling 'Agent XPs', see "Surface Area Configuration" in SQL Server Books Online.
2011-06-17 14:35:13.15 spid7s Error: 912, Severity: 21, State: 2.
2011-06-17 14:35:13.15 spid7s 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.
2011-06-17 14:35:13.27 spid7s Error: 3417, Severity: 21, State: 3.
2011-06-17 14:35:13.27 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.

This is a known issue that has been documented in KB: https://support.microsoft.com/kb/2163980 and fixed in Cumulative Update 2 for SQL Server 2008 R2 RTM .

Here are actions you can take to prevent this from happening when applying a hotfix or corrective actions to start SQL Server back if in a failed state.

ALREADY IN FAILED STATE:

If the SQL Server service is already in a failed state (for example: an attempt to install the security patch or CU1 was made), follow the steps mentioned in the Workaround section of the KB 2163980 to recover from the problem.

Once you implement the workaround and restart SQL Server the upgrade will complete successfully.

PATCH NOT YET APPLIED (STEPS TO PREVENT FAILURE FOR MANUAL UPDATES):

If the patch has not yet been applied and you want to prevent this issue from happening, you can follow these steps:

1. Verify the version of your SQL Server 2008 R2 instance. Also, check if the instance is configured for UCP, run the following query:

declare @isUCP bit

select @isUCP=msdb.dbo.fn_sysutility_get_is_instance_ucp()

select (case(@isUCP) when 0 then 'This is not a UCP instance.' else 'This is a UCP instance.' end)

If SQL Server version is < 10.50.1720.0, and if this instance of SQL Server is a UCP instance, then you will be impacted by this issue. Proceed further with rest of the steps below.

NOTE: For MS11-049, which is the recently released security update for SQL Server, this issue is specific to KB 2494088 version of the fix for SQL Server 2008 R2 which is applicable only for server instances on RTM version of the product. If you already have applied a Cumulative Update for the instance, then you will need to apply KB2494086 of the security fix. Since KB2494086 includes the fix from KB 2163980, installation of that KB 2494086 version will not run into this issue.

2. Stop SQL Server Agent service for the associated instance. If it is a clustered instance, take the SQL Server Agent resource offline.

This is a critical step - if SQL Server Agent service is running when setup is launched, it will be stopped during setup which in-turn disables Agent XPs sp_configure option resulting in the above failures.

3. Log on to SQL Server and make sure sp_configure option 'Agent XPs' is enabled.

4. Run the setup to install the security fix (MS11-049) or Cumulative update 1.

5. Start the SQL Server Agent service once the patch is installed successfully.

6. Repeat steps 1-5 for every SQL Server 2008 R2 instance.

Ajay Jagannathan | Microsoft SQL Server Escalation Services