Post reply

Question about handling commit_table waits

  • August 23, 2018 at 1:12 pm

    #362206

    Dear SQL Server professionals, 

    Thank you for looking into my question.  

    I have a 3 node cluster (with no shared storage !!) with Always On availability groups.  The architecture is one Primary Replica, one synchronous secondary replica and one asynchronous secondary replica.  I also have "Change Tracking" enabled on one of the databases in primary replica. I have an agent job that runs every minute to capture change tracking information.

    The log backups and the change tracking job was running for more than 20 hours and did not finish. When I looked into the server, I noticed "commit_table" waits on the SQL Job that reads change tracking information and also on the log backup job. Since it was running for more than 20 hours, I tried to kill these processes. But the processes did not get killed for more than 30 minutes and was rolling back.  This started causing blocking on the application queries.  

    I tried failing over to the synchronous replica to solve this issue.

    Can someone help me to get some insight and if I had done something wrong, how to correct it in future?

    Thanks again.

  • August 23, 2018 at 2:13 pm

    #2003107

    This wait type is when a thread is waiting for access to the in-memory portion of the hidden commit table (sys.syscommittab) that helps underpin the Change Tracking functionality. This table contains one row for each transaction that has changed a table that is being change tracked, and is accessed from many places in the Storage Engine (e.g., when a checkpoint occurs, during periodic automatic cleanup, when a transaction commits).

    How big are the change Tracking tables?
    What is the cleanup schedule for change tracking?
    Which version of SQL are you using?

  • August 23, 2018 at 2:39 pm

    #2003112

    Thank you for the response. 

    There are 13 tables that are enabled for change tracking in the database. The largest table has 1.8 million records. 
    The cleanup schedule is set to 2 days. 
    I am running SQL Server 2017 Enterprise edition.

    Thanks

  • August 23, 2018 at 3:29 pm

    #2003116

    Siva Ramasamy - Thursday, August 23, 2018 2:39 PM

    Thank you for the response. 

    There are 13 tables that are enabled for change tracking in the database. The largest table has 1.8 million records. 
    The cleanup schedule is set to 2 days. 
    I am running SQL Server 2017 Enterprise edition.

    Thanks

    I think problem might be with the cleanup process.

    Use sys.sp_flush_commit_table_on_demand to start cleanup on demand.But you have to disable the scheduled cleanup process.

  • August 23, 2018 at 3:36 pm

    #2003119

    Thank you. Let me explore.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply