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.