SQL Server Error 802

Error number

802

Severity

17 (resource or software problem)

State

Not documented by Microsoft (not reproduced here)

Product

Microsoft SQL Server Database Engine

Versions

All supported SQL Server versions (2016 to 2025), Azure SQL Database, Azure SQL Managed Instance

Category

Memory

Last reviewed

24 October 2026

What it means

Error 802 means SQL Server needed a free buffer in the buffer pool, the pool was full and could not grow any further, so the request that needed it failed.

The buffer pool is where SQL Server keeps data and index pages, and it also supplies memory to many other components. Microsoft's explanation of 802 is short: the buffer pool is full and cannot grow larger. That happens when SQL Server has reached its memory target (max server memory, or what Windows allows it) and cannot free buffers fast enough, for example because large memory grants, caches or locks hold the memory, or because Windows itself is short of memory.

Severity 17 means a resource ran out; the statement fails but the server keeps running. The message has no pool name or size in it, so it does not tell you who used the memory. Compare it with error 701, which names the resource pool: the two often appear together during the same memory shortage, and the same checks of memory clerks and grants apply. For 802, start by comparing SQL Server's target and total memory and seeing what is filling the buffer pool.

Common causes

• Max server memory set low for the workload, or other applications on the server leaving SQL Server too little memory to reach its target.
• Large query memory grants for sorts and hashes taking memory that would otherwise hold data pages.
• Large caches outside the data cache, such as a plan cache full of single-use plans or a lock manager holding millions of locks.
• Big scans of large tables that keep replacing the buffer pool contents, so pages cannot be freed fast enough during peaks.

Diagnose it

Read-only These queries only read metadata and dynamic management views. They do not change data, permissions or configuration, so they are safe to run first.

SELECT RTRIM(object_name) AS object_name, RTRIM(counter_name) AS counter_name, cntr_value
FROM sys.dm_os_performance_counters
WHERE counter_name IN (N'Target Server Memory (KB)', N'Total Server Memory (KB)',
                       N'Database Cache Memory (KB)', N'Granted Workspace Memory (KB)',
                       N'Memory Grants Pending', N'Page life expectancy')
  AND (object_name LIKE N'%Memory Manager%' OR object_name LIKE N'%Buffer Manager%');

SELECT CASE database_id WHEN 32767 THEN N'(resource database)' ELSE DB_NAME(database_id) END AS database_name,
       COUNT_BIG(*) * 8 / 1024 AS cached_mb,
       SUM(CAST(is_modified AS int)) * 8 / 1024 AS dirty_mb
FROM sys.dm_os_buffer_descriptors
GROUP BY database_id
ORDER BY cached_mb DESC;

SELECT TOP (10) type, SUM(pages_kb) / 1024 AS pages_mb
FROM sys.dm_os_memory_clerks
WHERE type <> N'MEMORYCLERK_SQLBUFFERPOOL'
GROUP BY type
ORDER BY pages_mb DESC;

SELECT total_physical_memory_kb / 1024 AS total_mb,
       available_physical_memory_kb / 1024 AS available_mb,
       system_memory_state_desc
FROM sys.dm_os_sys_memory;

SELECT name, value_in_use
FROM sys.configurations
WHERE name IN (N'max server memory (MB)', N'min server memory (MB)', N'min memory per query (KB)');

How to fix it

Each fix is labelled with what it changes. Try read-only checks and the least invasive fix first, and test changes outside production.

-- Only with a value chosen from measurements (example: 28 GB on a 32 GB server)
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'max server memory (MB)', 28672;
RECONFIGURE;

Examples

A large report fails during a memory shortage

Shown as documented by Microsoft; not reproduced here because it needs the buffer pool to be full on the whole instance. The query that fails is usually a victim. The level and state a client sees were not observed in the lab, so only the message text is shown; the catalogue severity is 17.

SELECT o.CustomerId, COUNT(*) AS Orders
FROM dbo.Orders AS o
JOIN dbo.OrderLines AS l ON l.OrderId = o.OrderId
GROUP BY o.CustomerId;

There is insufficient memory available in the buffer pool.

How to prevent it

Watch Target and Total Server Memory, Page life expectancy and Memory Grants Pending over time, so the point where SQL Server runs at its limit is visible before queries fail. Keep a baseline of the memory clerks to spot one that grows.

Leave memory for Windows and anything else on the server, set max server memory deliberately from those measurements, and tune the queries that scan the most pages or ask for the largest grants.

Common questions

Learn more on CodeWithSQL

• Performance tuning
• SQL indexes

Official documentation

• MSSQLSERVER_802 (Microsoft Learn)
• Server memory configuration options
• sys.dm_os_buffer_descriptors

Last technically reviewed 24 October 2026; examples and diagnostics run on SQL Server 2025 (17.0.1145.4). Message text checked against the engine's sys.messages catalogue.