ALTER DATABASE SET ALLOW_SNAPSHOT_ISOLATION ON takes forever

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.

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.

StackExchange Database Administrators Q#204610, answer score: 6