6

One of vendor software needed to install on a test server for POC. It takes forever when the setup.exe is trying to alter one of the databases with SET ALLOW_SNAPSHOT_ISOLATION ON.

The test server was on Microsoft SQL Server 2016 (SP1-CU8), developer edition.

After rolling back to SP1-CU5, SET ALLOW_SNAPSHOT_ISOLATION ON worked.

Has anyone experienced the same behavior? Any work around besides rolling back the CU?

There was no blocking and no other users at all. POC is in isolated lab environment. Tried on 2 other dev SQL Servers in live environment with 2016 SP1 CU7, experienced same behavior.

0

2 Answers 2

1

We were able to track down the cause of the behavior with help from Microsoft. It was related to audits created in earlier CU's. If I stopped and recreated the audits after applying the latest CU, the issue went away.

https://support.microsoft.com/en-us/help/4090966/sql-server-session-hangs-when-you-try-to-enable-snapshot-isolation

6

I ran into this exact situation and can confirm there was no blocking in my case as the setup basically altered the snapshot isolation immediately after creating the database.

In my case the wait type was ENABLE_VERSIONING.

When investigating I found this feedback item Enabling snapshot isolation in 2016 SP1 CU6 and CU7 hangs which is still under investigation.

One of the comments seem to suggest a SERVER AUDIT could be the case.

If you don't have a server audit running I'm afraid your option for now will be to roll back to an older CU and watch that feedback item for updates.

1
  • 3
    I tested and confirm that by turning SERVER AUDIT off on 2016 SP1 CU8 and SP2, SET ALLOW_SNAPSHOT_ISOLATION ON worked as expected otherwise it would just hang. Thanks Tom V.
    – donger
    Commented Apr 25, 2018 at 17:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.