Symptoms
Consider the following scenario:
-
You have an instance of Microsoft SQL Server 2008 R2 that hosts a user control point (UCP).
-
You install cumulative update package 1 for SQL Server 2008 R2 on the instance.
In this scenario, the installation succeeds. However, the SQL Server service cannot start. Additionally, the following error messages are logged in the SQL Server Errorlog file:
<Date>
<Time> spid7s Executing [sysutility_mdw].sysutility_ucp_core.sp_initialize_mdw_internal
<Date><Time> 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.
<Date><Time> spid7s Error: 15281, Severity: 16, State: 1.
<Date><Time> 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.
<Date><Time> spid7s Error: 912, Severity: 21, State: 2.
<Date><Time> 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.
<Date><Time> spid7s Error: 3417, Severity: 21, State: 3.
<Date><Time> 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.<Date><Time> spid7s SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.
This problem may also occur when you upgrade an instance of SQL Server 2008 R2 Release Candidate (RC) that hosts a UCP to the release version of SQL Server 2008 R2.
Cause
This problem occurs because the Agent XPs option is not enabled when SQL Server 2008 R2 runs the upgrade script for the msdb database.
This script runs when the SQL Server service starts. The script runs a stored procedure that is related to the UCP. Additionally, the stored procedure requires that the Agent XPs option be enabled.
Resolution
Cumulative update information
The fix for this issue was first released in Cumulative Update 2. For more information about how to obtain this cumulative update package for SQL Server 2008 R2, click the following article number to view the article in the Microsoft Knowledge Base:
2072493 Cumulative update package 2 for SQL Server 2008 R2Note Because the builds are cumulative, each new fix release contains all the hotfixes and all the security fixes that were included with the previous SQL Server 2008 R2 fix release. We recommend that you consider applying the most recent fix release that contains this hotfix. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
981356 The SQL Server 2008 R2 builds that were released after SQL Server 2008 R2 was released
Workaround
Note If you have not installed cumulative update Package 1 yet, you should follow the "Resolution" section to install cumulative update package 2. This problem does not exist in cumulative update package 2 and later versions of update for SQL Server 2008 R2.
If you experience this problem after you install cumulative update package 1, follow these steps to work around this problem:
-
Enable trace flag 902 on the instance of SQL Server 2008 R2. To do this, follow these steps:
-
Open SQL Server Configuration Manager.
-
In SQL Server Configuration Manager, click SQL Server Services.
-
Double-click the SQL Serverservice.
-
In the SQL Server Properties dialog box, click the Advanced tab.
-
On click the Advanced tab, locate the Startup Parameters item.
-
Add ;-T902 to the end of the existing string value, and then click OK.
-
-
Right-click the SQL Serverservice, and then click Start.
-
If the SQL Server Agent service is running, right-click the SQL Server Agent service, and then click Stop.
-
Open SQL Server Management Studio, and then connect to the instance of SQL Server 2008 R2.
-
Run the following statements:
EXEC sp_configure 'show advanced', 1;
RECONFIGURE;
EXEC sp_configure 'allow updates', 0;
RECONFIGURE;
EXEC sp_configure 'Agent XPs', 1;
RECONFIGURE;
GO -
In SQL Server Configuration Manager, right-click the SQL Serverservice, and then click Stop.
-
Remove trace flag 902 on the instance of SQL Server 2008 R2. To do this, delete ;-T902 from the string value that you updated in step 1f.
-
Right-click the SQL Serverservice, and then click Start.
-
Right-click the SQL Server Agent service, and then click Start.
-
In SQL Server Management Studio, reconnect to the instance of SQL Server 2008 R2.
-
In Object Explorer, expand Management, right-click Data Collection, and then click Enable Data Collection.
Note If data collection is already enabled, the Enable Data Collection item is unavailable.
Note You may receive the following error message when you connect to the instance of SQL Server 2008 R2 in SQL Server Management Studio:
Error: 18401
Login failed for user '<login name>'. Reason: Server is in script upgrade mode. Only administrator can connect at this time.
This error message indicates that SQL Server 2008 R2 is completing the installation of cumulative update package 1. If you receive this error message, wait several minutes and then connect to the instance of SQL Server 2008 R2 again.
Status
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.
References
For information about the Incremental Servicing Model for SQL Server, click the following article number to view the article in the Microsoft Knowledge Base:
935897 An Incremental Servicing Model is available from the SQL Server team to deliver hotfixes for reported problems
For more information about the naming schema for SQL Server updates, click the following article number to view the article in the Microsoft Knowledge Base:
822499
Naming schema for Microsoft SQL Server software update packages
For more information about software update terminology, click the following article number to view the article in the Microsoft Knowledge Base:
824684
Description of the standard terminology that is used to describe Microsoft software updates
