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.
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)
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.
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
We recently switched to READ committed snapshot isolation and wanted to do some sanity testing. What are your thoughts on that?