It is very easy to know the snapshot Isolation State of Database and here is the quick script for the same.
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)
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.
SELECT name AS ‘Database’,
FROM sys.databases AS s;
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?