none
HADR_SYNC_COMMIT issue RRS feed

  • Question

  • Hi,

    We are facing HADR_SYNC_COMMIT wait type in one of production server. But luckly no timedouts from app side.

    This is intermittent issue.

    We Opened SEV A with Microsoft and working from past two months. But no solution found yet.

    During the call we generated N number of PSSDIAG and XEVENTS , but no use. Microsoft telling logs looks good.

    Environment:

    SQL Server 2016 ENT With SP2.

    AlwaysOn: Primary+1Secondary (In Sync Mode).

    Can anybody suggest me how to proceed further

    Thursday, November 21, 2019 4:28 PM

All replies

  • Hi Vijay:

    HADR_SYNC_COMMIT indicates the transaction ready to commit in Primary replica but its waiting ACK from Secondary replica.

    Is both Primary and Secondary replica sits on Same DC or Different DC.

    If Different DC, you need check on n/w speed and all.

    If in Same DC, pl check DISK I/O, CPU and all other resources.

    Pl refer below:

    https://blogs.msdn.microsoft.com/sql_server_team/troubleshooting-high-hadr_sync_commit-wait-type-with-always-on-availability-groups/

    https://blogs.msdn.microsoft.com/psssql/2018/04/05/troubleshooting-data-movement-latency-between-synchronous-commit-always-on-availability-groups/

    https://aboutsqlserver.com/2019/06/09/hadr-sync-commit/


    Thanks, Satish Kumar. Please mark as this post as answered if my anser helps you to resolves your issue :)

    Thursday, November 21, 2019 4:54 PM
  • Hi Satish,

    Both Servers are in same DC.

    Microsoft analyzed all PSSdiag and XEvents and confirmed there is no DISK IO and CPU.

    They are suggesting that the temporarly solution is to Put AG in ASYNC mode. But i knew this is risky.


    Thursday, November 21, 2019 7:51 PM
  • Hi Vijay:

    Is your secondary replica using for any reporting purpose?

    Let me know?

    Thanks,


    Thanks, Satish Kumar. Please mark as this post as answered if my anser helps you to resolves your issue :)

    Friday, November 22, 2019 3:12 PM
  • When EXACTLY do you see this wait state?  Are you having a performance problem?

    It is perfectly normal and expected to see this wait state when you are using AlwaysOn for short periods.  This does NOT indicate a problem.

    Friday, November 22, 2019 4:15 PM
  • There is no reporting on secondary.

    After reviewing logs (PSSdiag and XEvent) Microsoft mentioned that everything is normal. Then Microsoft recommended that go with Async with still performance issue. We changed to Async. But this it not good practive as we are loosing HA.

    After changing to Async we got lot of performance improvement from application and not getting any complaints from clients. 

    Friday, November 22, 2019 5:23 PM