MSSQLSERVER_802

  • Article

Topic Status: Some information in this topic is preview and subject to change in future releases. Preview information describes new features or changes to existing features in Microsoft SQL Server 2016 Community Technology Preview 2 (CTP2).

Details

Product Name

SQL Server

Event ID

802

Event Source

MSSQLSERVER

Component

SQLEngine

Symbolic Name

NO_BUFS

Message Text

There is insufficient memory available in the buffer pool.

Explanation

This is caused when the buffer pool is full and the buffer pool can not grow any larger.

User Action

The following list outlines general steps that will help in troubleshooting memory errors:

  1. Verify whether other applications or services are consuming memory on this server. Reconfigure less critical applications or services to consume less memory.

  2. Start collecting performance monitor counters for SQL Server**: Buffer Manager**, SQL Server**: Memory Manager**.

  3. Check the following SQL Server memory configuration parameters:

    • max server memory

    • min server memory

    • min memory per query

    Notice any unusual settings and correct them as necessary. Account for increased memory requirements for SQL Server. Default settings are listed in "Setting Server Configuration Options" in SQL Server Books Online.

  4. Observe DBCC MEMORYSTATUS output and the way it changes when you see these error messages.

  5. Check the workload (number of concurrent sessions, currently executing queries).

The following actions may make more memory available to SQL Server:

  • If applications besides SQL Server are consuming resources, try stopping these applications or running them on a separate server.

  • If you have configured max server memory, increase the setting.

Run the following DBCC commands to free several SQL Server memory caches.

  • DBCC FREESYSTEMCACHE

  • DBCC FREESESSIONCACHE

  • DBCC FREEPROCCACHE

If the problem continues, you will need to investigate further and possibly reduce workload.