Problem: Recently I got an issue on SQL Server 2008 R2 instance where cumulative update Package 1 applied. SQL Services are unable to start after the CU1.
When I checked the error log, I found the below error messages in the log file.
Error: 33009, Severity: 16, State: 2.
The database owner SID recorded in the master database differs from the database owner SID recorded in database ‘msdb’. You should correct this situation by resetting the owner of database ‘msdb’ using the ALTER AUTHORIZATION statement.
Error: 912, Severity: 21, State: 2.
Script level upgrade for database ‘master’ failed because upgrade step ‘sqlagent100_msdb_upgrade.sql’ encountered error 33009, state 2, 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.
Error: 3417, Severity: 21, State: 3.
Cannot recover the master database. SQL Server is unable to run. Restore master from a full backup, repair it, or rebuild it.
Solution: As mentioned by Microsoft that CU1 has bug which is resolved in CU2 onwards version release. If you have already installed the CU1 perform the below steps to resolve the issue and if you haven’t applied CU1, install CU2.
Step 1: Add the ;-T902 parameter to startup parameter list and start the SQL Server Service.
Step 2: If the SQL Agent Service running, stop the agent service.
Step 3: Connect to SQL Server through SSMS and change MSDB database owner and configure Agent XPs parameter.
ALTER AUTHORIZATION ON DATABASE::MSDB TO SA
Agent XPs value should be 1 for CU1, you can enable it by executing below query.
EXEC sp_configure 'show advanced', 1; RECONFIGURE; EXEC sp_configure 'allow updates', 0; RECONFIGURE; EXEC sp_configure 'Agent XPs', 1; RECONFIGURE; GO
Step 4: Remove the ;-T902 from the startup parameter list. Restart the SQL Server and Agent Service.
Step 5: Remove the ;-T902 from the startup parameter list. Restart the SQL Server and Agent Service.
Step 6: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.