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.

The User Data directory in the registry is not valid. Verify DefaultData key under the instance hive points to a valid directory.

-> I was performing an In-Place upgrade of SQL Server from SQL Server 2017 to SQL Server 2019.

-> The upgrade failed with below error,

TITLE: Microsoft SQL Server 2019 Setup
------------------------------

The following error has occurred:

The User Data directory in the registry is not valid. Verify DefaultData key under the instance hive points to a valid directory.

For help, click: https://go.microsoft.com/fwlink?LinkID=20476&ProdName=Microsoft%20SQL%20Server&EvtSrc=setup.rll&EvtID=50000&ProdVer=15.0.4013.40&EvtType=0xD8FB5EBA%25400x97A656BB%25401306%254067

------------------------------
BUTTONS:

OK
------------------------------

-> The error states that there is some issue with the data directory.

-> I right clicked on the SQL Server instance and clicked on properties. Clicked on “Database Settings” and verified “Database default locations”. The data location mentioned is “F:\data1\”. This location is not available on the database server.

-> I corrected it to F:\data which is available on the database server and then clicked OK. Started the In-Place upgrade again and it completed fine.

-> This issue also happens during Service pack and cumulative updates install also. We should make sure that the “Data” and “Log” location is correct under “Database Settings” in SQL Server Instance properties.

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.