patternsqlMinor
Snapshot_isolation_state_desc on a Read only/Stand by Database
Viewed 0 times
standreaddatabasesnapshot_isolation_state_desconly
Problem
I have configured Log Shipping where the primary server database has
However, it turns back on every time the SQL Server restarts.
Is this expected behaviour? If so, why?
snapshot_isolation_state_desc reported as off. I tried to turn it off on the secondary which is a standby read only by using the GUI and by using the T-SQL statement to disable:ALTER DATABASE ABC SET ALLOW_SNAPSHOT_ISOLATION OFF;However, it turns back on every time the SQL Server restarts.
Is this expected behaviour? If so, why?
Solution
SQL Server always seems to set snapshot isolation (SI) on when a database is made read only. I have no idea why it does this, but it does.
You can try to turn it off, and the
One theory is that setting SI prevents unexpected errors for people running an SI transaction and accessing the read-only database, but it seems like there should be a deeper technical reason.
In any case, the behaviour is "normal", so you should not worry about it.
You can try to turn it off, and the
ALTER DATABASE statement will succeed, but snapshot isolation remains enabled while the database is read only.One theory is that setting SI prevents unexpected errors for people running an SI transaction and accessing the read-only database, but it seems like there should be a deeper technical reason.
In any case, the behaviour is "normal", so you should not worry about it.
Context
StackExchange Database Administrators Q#204830, answer score: 7
Revisions (0)
No revisions yet.