7

I have configured Log Shipping where the primary server database has 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?

Paul White
  • 94,921
  • 30
  • 437
  • 687
SQL_NoExpert
  • 1,107
  • 1
  • 21
  • 37

1 Answers1

7

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 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.

Paul White
  • 94,921
  • 30
  • 437
  • 687