none
Enaling Allow_Snapshot_Isolation hangs in SQL 2016

    Question

  • SQL Server 2016 SP1, CU6 (13.0.4457.0)

    When setting allow_snapshot_isolation to ON on a database it seems to hang the session indefinitely.

    EG:

    create database junk
    alter database junk set allow_snapshot_isolation on

    If I do this the session sits with a wait type of ENABLE_VERSIONING, no blocking sessions, the reads/writes/cpu don't increase but the wait time continually resets - I.e. when I look in sys.dm_os_waiting_tasks all the blocking session/task fields are null, the wait duration might be 300ms, then next time I look at the view it's 600, then next time it's back to 100 and so forth.

    Note per above I've issued a create database and the alter in a single consecutive step just to demonstrate the issue and to show that there is absolutely nothing that could possibly be using that database at the time.

    I've seen reference in other posts to there having been an issue of this nature in RTM of SQL2016 - for example https://lewicki.online/wp/allow_snapshot_isolation-hanging-in-sql-server-2016/ - this post alleges that it was fixed in CU1 however I can't find mention of it in the list of fixes in CU1. Note however we are running the second to latest CU in SP1, again I can't see anything in SP1CU7 (which is pending install) that would suggest an issue has been fixed with the outstanding CU.

    Any thoughts on this, is there a known problem that I haven't been able to track down?

    Friday, January 19, 2018 5:23 AM

All replies

  • The plot thickens.

    This same process was run less than a week ago and worked - at that time the server was on SP1 CU5 - the following day we patched that environment up to CU6 - so looks like a bug has been introduced. I've tried on a number of our servers which are at CU6 and the same issue presents, those which have not yet been patched are not reporting the problem. In the process of installing CU7 to test if that fixes it.

    Friday, January 19, 2018 6:29 AM
  • Update - problem persists post installation of CU7.
    Friday, January 19, 2018 6:44 AM
  • No repro on CU6/CU7, but since the wait type is ENABLE_VERSIONING, i'd start identify issues with tempdb, did you see something interesting?
    Monday, January 22, 2018 1:51 AM
  • We are also seeing this on one of our instances which is SP1-CU6.   Have you made any progress or received any further information from Microsoft on this?
    Monday, January 22, 2018 3:34 PM
  • I will try to reproduce it and if I can reproduce it will try to involve some one from MS, if possible. Thanks for understanding.

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Tuesday, January 23, 2018 1:52 PM
    Moderator
  • I see it as well.  I have 24 SQL 2016 instances.  I upgraded 14 of them from CU3 to CU7 before we noticed it.

    All of the SP1 CU7 servers wait forever with trying to set Allow_Snapshot_Isolation, none of the SP1 CU3 servers have that behavior.

    I opened a support ticket with MS yesterday.  I'll post if there's a good resolution available.

    Doug


    Tuesday, January 30, 2018 8:33 PM
  • Seems to be related to the DATABASE_CHANGE_GROUP server audit spec.

    If I stopped the server audit, the issue would go away until I restarted the audit.

    If I dropped and recreated the server audit, it seemed to fix the issue and the audit would still run.



    Monday, February 5, 2018 9:08 PM
  • Thanks for the update.

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Tuesday, February 6, 2018 6:49 AM
    Moderator
  • Now I finally can stop wretching my few straws of hair from my head.  :)

    I first tried with just the db audit that was running. Didn't help. 

    Then disabled and dropped the 3 server audits as well and then it worked. 

    Version: 

    Microsoft SQL Server 2017 (RTM-CU1) (KB4038634) - 14.0.3006.16 (X64)   Oct 19 2017 02:42:29   Copyright (C) 2017 Microsoft Corporation  Standard Edition (64-bit) on Windows Server 2016 Standard 10.0 <X64> (Build 14393: ) (Hypervisor) 


    JB

    Friday, February 9, 2018 4:35 PM