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