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

-> 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.

Common language runtime (CLR) execution is not supported under lightweight pooling

-> I was performing a cumulative update 8 install on a SQL Server 2019 database server and got below error,

TITLE: SQL Server Setup failure.
SQL Server Setup has encountered the following error:
The NT service ‘SQLAgent$IN2019’ could not be started.

Error code 0x84B20001.
For help, click: https://go.microsoft.com/fwlink?LinkID=20476&ProdName=Microsoft%20SQL%20Server&EvtSrc=setup.rll&EvtID=50000&EvtType=0xAE7B8BF3%25400xF762992B%25401202%25401
BUTTONS:
OK

-> SQL Server 2019 Cumulative update 8 installation failed with SQL Server and SQL Server agent service offline. I tried starting SQL Server service and got below message,

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

-> Checked the setup logs from location C:\Program Files\Microsoft SQL Server\150\Setup Bootstrap\Log,

Summary.txt

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' could not be started.
Start time: 2020-12-13 12:02:39
End time: 2020-12-13 12:34:46
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

Instance IN2019 overall summary:
Final result: The patch installer has failed to update the shared features. 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' could not be started.
Start time: 2020-12-13 12:05:15
End time: 2020-12-13 12:12:25
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=0x0EE19F0D%400xF762992B%401202%401&EvtType=0x0EE19F0D%400xF762992B%401202%401

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

SQL Server log

2020-12-13 12:35:42.11 spid9s Database 'master' is upgrading script 'msdb110_upgrade.sql' from level 251660310 to level 251662313.
2020-12-13 12:35:42.11 spid9s ----------------------------------
2020-12-13 12:35:42.11 spid9s Starting execution of PRE_MSDB.SQL
2020-12-13 12:35:42.11 spid9s ----------------------------------
2020-12-13 12:35:42.12 spid9s Error: 46906, Severity: 16, State: 1.
2020-12-13 12:35:42.12 spid9s Unable to retrieve registry value 'NodeRole' from Windows registry key 'Software\Microsoft\Microsoft SQL Server\MSSQL15.IN2019\Polybase\Configuration': (null).
2020-12-13 12:35:42.22 spid9s Setting database option COMPATIBILITY_LEVEL to 100 for database 'msdb'.
2020-12-13 12:35:42.26 spid9s -----------------------------------------
2020-12-13 12:35:42.26 spid9s Starting execution of PRE_SQLAGENT100.SQL
2020-12-13 12:35:42.26 spid9s -----------------------------------------

-> It seems like we are on script upgrade mode. Script upgrade mode happens on a SQL Server instance after a Service pack or cumulative update is installed and SQL service is restarted. It basically executes the required scripts/procedures to actually upgrade instance/databases to a version as installed by service pack/cumulative update.

2020-12-13 12:35:48.18 spid9s Creating procedure [dbo].[sp_syscollector_sql_text_lookup]…
2020-12-13 12:35:48.18 spid9s Installing out of the box Collector objects
2020-12-13 12:35:48.18 spid9s
2020-12-13 12:35:48.24 spid9s Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install.
2020-12-13 12:35:48.24 spid9s Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install.
2020-12-13 12:35:48.25 spid9s Error: 5846, Severity: 16, State: 1.
2020-12-13 12:35:48.25 spid9s Common language runtime (CLR) execution is not supported under lightweight pooling. Disable one of two options: "clr enabled" or "lightweight pooling".
2020-12-13 12:35:48.25 spid9s Error: 912, Severity: 21, State: 2.
2020-12-13 12:35:48.25 spid9s Script level upgrade for database 'master' failed because upgrade step 'msdb110_upgrade.sql' encountered error 5846, state 1, 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.
2020-12-13 12:35:48.25 spid9s Error: 3417, Severity: 21, State: 3.
2020-12-13 12:35:48.25 spid9s 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.
2020-12-13 12:35:48.25 spid9s SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.

-> Error message from SQL Server error log is pretty clear that the script upgrade mode is failing with below message,

2020-12-13 12:35:48.25 spid9s Error: 5846, Severity: 16, State: 1.
2020-12-13 12:35:48.25 spid9s Common language runtime (CLR) execution is not supported under lightweight pooling. Disable one of two options: "clr enabled" or "lightweight pooling".

-> Adding trace flag 902 to SQL Server startup parameters to avoid script upgrade mode temporarily for troubleshooting purpose. Please note that this trace flag is just for troubleshooting and is not supported to run it continuously in a production environment. Please refer this article for more details on this trace flag.

-> SQL Server will be online after adding this trace flag.

-> The error that fails the script upgrade mode basically advises that either clr or lightweight pooling should be enabled.

-> Checking the status of “clr enabled” or “lightweight pooling” on the SQL Server instance,

-> From the above screenshot, it is clear that “CLR Enabled” Config_value and run_value is set to 1. This means that it was enabled and then reconfigure was also executed fine.

-> On the other hand “Lightweight Pooling” is enabled as config_value is 1, but Reconfigure is not yet executed as run_value is still 0.

-> Let us try executing a Reconfigure and see what happens,

Msg 5846, Level 16, State 1, Line 1
Common language runtime (CLR) execution is not supported under lightweight pooling. Disable one of two options: "clr enabled" or "lightweight pooling".

-> Check this article for more details on “lighweight pooling”. It also advises that CLR is not supported under lightweight pooling.

-> I disabled “lightweight pooling” using below command,

sp_configure 'lightweight pooling',0
reconfigure

-> In case you get an error as below while executing “RECONIGURE”. Try removing Trace flag 902 from SQL Server Configuration manager and restart SQL Server.

Msg 5808, Level 16, State 1, Line 1
Ad hoc update to system catalogs is not supported.

-> Once SQL Server is online make sure “lightweight pooling” is disabled and that the SQL server version has changed.

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.

The Instance ID ‘MSSQLSERVER’ is already in use by SQL Server instance ‘MSSQLSERVER.INACTIVE’

-> I was performing an In-Place upgrade of SQL Server 2017 to SQL Server 2019. When I selected the required default instance on the setup window and clicked next, I received below message and couldn’t proceed further,

[error message] The instance id 'MSSQLSERVER' is already in use by Sql Server Instance 'MSSQLSERVER.INACTIVE'. 
To continue, specify a unique instance id.

[details] microsoft.sqlserver.configuration.setupextension.instanceidisusedbyanotherinstanceexception: 
the instance id 'MSSQLSERVER' is already in use by Sql Server Instance 'MSSQLSERVER.INACTIVE'. 
To continue, specify a unique instance id.
=====================================================================

-> It seems like we have MSSQLSERVER.INACTIVE instance on the database server and that is due to an failed install before.

-> In my case the In-Place upgrade failed due to the issue discussed in this article.

-> I ran a discovery report from SQL Server 2019 setup,

-> Below is the discovery report,

-> It seems like the problematic component in my case is SQL Server Analysis services.

-> I searched for file “Datastore_Discovery.xml” on the database server and opened it. I then searched for INACTIVE and got below results,

-> I noted down the product code for the INACTIVE instance and executed below in an administrative command prompt. This uninstalls the INACTIVE instance for that particular component.

msiexec /x {F01BC425-ABD3-41AA-9B88-8B1521B2B5D0}
msiexec /x {F7F6414B-264B-4C08-830B-3E6722F5F025}

-> I started the In-Place upgrade this time and it completed fine without any issues.

-> In my case it was just SQL Server Analysis Service, but there will be instances you will see other components also. Hence it is required to look at the discovery report and remove all INACTIVE components on the database server to move forward.

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.