sys.dm_clr_properties (Transact-SQL)
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.
Column name |
Data type |
Description |
|---|---|---|
name |
nvarchar(128) |
The name of the property. |
value |
nvarchar(128) |
Value of the property. |
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
Requires VIEW SERVER STATE permission on the server.
Examples
The following example retrieves information about the hosted CLR:
select name, value
from sys.dm_clr_properties