ALLOW_SNAPSHOT_ISOLATION hanging in SQL Server 2016

In test environment I had SQL Server 2016 with RTM build (13.0.1601.5). Dev team asked to change option for one of the databases and set  ALLOW_SNAPSHOT_ISOLATION to ON as they wanted to test how much impact this may cause in certain scenario.

Usually change of this database setting takes ca. blink of eye but in this case it didn’t complete in seconds, minutes then hours…

Only wait type I saw was “ENABLE_VERSIONING” which “Occurs when SQL Server waits for all update transactions in this database to finish before declaring the database ready to transition to snapshot isolation allowed state. This state is used when SQL Server enables snapshot isolation by using the ALTER DATABASE statement.

“as explained by https://msdn.microsoft.com/en-us/library/ms179984.aspx

It made sense first all update transactions must complete and newly started transactions will be able to use versioning. Trouble was there were no open  transactions in this database. Lucky for me  it was TEST/DEV machine so to make sure there were no weird transactions I have restarted first SQL Server service. It didn’t help  to solve to problem so I have tried to change this option on database I have created only for this purpose. Guess what? It also didn’t succeed…

Actual solution that resolved the problem was installation of CU1 for SQL Server 2016. After CU1 has been applied setting ALLOW_SNAPSHOT_ISOLATION database option took  less then blink of an eye.

Author: G L

To ja. Pisarz co pisze na blogu

Leave a Reply

Your email address will not be published. Required fields are marked *