In MS SQL Server is there a way to detect whether a database has had its isolation level set via the T-SQL command ALTER DATABASE <database> SET READ_COMMITTED_SNAPSHOT ON;
I cannot find a simple way to detect this in either T-SQL or via the Management Studio's GUI.
TIA
This question is related to
sql-server
isolation-level
read-committed-snapshot
As per https://msdn.microsoft.com/en-us/library/ms180065.aspx, "DBCC USEROPTIONS reports an isolation level of 'read committed snapshot' when the database option READ_COMMITTED_SNAPSHOT is set to ON and the transaction isolation level is set to 'read committed'. The actual isolation level is read committed."
Also in SQL Server Management Studio, in database properties under Options->Miscellaneous there is "Is Read Committed Snapshot On" option status
Neither on SQL2005 nor 2012 does DBCC USEROPTIONS
show is_read_committed_snapshot_on
:
Set Option Value
textsize 2147483647
language us_english
dateformat mdy
datefirst 7
lock_timeout -1
quoted_identifier SET
arithabort SET
ansi_null_dflt_on SET
ansi_warnings SET
ansi_padding SET
ansi_nulls SET
concat_null_yields_null SET
isolation level read committed
Source: Stackoverflow.com