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.

score 214 · Accepted Answer · edited Feb 01 '22 at 13:31

214

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.

edited Feb 01 '22 at 13:31

Rafael Tavares

5,678
4
32
48

answered Sep 09 '08 at 14:10

Galwegian

41,475
16
112
158

7

I like "DBCC USEROPTIONS", a bit easier to remember. – ProVega Oct 29 '13 at 04:43
6

@ProVega - DBCC USEROPTIONS does not return the value of is_read_committed_snapshot_on. – Carsten Schütte May 08 '15 at 14:37

score 4 · Answer 2 · edited Feb 01 '22 at 13:30

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

score 0 · Answer 3 · edited Aug 03 '15 at 09:10

0

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

edited Aug 03 '15 at 09:10

Christian Strempfer

7,291
6
50
75

answered Jan 06 '14 at 03:44

user3164106

17
2

4

The Isolation level in `DBCC USEROPTIONS` does tell you. If it's `read committed snapshot` then it's on, otherwise it's off – Greg Oct 15 '15 at 02:05
@Greg - read committed snapshot is set at the database level, not the user connection level. DBCC USEROPTIONS won't tell you if it's on or off. – JasonS Oct 20 '15 at 16:23

How to detect READ_COMMITTED_SNAPSHOT is enabled?

3 Answers3

Linked