COMMIT_ACT Wait Type Explained

COMMIT_ACT waits manifest when SQL Server's Distributed Transaction Coordinator (DTC) or Always On Availability Groups coordinate transaction commits across multiple resources. The wait appears when the local transaction manager has prepared a transaction for commit but must wait for confirmation from remote participants before finalizing the commit phase.

In distributed transactions, SQL Server implements a two-phase commit protocol where the transaction coordinator first sends prepare messages to all participants, then waits for their responses before issuing commit commands. During this coordination phase, sessions experience COMMIT_ACT waits while the transaction manager processes acknowledgments.

Database mirroring scenarios generate these waits when the principal server waits for the mirror server to acknowledge transaction log records before committing. The principal cannot complete the commit until receiving confirmation that the mirror has hardened the log records to disk.

Always On Availability Groups introduced changes in SQL Server 2012 where COMMIT_ACT waits occur during synchronous replica coordination. When synchronous commit mode is configured, the primary replica waits for secondary replicas to acknowledge log hardening before completing the transaction commit.

SQL Server 2016 enhanced the wait type granularity, providing better visibility into specific coordination phases. SQL Server 2019 and later versions improved the internal timing mechanisms, reducing false positive wait accumulations during normal coordination operations.

The scheduler becomes involved when multiple sessions compete for transaction coordinator resources. Buffer pool interaction occurs when transaction log records require flushing during the commit coordination phase. Lock manager integration ensures proper isolation during the commit process across all participants.

-- Check current COMMIT_ACT waits and their accumulation
SELECT 
    wait_type,
    waiting_tasks_count,
    wait_time_ms,
    max_wait_time_ms,
    signal_wait_time_ms,
    wait_time_ms / NULLIF(waiting_tasks_count, 0) AS avg_wait_ms
FROM sys.dm_os_wait_stats 
WHERE wait_type = 'COMMIT_ACT'
    AND waiting_tasks_count > 0;

-- Identify sessions currently experiencing COMMIT_ACT waits
SELECT 
    s.session_id,
    s.login_name,
    s.host_name,
    s.program_name,
    r.wait_type,
    r.wait_time,
    r.wait_resource,
    t.text AS current_sql
FROM sys.dm_exec_sessions s
INNER JOIN sys.dm_exec_requests r ON s.session_id = r.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.wait_type = 'COMMIT_ACT';

-- Check for distributed transaction activity
SELECT 
    t.transaction_id,
    t.transaction_begin_time,
    t.transaction_state,
    t.transaction_status,
    dt.database_transaction_state,
    dt.database_transaction_log_record_count
FROM sys.dm_tran_active_transactions t
LEFT JOIN sys.dm_tran_database_transactions dt ON t.transaction_id = dt.transaction_id
WHERE t.transaction_type = 4 -- Distributed transaction
    OR EXISTS (SELECT 1 FROM sys.dm_tran_session_transactions st WHERE st.transaction_id = t.transaction_id);

-- Monitor Always On AG synchronization if applicable
SELECT 
    ag.name AS ag_name,
    ar.replica_server_name,
    ar.availability_mode_desc,
    ars.synchronization_state_desc,
    ars.synchronization_health_desc,
    drs.log_send_queue_size,
    drs.redo_queue_size
FROM sys.availability_groups ag
INNER JOIN sys.availability_replicas ar ON ag.group_id = ar.group_id
INNER JOIN sys.dm_hadr_availability_replica_states ars ON ar.replica_id = ars.replica_id
LEFT JOIN sys.dm_hadr_database_replica_states drs ON ar.replica_id = drs.replica_id
WHERE ar.availability_mode_desc = 'SYNCHRONOUS_COMMIT';

-- Check transaction log activity during commit coordination
SELECT 
    database_id,
    DB_NAME(database_id) AS database_name,
    total_log_size_mb,
    active_log_size_mb,
    log_since_last_log_backup_mb,
    log_recovery_size_mb
FROM sys.dm_db_log_stats(DB_ID());

Optimize DTC Configuration for Distributed Transactions

-- Enable distributed transaction coordination optimization
-- WARNING: Test thoroughly in development environment
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'remote query timeout', 0; -- Disable timeout for distributed queries
RECONFIGURE;

-- Manual step required: Configure MSDTC security settings via Component Services (dcomcnfg.exe)
-- This is a GUI tool and must be run interactively on the server

Test in development first. This configuration change affects all distributed transactions and requires MSDTC service restart.

Reduce Always On Synchronous Commit Latency

-- Switch high-latency replicas to asynchronous commit temporarily
-- Replace 'YourAGName' and 'HighLatencyReplica' with actual names
ALTER AVAILABILITY GROUP [YourAGName] 
MODIFY REPLICA ON 'HighLatencyReplica' 
WITH (AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT);

-- Monitor the change impact
SELECT 
    replica_server_name,
    synchronous_commit,
    synchronization_state_desc
FROM sys.availability_replicas ar
INNER JOIN sys.dm_hadr_availability_replica_states ars ON ar.replica_id = ars.replica_id;

This reduces commit coordination overhead but impacts data protection. Evaluate RTO/RPO requirements before implementation.

Kill Blocking Distributed Transactions

-- Identify and terminate problematic distributed transactions
-- WARNING: This will rollback transactions, causing data loss of uncommitted changes
DECLARE @transaction_id BIGINT;
DECLARE @session_id INT;

-- Find oldest blocking distributed transaction
SELECT TOP 1 
    @transaction_id = t.transaction_id,
    @session_id = st.session_id
FROM sys.dm_tran_active_transactions t
INNER JOIN sys.dm_tran_session_transactions st ON t.transaction_id = st.transaction_id
WHERE t.transaction_type = 4
    AND DATEDIFF(MINUTE, t.transaction_begin_time, GETDATE()) > 30 -- Transactions older than 30 minutes
ORDER BY t.transaction_begin_time;

-- Kill the session if found
IF @session_id IS NOT NULL
BEGIN
    DECLARE @sql NVARCHAR(50) = 'KILL ' + CAST(@session_id AS NVARCHAR(10));
    EXEC sp_executesql @sql;
    PRINT 'Killed session: ' + CAST(@session_id AS VARCHAR(10));
END;

Only use in emergency situations. Coordinate with application teams before killing active sessions.

Configure Transaction Log Optimization

-- Optimize transaction log settings to reduce commit coordination overhead
-- Adjust log file growth to prevent auto-growth delays during commits
ALTER DATABASE [YourDatabase] 
MODIFY FILE (
    NAME = 'YourDatabase_Log',
    FILEGROWTH = 1024MB -- Set appropriate growth size
);

-- Enable instant file initialization if not already enabled
-- This requires 'Perform Volume Maintenance Tasks' permission for SQL Server service account
-- Check current setting
SELECT 
    instant_file_initialization_enabled,
    sql_memory_model_desc
FROM sys.dm_server_services
WHERE servicename LIKE 'SQL Server%';

Instant file initialization reduces log growth delays but requires Windows-level permissions. Coordinate with system administrators.

Configure MSDTC with appropriate authentication and network settings to minimize coordination delays. Enable network DTC access only when required and use Windows Authentication instead of anonymous access to reduce security validation overhead.

For Always On Availability Groups, place synchronous replicas on low-latency networks with dedicated bandwidth. Monitor network round-trip time between replicas and consider asynchronous commit for geographically distant replicas. Implement proper log file sizing with fixed growth increments to prevent auto-growth delays during commit coordination.

Establish monitoring thresholds for COMMIT_ACT wait accumulation rates. Alert when average wait times exceed 100ms or total wait time increases rapidly over baseline measurements. Create automated scripts to identify long-running distributed transactions and coordinate with application teams for proper transaction scope management.

Review application transaction boundaries to minimize distributed transaction scope. Implement circuit breaker patterns for remote service calls within transactions. Use queuing mechanisms for non-critical cross-database operations instead of including them in synchronous distributed transactions.

Configure appropriate query timeouts and connection pooling settings in application connection strings. Monitor transaction log disk performance and ensure adequate IOPS capacity for commit processing. Implement regular log backup schedules to prevent log file growth that can impact commit performance.