sys.dm_clr_properties (Transact-SQL)

Applies to: SQL Server Azure SQL Managed Instance Analytics Platform System (PDW)

Returns a row for each property related to SQL Server common language runtime (CLR) integration, including the version and state of the hosted CLR. The hosted CLR is initialized by running the CREATE ASSEMBLY, ALTER ASSEMBLY, or DROP ASSEMBLY statements, or by executing any CLR routine, type, or trigger. The sys.dm_clr_properties view does not specify whether execution of user CLR code has been enabled on the server. Execution of user CLR code is enabled by using the sp_configure stored procedure with the clr enabled option set to 1.

The sys.dm_clr_properties view contains the name and value columns. Each row in this view provides details about a property of the hosted CLR. Use this view to gather information about the hosted CLR, such as the CLR install directory, the CLR version, and the current state of the hosted CLR. This view can help you determine if the CLR integration code is not working because of problems with the CLR installation on the server computer.

Properties

The directory property indicates the directory that the .NET Framework was installed to on the server. There could be multiple installations of .NET Framework on the server computer and the value of this property identifies which installation SQL Server is using.

The version property indicates the version of the .NET Framework and hosted CLR on the server.

The sys.dm_clr_properties dynamic managed view can return six different values for the state property, which reflects the state of the SQL Server hosted CLR. They are:

• Mscoree is not loaded.

• Mscoree is loaded.

• Locked CLR version with mscoree.

• CLR is initialized.

• CLR initialization permanently failed.

• CLR is stopped.

The Mscoree is not loaded and Mscoree is loaded states show the progression of the hosted CLR initialization on server startup, and are not likely to be seen.

The Locked CLR version with mscoree state may be seen where the hosted CLR is not being used and, thus, it has not yet been initialized. The hosted CLR is initialized the first time a DDL statement (such as CREATE ASSEMBLY (Transact-SQL)) or a managed database object is executed.

The CLR is initialized state indicates that the hosted CLR was successfully initialized. Note that this does not indicate whether execution of user CLR code was enabled. If the execution of user CLR code is first enabled and then disabled using the Transact-SQL sp_configure stored procedure, the state value will still be CLR is initialized.

The CLR initialization permanently failed state indicates that hosted CLR initialization failed. Memory pressure is a likely cause, or it could also be the result of a failure in the hosting handshake between SQL Server and the CLR. Error message 6512 or 6513 will be thrown in such a case.

The CLR is stopped state is only seen when SQL Server is in the process of shutting down.

Remarks

The properties and values of this view might change in a future version of SQL Server due to enhancements of the CLR integration functionality.

Permissions

On SQL Server and SQL Managed Instance, requires VIEW SERVER STATE permission.

On SQL Database Basic, S0, and S1 service objectives, and for databases in elastic pools, the server admin account, the Microsoft Entra admin account, or membership in the ##MS_ServerStateReader## server role is required. On all other SQL Database service objectives, either the VIEW DATABASE STATE permission on the database, or membership in the ##MS_ServerStateReader## server role is required.

Permissions for SQL Server 2022 and later

Requires VIEW SERVER PERFORMANCE STATE permission on the server.

Examples

The following example retrieves information about the hosted CLR:

SELECT name, value   
FROM sys.dm_clr_properties;  

See also

Dynamic Management Views and Functions (Transact-SQL)
Common Language Runtime Related Dynamic Management Views (Transact-SQL)