SQL Server Error 845 - Causes & Fixes

Error 845 triggers when SQL Server's buffer pool manager cannot acquire a latch within the timeout period. Latches protect buffer pool pages during I/O operations, ensuring data consistency while pages are read from or written to disk. The scheduler thread attempting to access a page enters a wait state, and when the latch acquisition exceeds the internal timeout threshold (approximately 300 seconds for buffer latch waits), SQL Server raises this error.

The buffer pool manager uses different latch types (SH, EX, UP, KP) to coordinate access between worker threads. When I/O subsystem performance degrades severely, pages remain latched longer as read/write operations cannot complete. This creates a cascading effect where multiple processes queue for the same latched pages.

Hardware failures manifest differently across SQL Server versions. SQL Server 2016 introduced automatic soft-NUMA configuration which can amplify latch contention on NUMA systems. SQL Server 2019 added intelligent query processing features that may generate different I/O patterns, while SQL Server 2022's buffer pool parallel scan improvements can reduce some latch contention scenarios but may expose underlying storage issues more readily.

The error commonly occurs during checkpoint operations, lazy writer activity, or large table scans when the storage subsystem cannot keep pace with SQL Server's I/O demands. Memory pressure exacerbates the issue by forcing more frequent page reads and writes.

-- Check current latch waits and blocking
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 LIKE 'PAGELATCH%' 
   OR wait_type LIKE 'PAGEIOLATCH%'
ORDER BY wait_time_ms DESC;

-- Identify index-level latch contention hotspots
SELECT TOP 10
    OBJECT_SCHEMA_NAME(ios.object_id) AS schema_name,
    OBJECT_NAME(ios.object_id) AS table_name,
    i.name AS index_name,
    ios.page_latch_wait_count,
    ios.page_latch_wait_in_ms,
    ios.page_io_latch_wait_count,
    ios.page_io_latch_wait_in_ms
FROM sys.dm_db_index_operational_stats(DB_ID(), NULL, NULL, NULL) ios
INNER JOIN sys.indexes i 
    ON ios.object_id = i.object_id AND ios.index_id = i.index_id
WHERE ios.page_latch_wait_count > 0 OR ios.page_io_latch_wait_count > 0
ORDER BY ios.page_io_latch_wait_in_ms + ios.page_latch_wait_in_ms DESC;

-- Monitor I/O stall statistics per database file
SELECT 
    DB_NAME(vfs.database_id) AS database_name,
    vfs.file_id,
    vfs.num_of_reads,
    vfs.io_stall_read_ms,
    vfs.num_of_writes,
    vfs.io_stall_write_ms,
    vfs.io_stall_read_ms / NULLIF(vfs.num_of_reads, 0) AS avg_read_stall_ms,
    vfs.io_stall_write_ms / NULLIF(vfs.num_of_writes, 0) AS avg_write_stall_ms
FROM sys.dm_io_virtual_file_stats(NULL, NULL) vfs
WHERE vfs.io_stall_read_ms > 100 OR vfs.io_stall_write_ms > 100
ORDER BY vfs.io_stall_read_ms + vfs.io_stall_write_ms DESC;

-- Check schedulers for pending I/O backlog
SELECT 
    scheduler_id,
    cpu_id,
    status,
    current_tasks_count,
    runnable_tasks_count,
    work_queue_count,
    pending_disk_io_count
FROM sys.dm_os_schedulers
WHERE scheduler_id < 255
ORDER BY pending_disk_io_count DESC;

-- Check buffer pool pressure and memory clerks
SELECT 
    type,
    pages_kb,
    virtual_memory_reserved_kb,
    virtual_memory_committed_kb,
    awe_allocated_kb
FROM sys.dm_os_memory_clerks
WHERE type IN ('MEMORYCLERK_SQLBUFFERPOOL', 'MEMORYCLERK_SQLSTORENG')
ORDER BY pages_kb DESC;

-- Examine checkpoint and lazy writer activity
SELECT 
    cntr_value,
    cntr_type
FROM sys.dm_os_performance_counters
WHERE counter_name IN ('Checkpoint pages/sec', 'Lazy writes/sec', 'Page life expectancy')
  AND object_name LIKE '%Buffer Manager%';

Immediate Relief: Force Checkpoint and Clear Buffer Pool (Emergency Only)

-- WARNING: This will cause performance impact as pages are reloaded
-- Only use during severe Error 845 storms
CHECKPOINT;
WAITFOR DELAY '00:00:10';
DBCC DROPCLEANBUFFERS;
-- Monitor I/O patterns after execution

Increase Memory for Buffer Pool

-- Calculate recommended max server memory (leave 4GB for OS + 1GB per 8GB above 16GB)
DECLARE @TotalMemoryMB INT;
DECLARE @RecommendedMaxMemMB INT;

SELECT @TotalMemoryMB = total_physical_memory_kb / 1024
FROM sys.dm_os_sys_memory;

SET @RecommendedMaxMemMB = CASE 
    WHEN @TotalMemoryMB <= 4096 THEN @TotalMemoryMB - 1024
    WHEN @TotalMemoryMB <= 16384 THEN @TotalMemoryMB - 2048
    ELSE @TotalMemoryMB - 4096 - ((@TotalMemoryMB - 16384) / 8192) * 1024
END;

EXEC sp_configure 'max server memory (MB)', @RecommendedMaxMemMB;
RECONFIGURE;
-- Restart SQL Server service to fully apply memory changes

Enable Trace Flags for Advanced I/O Handling

-- Enable trace flag 845 for extended latch timeout logging
-- Enable trace flag 3226 to suppress backup log entries
-- Test impact before implementing in production
DBCC TRACEON(845, -1);
DBCC TRACEON(3226, -1);

-- Verify active trace flags
DBCC TRACESTATUS(-1);
-- Add to startup parameters for persistence

Optimize TempDB Configuration

-- Create optimal tempdb file configuration
-- Run this only during maintenance windows
USE master;
GO

DECLARE @LogicalCores INT = (SELECT cpu_count FROM sys.dm_os_sys_info);
DECLARE @FileCount INT = CASE 
    WHEN @LogicalCores <= 8 THEN @LogicalCores 
    ELSE 8 
END;

DECLARE @SQL NVARCHAR(MAX) = '';
DECLARE @i INT = 1;

WHILE @i <= @FileCount
BEGIN
    SET @SQL = @SQL + 'ALTER DATABASE tempdb ADD FILE (
        NAME = ''tempdev' + CAST(@i AS VARCHAR(2)) + ''',
        FILENAME = ''C:\TempDB\tempdev' + CAST(@i AS VARCHAR(2)) + '.mdf'',
        SIZE = 1GB,
        FILEGROWTH = 256MB);' + CHAR(13);
    SET @i = @i + 1;
END;

-- Review generated SQL before execution
PRINT @SQL;
-- EXEC sp_executesql @SQL; -- Uncomment after review

Configure proper memory allocation by setting max server memory to leave adequate RAM for the operating system and other applications. Monitor Page Life Expectancy consistently, aiming for values above 300 seconds for most workloads.

Implement I/O subsystem monitoring using Performance Monitor counters for Avg. Disk Queue Length (target <2 per spindle) and Avg. Disk sec/Read and sec/Write (target <20ms). Deploy storage solutions with sufficient IOPS capacity for peak workloads, typically requiring 10-15% headroom above measured peaks.

Schedule maintenance operations during low-activity periods to prevent checkpoint and statistics update conflicts. Configure database auto-growth settings with reasonable fixed sizes rather than percentage growth to prevent allocation delays during high-activity periods.

Enable SQL Server 2019+ accelerated database recovery for faster crash recovery and reduced latch contention during long-running transactions. Use instant file initialization and configure appropriate backup compression to reduce I/O overhead.

Monitor tempdb usage patterns and pre-size files appropriately. Implement query optimization reviews focusing on scans of large tables during peak hours, as these operations consume significant buffer pool resources and increase latch pressure.

Deploy hardware health monitoring for storage controllers, memory modules, and disk arrays. Error 845 frequently correlates with pending hardware failures that manifest as intermittent I/O delays before complete failure occurs.