SQL SERVER – How to Check Snapshot Isolation State of Database

It is very easy to know the snapshot Isolation State of Database and here is the quick script for the same.

SELECT name
, s.snapshot_isolation_state
, snapshot_isolation_state_desc
, is_read_committed_snapshot_on
FROM sys.databases s

Upon running above code it will return the results describing the status of the isolation level for your database. Here is the screenshot which describes the same.

SQL SERVER - How to Check Snapshot Isolation State of Database isolationstate

Just on a side note, remember that READ COMMITTED SNAPSHOT does optimistic reads and pessimistic writes. Whereas, SNAPSHOT does optimistic reads and optimistic writes. It is recommended that you go for READ COMMITTED SNAPSHOT for most of your application where you want to implement row versioning. Microsoft has a detailed article on this subject over here.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
Previous Post
SQL SERVER – SQL Server 2008 R2 Service Pack 3 – Download
Next Post
SQL SERVER – UPDATE FROM SELECT Statement with Condition

Related Posts

3 Comments. Leave new

  • Very good, simple and to the point.
    However, I made one slight change by simply aliasing the name column as “database”.
    Here is the exact code I used.

    USE master;
    GO

    SELECT name AS ‘Database’,
    s.snapshot_isolation_state,
    s.snapshot_isolation_state_desc,
    is_read_committed_snapshot_on
    FROM sys.databases AS s;
    GO

    Love the simplicity of your answer.

    Reply
  • I have the snapshot_isolation_state_desc off on the Primary server but on the log shipped secondary whenever I turn it off it turns back on. My Secondary is a readonly/standby. The vendor requires this to be turned off at all times. Any suggenstions

    Reply
  • We recently switched to READ committed snapshot isolation and wanted to do some sanity testing. What are your thoughts on that?

    Reply

Leave a Reply

Menu