LOGMGR Wait Type Explained

LOGMGR waits manifest during the log manager shutdown sequence when SQL Server transitions a database to an offline state. The log manager must ensure all pending log writes reach persistent storage before marking the database as cleanly shut down. This process involves the Log Writer (LogWriter) thread coordinating with the I/O completion ports to verify all outstanding asynchronous log writes have completed.

During normal operation, the log buffer flushes to disk asynchronously. However, during shutdown, SQL Server enforces synchronous completion of all pending log I/Os to maintain transactional consistency. The LOGMGR wait specifically measures the time spent waiting for these final I/O operations to complete before the log manager can safely shut down.

SQL Server 2016 introduced improvements to the log writer architecture that reduced LOGMGR wait times in most scenarios. SQL Server 2019 further optimized the shutdown sequence by implementing more efficient I/O completion detection mechanisms. SQL Server 2022 and 2025 maintain these optimizations while adding better telemetry for diagnosing prolonged LOGMGR waits.

The wait becomes problematic when the underlying storage subsystem exhibits high latency or when extremely large volumes of uncommitted transactions exist at shutdown time. Network-attached storage, overloaded SANs, or failing disk drives commonly cause extended LOGMGR waits during database shutdown operations.

-- Check current LOGMGR waits and their duration
SELECT 
    wait_type,
    waiting_tasks_count,
    wait_time_ms,
    max_wait_time_ms,
    signal_wait_time_ms
FROM sys.dm_os_wait_stats 
WHERE wait_type = 'LOGMGR';

-- Identify databases with active log I/O during shutdown scenarios
SELECT 
    DB_NAME(database_id) AS database_name,
    total_log_size_in_bytes / 1048576.0 AS total_log_size_mb,
    used_log_space_in_bytes / 1048576.0 AS used_log_space_mb,
    used_log_space_in_percent
FROM sys.dm_db_log_space_usage;

-- Monitor current pending I/O requests for log files
SELECT 
    ipir.io_pending_ms_ticks,
    ipir.io_pending,
    ipir.io_handle,
    ipir.scheduler_address,
    DB_NAME(mf.database_id) AS database_name,
    mf.name AS file_name,
    mf.type_desc
FROM sys.dm_io_pending_io_requests ipir
JOIN sys.dm_io_virtual_file_stats(NULL, NULL) vfs 
    ON ipir.io_handle = vfs.file_handle
JOIN sys.master_files mf 
    ON vfs.database_id = mf.database_id 
    AND vfs.file_id = mf.file_id
WHERE mf.type_desc = 'LOG'
ORDER BY ipir.io_pending_ms_ticks DESC;

-- Check transaction log file I/O statistics for performance issues
SELECT 
    DB_NAME(vfs.database_id) AS database_name,
    mf.name AS file_name,
    mf.physical_name,
    vfs.io_stall_write_ms / (vfs.num_of_writes + 1) AS avg_write_latency_ms,
    vfs.num_of_writes,
    vfs.num_of_bytes_written / 1048576.0 AS mb_written
FROM sys.dm_io_virtual_file_stats(NULL, NULL) vfs
JOIN sys.master_files mf ON vfs.database_id = mf.database_id 
    AND vfs.file_id = mf.file_id
WHERE mf.type_desc = 'LOG' 
    AND vfs.num_of_writes > 0
ORDER BY avg_write_latency_ms DESC;

-- Examine open transactions that might delay log shutdown
SELECT 
    s.session_id,
    db_name(dt.database_id) AS database_name,
    dt.database_transaction_begin_time,
    dt.database_transaction_log_bytes_used / 1024.0 AS log_kb_used,
    s.program_name,
    s.host_name
FROM sys.dm_tran_database_transactions dt
JOIN sys.dm_tran_session_transactions st ON dt.transaction_id = st.transaction_id
JOIN sys.dm_exec_sessions s ON st.session_id = s.session_id
WHERE dt.database_transaction_state IN (1,2,3)
ORDER BY dt.database_transaction_begin_time;

Force checkpoint before shutdown to minimize pending log I/Os

-- Forces all dirty pages to disk and minimizes log activity during shutdown
-- Run this before planned database OFFLINE operations
USE [YourDatabase];
CHECKPOINT;

-- For all databases
EXEC sp_MSforeachdb 'USE [?]; CHECKPOINT;';

Test in development first. Checkpoint operations can cause brief I/O spikes and may impact concurrent workloads during execution.

Configure instant file initialization to reduce shutdown delays

-- Enable instant file initialization at SQL Server service level
-- This requires Windows "Perform Volume Maintenance Tasks" privilege
-- Reduces time needed for log file operations during shutdown

-- Verify current setting (SQL Server 2016+)
SELECT 
    instant_file_initialization_enabled,
    sql_memory_model_desc
FROM sys.dm_server_services
WHERE servicename LIKE 'SQL Server (%';

Requires SQL Server service restart and Windows administrator privileges. Test thoroughly as this affects all file growth operations server-wide.

Optimize log file configuration to prevent shutdown delays

-- Pre-size transaction log files to avoid autogrowth during shutdown
-- Replace with appropriate database name and size
USE [master];
GO

ALTER DATABASE [YourDatabase] 
MODIFY FILE (
    NAME = 'YourDatabase_Log',
    SIZE = 1024MB,      -- Set to appropriate size based on workload
    FILEGROWTH = 256MB  -- Set reasonable growth increment
);

Calculate appropriate log file sizes based on peak transaction volume. Undersized logs cause frequent autogrowth events that complicate shutdown procedures.

Implement graceful shutdown procedures for applications

-- Create stored procedure to prepare database for clean shutdown
CREATE PROCEDURE dbo.PrepareForShutdown
AS
BEGIN
    SET NOCOUNT ON;
    
    -- Kill long-running transactions (customize criteria as needed)
    DECLARE @sql NVARCHAR(MAX);
    SELECT @sql = STRING_AGG('KILL ' + CAST(session_id AS VARCHAR(10)), '; ')
    FROM sys.dm_exec_sessions s
    JOIN sys.dm_tran_session_transactions st ON s.session_id = st.session_id
    JOIN sys.dm_tran_database_transactions dt ON st.transaction_id = dt.transaction_id
    WHERE dt.database_transaction_begin_time < DATEADD(MINUTE, -30, GETDATE())
        AND s.session_id != @@SPID;
    
    IF @sql IS NOT NULL
        EXEC sp_executesql @sql;
    
    -- Force checkpoint
    CHECKPOINT;
END;

Customize transaction timeout thresholds for your environment. Always test kill session logic in development environments first.

Configure transaction log files with adequate pre-sizing to eliminate autogrowth events during shutdown sequences. Monitor log file I/O latency using sys.dm_io_virtual_file_stats and establish baselines for normal shutdown times versus problematic ones.

Implement application-level connection pooling with proper connection lifecycle management to minimize long-running transactions that complicate log manager shutdown. Configure connection timeouts aggressively for non-critical applications to prevent abandoned transactions from extending LOGMGR wait times.

Establish storage performance monitoring for transaction log drives using Performance Monitor counters Avg. Disk sec/Write and Disk Write Bytes/sec. LOGMGR waits correlate directly with storage subsystem performance, particularly for write operations to log files.

Deploy regular checkpoint operations during low-activity periods to minimize dirty buffer pool pages that require log record flushes during shutdown. SQL Server 2019's Accelerated Database Recovery significantly reduces LOGMGR wait times by maintaining consistent database states without requiring extensive log replay during recovery operations.

Configure appropriate log backup frequencies to prevent transaction log files from growing excessively large, which extends the time required to flush all pending log records during shutdown operations. Oversized transaction logs directly correlate with extended LOGMGR wait times during database closure events.