KB2769594 - SQL Server 2012 experiences out-of-memory errors

Symptoms

Microsoft SQL Server 2012 may experience some out-of-memory errors because of an internal memory leak. In this situation, the leaked memory is consumed by the MEMORYCLERK_SQLLOGPOOL memory clerk that is used internally for transaction log activities. For example, the MEMORYCLERK_SQLLOGPOOL memory clerk is used internally for the following transaction log activities:

• AlwaysOn change-capturing activities on the primary replicas

• AlwaysOn redo manager activities on the secondary availability replicas

This issue may be aggravated by a high volume of transactions that are being synchronized on an instance of SQL Server that hosts many availability databases. When this issue occurs, an error message that resembles the following is logged in the SQL error log:

When the instance of SQL Server hosts a database that is involved in a SQL Server AlwaysOn availability group, the data synchronization between replicas may fail. To work around the problem, you can stop and then restart SQL Server.

When the out-of-memory conditions occurs, the availability group may fail over to the secondary availability replica, and the former primary replica may continue achieving errors until the service is stopped and restarted. The availability group may go into the Resolving or Unknown state, and all the databases in that availability group may be inaccessible, until the SQL service is restarted to free the log pool memory.

You can run the DBCC MEMORYSTATUS statement when the failure begins to notice in the output that many gigabytes of memory are consumed by the MEMORYCLERK_SQLLOGPOOL memory clerk. The following is an example output:

MEMORYCLERK_SQLLOGPOOL (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 823544You can also see the number of memory pages of the problem by running the following DMV queries:

SELECT * FROM sys.dm_os_memory_clerks WHERE type='MEMORYCLERK_SQLLOGPOOL'
SELECT type, name, pages_in_bytes/ 1024 AS PMO_KB FROM sys.dm_os_memory_objects WHERE type like 'MEMOBJ_LOGPOOL%'

Another possible issue is that SQL Server queries could wait for RESOURCE_SEMAPHORE waittype when the buffer pool memory cannot be immediately obtained because of the high number of Log Pool consumers.

This hotfix resolves the following issues:

• A memory leak is corrected in the internal read-ahead mechanism that is used to locate the transaction log pools that are used by AlwaysOn and other transaction log intensive activities in SQL Server 2012.

• When there is low memory available, SQL Server releases the internal log pool memory aggressively to recover from out-of-memory failures more gracefully.

Resolution