How to detect READ_COMMITTED_SNAPSHOT is enabled?

Question

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.

Answer 1

SELECT is_read_committed_snapshot_on FROM sys.databases 
WHERE name= 'YourDatabase'

Return value:

• 1: READ_COMMITTED_SNAPSHOT option is ON. Read operations under the READ COMMITTED isolation level are based on snapshot scans and do not acquire locks.
• 0 (default): READ_COMMITTED_SNAPSHOT option is OFF. Read operations under the READ COMMITTED isolation level use Shared (S) locks.

Answer 2

1. 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.

2. Also in SQL Server Management Studio, in database properties under Options->Miscellaneous there is "Is Read Committed Snapshot On" option status