Share via

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

  • Article

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

SQL Server 2012 : Script level upgrade for database 'master' failed because upgrade step 'msdb110_upgrade.sql'

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

 

A newer version of this post is available in This Link

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.

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: 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'

           GO

           RECONFIGURE WITH OVERRIDE

           GO

          }

2. SQL Server not able to create temp_MS_AgentSigningCertificate_database.mdf

Error:

{

Directory lookup for the file "P:\Data\temp_MS_AgentSigningCertificate_database.mdf" failed with the operating system error 2(The system cannot find the file specified.).

Error: 1802, Severity: 16, State: 1.

CREATE DATABASE failed. Some file names listed could not be created. Check related errors.

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

spid7s      Script level upgrade for database 'master' failed because upgrade step 'sqlagent100_msdb_upgrade.sql' encountered error 598, state 1, severity 25.

CREATE FILE encountered operating system error 3(The system cannot find the path specified.) while attempting to open or create the physical file 'Q:\Data\temp_MS_AgentSigningCertificate_database_log.LDF'.

}

This error is raised when the default database location is invalid. Edit below registry to have a valid directory for default database location.

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.<Instance Name>\Setup\SQLDataRoot

 

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

{

               EXEC sp_change_users_login 'Report';

}

 

 

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.

If you liked this post, do like us on FaceBook at https://www.facebook.com/mssqlwiki and join our FaceBook group https://www.facebook.com/mssqlwiki#!/groups/454762937884205/

Regards,

Karthick P.K

Technical Lead

Microsoft SQL Server Support

Comments

  • Anonymous
    February 12, 2012
    Thanks Karthick. We were close to rebuilding the sql instances when we tries ur steps. Saved so much work. Microsoft Documentation phuckers - Please have a KB for this issue and have it fast published.

  • Anonymous
    May 08, 2015
    We just applied SP3 on SQL Server 2008 R2 Enterprise Ed. on our development servers, two out of the four dev servers failed to starting after patching.  I was able to resolve the issue, and decide to post this as I did not find any posting to exactly what my issue was.   The error we received was: Script level upgrade for database 'master' failed because upgrade step 'sqlagent100_msdb_upgrade.sql' encountered error 574, state 0, severity 16. Resolution: After implementing the -T902 trace flag to start the instance, found user schema's in the MSDB database that were owned by DBO.   I removed those schema's and then removed the trace flag, restarted the instance and the patch install completed successfully.   It took about 4 hours to figure this one out.  Good luck, and hope this helps. Chris B.