How to detect READ_COMMITTED_SNAPSHOT is enabled?
In Microsoft 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.
SELECT is_read_committed_snapshot_on FROM sys.databases
WHERE name= 'YourDatabase'
Return value:
-
1:
READ_COMMITTED_SNAPSHOT
option is ON. Read operations under theREAD COMMITTED
isolation level are based on snapshot scans and do not acquire locks. -
0 (default):
READ_COMMITTED_SNAPSHOT
option is OFF. Read operations under theREAD COMMITTED
isolation level use Shared (S) locks.
- As per DBCC USEROPTIONS (Transact-SQL):
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