HADR_LOGPROGRESS_SYNC SQL Server Wait Type

Wait Type HADR_LOGPROGRESS_SYNC

The wait type HADR_LOGPROGRESS_SYNC is ranked #214 by Stedman Solutions and Database Health Monitor.

Wait statistics, in the context of SQL Server, refer to the amount of time that a query spends waiting to access data in the database. When a client application requests data from the database, the request is placed in a queue and the client application must wait for its turn to access the data. The time that the query spends waiting is called a "wait" and is tracked by SQL Server. This information can be used to identify potential performance bottlenecks and optimize the performance of the database. Wait statistics are commonly used by database administrators to diagnose and troubleshoot performance issues in SQL Server.


Understanding SQL Server HADR_LOGPROGRESS_SYNC Wait Types

In SQL Server Always On Availability Groups, synchronization between replicas is critical to maintaining data consistency and high availability. One wait type related to this process is HADR_LOGPROGRESS_SYNC. Understanding this wait type can help you troubleshoot and optimize your Always On environment.

What is HADR_LOGPROGRESS_SYNC?

The HADR_LOGPROGRESS_SYNC wait type occurs when SQL Server is waiting for log synchronization progress between the primary replica and its secondary replicas in an Always On Availability Group. This wait type reflects the time spent ensuring that the transaction log records are applied to the secondary replicas.

In simpler terms, this wait type indicates that SQL Server is working to ensure all replicas are up to date with the transaction log changes made on the primary. While some wait time is normal, prolonged or frequent waits can suggest issues with your configuration or system performance.

Why Does HADR_LOGPROGRESS_SYNC Happen?

Several factors can lead to HADR_LOGPROGRESS_SYNC waits, including:

  • High transaction log activity on the primary replica, causing delays in synchronizing with secondary replicas.
  • Network latency or insufficient bandwidth between the primary and secondary replicas.
  • Resource constraints, such as limited CPU, memory, or disk I/O, on the secondary replicas.
  • Slow or delayed application of transaction logs on the secondary replicas.
  • Suboptimal Always On Availability Group settings or misconfigured synchronization modes.

Understanding these factors can help you address the root causes and improve synchronization efficiency.

How to Monitor HADR_LOGPROGRESS_SYNC Waits

Monitoring HADR_LOGPROGRESS_SYNC waits is key to diagnosing and resolving synchronization delays. The Database Health Monitor is a powerful tool for this purpose. Its Historic Waits Monitoring feature helps you track this and other wait types over time, providing insights into patterns and correlations with system activity.

Using Database Health Monitor, you can determine if these waits are tied to high workloads, network issues, or resource bottlenecks, enabling you to take corrective action to improve performance.

What Can You Do About HADR_LOGPROGRESS_SYNC Waits?

If you encounter frequent or prolonged HADR_LOGPROGRESS_SYNC waits, consider these actions:

  • Optimize queries and indexes on the primary replica to reduce transaction log generation and workload.
  • Improve network bandwidth and reliability between the primary and secondary replicas to minimize synchronization delays.
  • Allocate sufficient CPU, memory, and disk I/O resources to the secondary replicas to handle log application efficiently.
  • Review Always On Availability Group synchronization settings to ensure they align with best practices for your workload.
  • Monitor and optimize the performance of secondary replicas to reduce log application delays.

These steps can help reduce synchronization bottlenecks and improve the overall performance of your Always On Availability Group.

How Stedman Solutions Can Help

At Stedman Solutions, we specialize in SQL Server performance optimization and troubleshooting, including addressing wait types like HADR_LOGPROGRESS_SYNC. Our managed services provide proactive monitoring and expert support to ensure your Always On Availability Groups run smoothly. Additionally, our Database Health Monitor offers powerful tools for tracking and analyzing SQL Server waits, helping you maintain a reliable and high-performing database system.

If you’re facing challenges with Always On synchronization or need assistance optimizing your SQL Server environment, contact us today. Let us help you achieve a resilient and efficient SQL Server setup!


Watch on YouTube


Find out more about our SQL Server Managed Services

Applies to

    Related Waits

    HADR_AG_MUTEX
    HADR_AR_CRITICAL_SECTION_ENTRY
    HADR_AR_MANAGER_MUTEX
    HADR_AR_UNLOAD_COMPLETED
    HADR_ARCONTROLLER_NOTIFICATIONS_SUBSCRIBER_LIST
    HADR_BACKUP_BULK_LOCK
    HADR_BACKUP_QUEUE
    HADR_CLUSAPI_CALL
    HADR_COMPRESSED_CACHE_SYNC
    HADR_CONNECTIVITY_INFO
    HADR_DATABASE_FLOW_CONTROL
    HADR_DATABASE_VERSIONING_STATE
    HADR_DATABASE_WAIT_FOR_RESTART
    HADR_DATABASE_WAIT_FOR_TRANSITION_TO_VERSIONING
    HADR_DB_COMMAND
    HADR_DB_OP_COMPLETION_SYNC
    HADR_DB_OP_START_SYNC
    HADR_DBR_SUBSCRIBER
    HADR_DBR_SUBSCRIBER_FILTER_LIST
    HADR_DBSEEDING
    HADR_DBSEEDING_LIST
    HADR_DBSTATECHANGE_SYNC
    HADR_FABRIC_CALLBACK
    HADR_FILESTREAM_BLOCK_FLUSH
    HADR_FILESTREAM_FILE_CLOSE
    HADR_FILESTREAM_FILE_REQUEST
    HADR_FILESTREAM_IOMGR
    HADR_FILESTREAM_MANAGER
    HADR_GROUP_COMMIT
    HADR_LOGCAPTURE_SYNC
    HADR_LOGCAPTURE_WAIT
    HADR_NOTIFICATION_DEQUEUE
    HADR_NOTIFICATION_WORKER_EXCLUSIVE_ACCESS
    HADR_NOTIFICATION_WORKER_STARTUP_SYNC
    HADR_NOTIFICATION_WORKER_TERMINATION_SYNC
    HADR_PARTNER_SYNC
    HADR_READ_ALL_NETWORKS
    HADR_RECOVERY_WAIT_FOR_CONNECTION
    HADR_RECOVERY_WAIT_FOR_UNDO
    HADR_REPLICAINFO_SYNC
    HADR_SYNC_COMMIT
    HADR_SYNCHRONIZING_THROTTLE
    HADR_TDS_LISTENER_SYNC
    HADR_TDS_LISTENER_SYNC_PROCESSING
    HADR_TIMER_TASK
    HADR_TRANSPORT_DBRLIST
    HADR_TRANSPORT_FLOW_CONTROL
    HADR_TRANSPORT_SESSION
    HADR_WORK_POOL
    HADR_WORK_QUEUE
    HADR_XRF_STACK_ACCESS

    See Also


    All Wait Types
    HADR_LOGPROGRESS_SYNC SQL Server Wait Type