SQL SERVER – Script Level Upgrade for Database ‘master’ Failed Because Upgrade Step ‘msdb110_upgrade.sql’ Encountered Error 8649, State 1, Severity 17

While trying to upgrade my SQL instance to the latest patch of SQL 2017, I encountered an issue. This has happened earlier with a few of my clients earlier but this time the cause was different. In this blog we would learn about cause and the fix of upgrade error:

The query has been canceled because the estimated cost of this query (37) exceeds the configured threshold of 30. Contact the system administrator.

Here are few earlier blogs about troubleshooting same issue.

This time it was a new error, so I am blogging it. As usual, I asked for ERRORLOG and was looking for the cause of failure.

Error: 8649, Severity: 17, State: 1.
The query has been canceled because the estimated cost of this query (37) exceeds the configured threshold of 30. Contact the system administrator.
Error: 912, Severity: 21, State: 2.
Script level upgrade for database ‘master’ failed because upgrade step ‘msdb110_upgrade.sql’ encountered error 8649, state 1, severity 17. 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. For more information about how to rebuild the master database, see SQL Server Books Online.
SQL Server shutdown has been initiated
SQL Trace was stopped due to server shutdown. Trace ID = ‘1’. This is an informational message only; no user action is required.

Solarwinds

WORKAROUND/SOLUTION

Whenever we have such upgrade script failure issue and SQL is not getting started, we need to use trace flag 902 to start SQL which would bypass script upgrade mode. This would allow us the find the cause and fix it. So, here are the steps I have done.

  1. As I mentioned earlier, first we started SQL with trace flag 902. I started SQL using trace flag 902 as below via command prompt.

NET START MSSQLSERVER /T902

For named instance, we need to use below (replace instance name based on your environment)

NET START MSSQL$INSTANCENAME /T902

Refer: SQL SERVER – 2005 – Start Stop Restart SQL Server from Command Prompt

  1. As soon as SQL was started, I was able to connect because the script didn’t run.
  • In Object Explorer, right-click a server and select Properties.
  • Click the Connections page.
  • Clear the Use query governor to prevent long-running queries check box.

SQL SERVER - Script Level Upgrade for Database 'master' Failed Because Upgrade Step 'msdb110_upgrade.sql' Encountered Error 8649, State 1, Severity 17 query-gov-err-01

Here is the equivalent T-SQL:

EXEC sys.sp_configure N'query governor cost limit', N'0'
GO
RECONFIGURE WITH OVERRIDE
GO

  1. After modifying the configuration, I stopped SQL Service and started normally (without trace flag) using SQL Server Configuration Manager.

And the issue was resolved. Have you faced any such interesting issue during SQL upgrades?

Reference: Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
, , ,
Previous Post
SQL SERVER – Read Only Routing Error: Client Unable to Establish Connection Because an Error was Encountered During Handshakes Before Login
Next Post
SQL SERVER – Error in Validation: Listener in Workgroup – Unable to determine if the computer exists in the domain ‘WORKGROUP’

Related Posts

Leave a Reply

Menu