How to detect READ COMMITTED SNAPSHOT is enabled

Question

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  lt database gt  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

User · Answer

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

User · Answer

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

User · Answer

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

[sql-server] How to detect READ_COMMITTED_SNAPSHOT is enabled?

Examples related to sql-server

Examples related to isolation-level

Examples related to read-committed-snapshot