Home/Error Library/sqlserver-845-buffer-latch-timeout-out-of-memory

SQL Server

SQL Server 2017 - 2022Memory & Latch Contention

12 min read

Msg 845, Level 17: Time-out occurred while waiting for buffer latch type 2 for page (1:12345) / 701 Out of memory

SQL Server Buffer Latch Timeout: Disk Bottlenecks, OS Memory Starvation & TempDB Allocation Contention

Resolving buffer latch timeouts stemming from storage latency or unconstrained max server memory, implementing Lock Pages in Memory and multi-file tempdb design.

Reviewed by:Senior Database Architect & SRE (dataiotech Labs)

Tested Versions: SQL Server 2017 / 2019 / 2022

Operational Verification & Reliability Metadata

Last Technical Review: 2 Kasım 2026

Tested VersionsSQL Server 2019 CU27, 2022 CU14

Required Privilegessysadmin

Risk Assessment

CriticalBuffer latch timeout indicates storage subsystem latency collapse or VM memory ballooning

Expected OutputPAGEIOLATCH_SH and PAGELATCH_EX wait durations dropping to baseline levels

Rollback Procedure:Scale down max server memory and configure multi-file tempdb

Microsoft Docs: Resolve PAGELATCH Contention

First 60 Seconds Emergency Protocol:

The error string is a symptom, not the root cause. Do not restart the server blindly. Run the diagnostic queries below to pinpoint the actual failure mode.

▸Symptom

Queries stall under storage starvation, producing:

plaintext

Msg 845, Level 17, State 1, Line 1
Time-out occurred while waiting for buffer latch type 2 for page (1:12345).
Msg 701, Level 17, State 1, Line 1
There is insufficient system memory in resource pool 'internal' to run this query.

▸What You See in Production

Storage latency spikes past 100ms. PAGEIOLATCH_SH waits dominate activity monitors.

▸Root Cause Matrix

Probability	Root Cause	Description
%50	Storage Latency Collapse	Disk subsystem fails to deliver requested pages within latch thresholds.
%30	Unconstrained Memory Sizing	SQL Server leaves zero RAM for host OS, inducing paging and VM ballooning.
%20	TempDB Allocation Contention	GAM/SGAM page bottlenecks on single-file tempdb setups.

▸EMERGENCY RUNBOOK (0-5 min)

sql

-- 1. Inspect host memory
SELECT (available_physical_memory_kb / 1024 / 1024) AS Available_GB FROM sys.dm_os_sys_memory;

-- 2. Re-allocate memory headroom to OS
EXEC sp_configure 'show advanced options', 1; RECONFIGURE;
EXEC sp_configure 'max server memory', 51200; RECONFIGURE;

-- 3. Query latch wait distribution
SELECT wait_type, wait_time_ms FROM sys.dm_os_wait_stats WHERE wait_type LIKE '%LATCH%' ORDER BY wait_time_ms DESC;

▸PERMANENT BLUEPRINT

1Lock Pages in Memory (LPIM): Grant LPIM privilege to SQL Server service account.
2TempDB Multi-File Standard: Create 8 equally sized tempdb data files across dedicated storage.

▸Verification

sql

SELECT wait_type, wait_time_ms FROM sys.dm_os_wait_stats WHERE wait_type IN ('PAGEIOLATCH_SH', 'PAGELATCH_UP');

Other Database Error Triage Playbooks

Other battle-tested diagnostic guides for high-severity database errors.

All Errors (82)

SQLSERVER5 min