SQL Server session hangs when you try to enable snapshot isolation in SQL Server 2016

Applies To
SQL Server 2016 Developer - duplicate (do not use) SQL Server 2016 Standard - duplicate (do not use) SQL Server 2016 Enterprise Core - duplicate (do not use) SQL Server 2016 Enterprise - duplicate (do not use) SQL Server 2016 Service Pack 1

Symptoms

After you install SQL Server SP1 Cumulative Update 6 (CU6), SQL Server session hangs indefinitely when you try to enable snapshot isolation by using the following statements:

ALTER DATABASE DatabaseName SET ALLOW_SNAPSHOT_ISOLATION ON

Note This issue may occur when you have a server audit started on the instance which is defined with an audit specification that includes DATABASE_CHANGE_GROUP.

Workaround

To work around this issue, temporarily stop the audit. The following are the statements:

  • Stop the audit:
    ALTER SERVER AUDIT AuditName WITH (STATE = OFF) GO
  • Change the database isolation level:
    ALTER DATABASE DatabaseName SET ALLOW_SNAPSHOT_ISOLATION ON
  • Turn the audit back on:
    ALTER SERVER AUDIT AuditName WITH (STATE = ON) GO

Status

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.

References

Learn about the terminology that Microsoft uses to describe software updates.