SQLServer2008: Script level upgrade for database 'master' failed because upgrade step 'sqlagent100_msdb_upgrade.sql' encountered error 574, state 0, severity 16

SQL Server 2008 instance fails to start or hangs after service pack or Cumulative update installation.

Error

Script level upgrade for database 'master' failed because upgrade step 'sqlagent100_msdb_upgrade.sql' encountered error 574, state 0, 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: 574, Severity: 16, State: 0.

CONFIG statement cannot be used inside a user transaction.

Error: 912, Severity: 21, State: 2.

Error: 3417, Severity: 21, State: 3.

Resolution

Start SQLServer from command prompt using trace flag –T902 to disable script execution

1.Turn off Implicit transaction

{

EXEC sys.sp_configure N'user options', N'0'

RECONFIGURE WITH OVERRIDE

}

2. Check if there are Orphan users in system databases and fix them.

{

EXEC sp_change_users_login 'Report';

}

3. SQLServer not able to create temp_MS_AgentSigningCertificate_database.mdf

Refer: http://blogs.msdn.com/b/sqlserverfaq/archive/2010/10/27/sql-server-2008-service-fails-to-start-after-applying-service-pack-1.aspx

If none of the above resolves the issue then you can use Trace flag -T3601 which causes the first 512 characters of each batch being executed to be printed to the error log. Identify the batch which is failling and troubleshoot the batch.

Regards,

Karthick P.K

Technical Lead

Microsoft SQL Server Support