-> I was applying SQL Server 2019 cumulative update 8. I got an error that SQL Agent service could not be started and the setup failed.

_{Overall summary:
Final result: The patch installer has failed to update the following instance:. To determine the reason for failure, review the log files.
Exit code (Decimal): -2068709375
Exit facility code: 1202
Exit error code: 1
Exit message: The NT service ‘SQLAgent$IN2019_1’ could not be started.
Start time: 2021-01-02 12:57:47
End time: 2021-01-02 14:39:59
Requested action: Patch
Exception help link: https://go.microsoft.com/fwlink?LinkId=20476&ProdName=Microsoft+SQL+Server&EvtSrc=setup.rll&EvtID=50000&ProdVer=15.0.4073.23&EvtType=0xAE7B8BF3%400xF762992B%401202%401&EvtType=0xAE7B8BF3%400xF762992B%401202%401}

_{Rules with failures or warnings:
Rules report file: C:\Program Files\Microsoft SQL Server\150\Setup Bootstrap\Log\20210102_125747\SystemConfigurationCheck_Report.htm}

_{Exception summary:
The following is an exception stack listing the exceptions in outermost to innermost order
Inner exceptions are being indented}

_{Exception type: Microsoft.SqlServer.Chainer.Infrastructure.ChainerInvalidOperationException
Message:
The NT service ‘SQLAgent$IN2019_1’ could not be started.
HResult : 0x84b20001
FacilityCode : 1202 (4b2)
ErrorCode : 1 (0001)
Data:
HelpLink.EvtType = 0xAE7B8BF3@0xF762992B@1202@1
DisableWatson = true
Stack:
at Microsoft.SqlServer.Configuration.SetupExtension.RunPatchAllInstanceAction.RestoreServiceStates()
at Microsoft.SqlServer.Configuration.SetupExtension.RunPatchAllInstanceAction.ExecuteAction(String actionId)
at Microsoft.SqlServer.Chainer.Infrastructure.Action.Execute(String actionId, TextWriter errorStream)
at Microsoft.SqlServer.Setup.Chainer.Workflow.ActionInvocation.<>c__DisplayClass2_0.b__0()
at Microsoft.SqlServer.Setup.Chainer.Workflow.ActionInvocation.ExecuteActionHelper(ActionWorker workerDelegate)}

-> I tried starting the SQL services from SQL Server configuration manager and got below error,

SQL Server Configuration Manager
The request failed or the service did not respond in a timely fashion. Consult the event log or other applicable error logs for details.
OK

-> This seems like a generic error. I then check the SQL Server error log and found below error,

_{2021-01-02 13:08:34.98 spid10s Database ‘master’ is upgrading script ‘msdb110_upgrade.sql’ from level 251660240 to level 251662313.
2021-01-02 13:08:34.98 spid10s ———————————-
2021-01-02 13:08:34.98 spid10s Starting execution of PRE_MSDB.SQL
2021-01-02 13:08:34.98 spid10s ———————————-}

.

.

_{2021-01-02 13:08:41.09 spid10s Uploading data collector package from disk: C:\Program Files\Microsoft SQL Server\MSSQL15.IN2019_1\MSSQL\Install\SqlTraceCollect.dtsx
2021-01-02 13:08:41.09 spid10s Error: 8649, Severity: 17, State: 1.
2021-01-02 13:08:41.09 spid10s The query has been canceled because the estimated cost of this query (37) exceeds the configured threshold of 30. Contact the system administrator.
2021-01-02 13:08:41.09 spid10s Error: 912, Severity: 21, State: 2.
2021-01-02 13:08:41.09 spid10s 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.
2021-01-02 13:08:41.10 spid10s Error: 3417, Severity: 21, State: 3.
2021-01-02 13:08:41.10 spid10s 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.
2021-01-02 13:08:41.10 spid10s SQL Trace was stopped due to server shutdown. Trace ID = ‘1’. This is an informational message only; no user action is required.}

-> The script upgrade mode fails with the error “The query has been canceled because the estimated cost of this query (37) exceeds the configured threshold of 30“.

-> I added trace flag 902 to temporarily stop script upgrade mode for troubleshooting purpose. Please note that this trace flag 902 should not be allowed to run continuously in a production environment, but should be strictly used for troubleshooting purpose only.

-> I added trace flag 902 to SQL Server startup parameter and started the SQL Services,

-> Once SQL Services is started after adding trace flag 902. I right clicked SQL server instance and clicked on properties.

-> From the properties window, I navigated to “Connections” tab as below,

-> “Use query governor to prevent long-running queries” is checked and a value of 30 seconds is mentioned. This setting will result in cancelling queries that has an estimated cost of more than 30 seconds. It seems like the query that was cancelled as part of script upgrade mode might have an estimated cost of more than 30 and that would have resulted in cancelling this query.

-> Please refer this article for more details on “Use query governor to prevent long-running queries”. The cost limit option specifies an upper limit on the estimated cost allowed for a given query to run. Query cost is an abstract figure determined by the query optimizer based on estimated execution requirements such as CPU time, memory, and disk IO. It refers to the estimated elapsed time, in seconds, that would be required to complete a query on a specific hardware configuration. This abstract figure does not equate to the time required to complete a query on the running instance. It should be treated as a relative measure. The default value for this option is 0, which sets the query governor to off. Setting the value to 0 allows all queries to run without any time limitation. If you specify a nonzero, nonnegative value, the query governor disallows execution of any query that has an estimated cost that exceeds that value.

-> I unchecked this option and clicked “OK”.

-> I removed trace flag 902 and started SQL services again. It worked fine this time and the SQL server version changed to an appropriate value.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.