Select the product you need help with

SQL Server 2012 experiences out-of-memory errors

Article ID: 2769594 - View products that this article applies to.

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:

Failed allocate pages: FAIL_PAGE_ALLOCATION 8
Error: 701, Severity: 17, State: 107.
There is insufficient system memory in resource pool 'internal' to run this query.
Error: 701, Severity: 17, State: 123.
There is insufficient system memory in resource pool 'PoolName' to run this query.
Error: 701, Severity: 17, State: 107. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.
Error: 17312, Severity: 16, State: 1.
SQL Server is terminating a system or background task HADR Task due to errors in starting up the task (setup state 1).
AppDomain 3181 (<appdomain>) is marked for unload due to memory pressure.
AppDomain 3181 (<appdomain>) unloaded.
Error: 9602, Severity: 16, State: 1.
Failed to start a system task with error code 28709, state 19.
Error: 17300, Severity: 16, State: 1.
SQL Server was unable to run a new system task, either because there is insufficient memory or the number of configured sessions exceeds the maximum allowed in the server. Verify that the server has adequate memory. Use sp_configure with option 'user connections' to check the maximum number of user connections allowed. Use sys.dm_exec_sessions to check the current number of sessions, including user processes.
Error: 17312, Severity: 16, State: 1.
SQL Server is terminating a system or background task HADR Task due to errors in starting up the task (setup state 1).
Error: 18054, Severity: 16, State: 1.

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 823544

You 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.

Back to the top | Give Feedback

Resolution

Cumulative update information

Cumulative Update 2 for SQL Server 2012 SP1

The fix for this issue was first released in Cumulative Update 2. For more information about how to obtain this cumulative update package for SQL Server 2012 SP1, click the following article number to view the article in the Microsoft Knowledge Base:

2790947

(http://support.microsoft.com/kb/2790947/ )

Cumulative update package 2 for SQL Server 2012 Service Pack 1

Note Because the builds are cumulative, each new fix release contains all the hotfixes and all the security fixes that were included with the previous SQL Server 2012 SP1 fix release. We recommend that you consider applying the most recent fix release that contains this hotfix. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

2772858

(http://support.microsoft.com/kb/2772858/ )

The SQL Server 2012 builds that were released after SQL Server 2012 Service Pack 1 was released

Cumulative Update 5 for SQL Server 2012

The fix for this issue was first released in Cumulative Update 5. For more information about how to obtain this cumulative update package for SQL Server 2012, click the following article number to view the article in the Microsoft Knowledge Base:

2777772

(http://support.microsoft.com/kb/2777772/ )

Cumulative update package 5 for SQL Server 2012

Note Because the builds are cumulative, each new fix release contains all the hotfixes and all the security fixes that were included with the previous SQL Server 2012 fix release. We recommend that you consider applying the most recent fix release that contains this hotfix. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

2692828

(http://support.microsoft.com/kb/2692828/ )

The SQL Server 2012 builds that were released after SQL Server 2012 was released

Hotfix information

A supported hotfix is available from Microsoft. However, this hotfix is intended to correct only the problem that is described in this article. Apply this hotfix only to systems that are experiencing this specific problem.

If the hotfix is available for download, there is a "Hotfix download available" section at the top of this Knowledge Base article. If this section does not appear, submit a request to Microsoft Customer Service and Support to obtain the hotfix.

Note If additional issues occur or if any troubleshooting is required, you might have to create a separate service request. The usual support costs will apply to additional support questions and issues that do not qualify for this specific hotfix. For a complete list of Microsoft Customer Service and Support telephone numbers or to create a separate service request, go to the following Microsoft website:

http://support.microsoft.com/contactus/?ws=support

(http://support.microsoft.com/contactus/?ws=support)

Note The "Hotfix download available" form displays the languages for which the hotfix is available. If you do not see your language, it is because a hotfix is not available for that language.

Restart requirement

You do not have to restart the computer after you apply this hotfix.

Prerequisites

To apply this hotfix, you must have SQL Server 2012 installed.

File information

The global version of this hotfix has the file attributes (or later file attributes) that are listed in the following table. The dates and times for these files are listed in Coordinated Universal Time (UTC). When you view the file information, it is converted to local time. To find the difference between UTC and local time, use the Time Zone tab in the Date and Time item in Control Panel.

Collapse this tableExpand this table

File name	File version	File size	Date	Time	Platform
Sqlmin.dll	2011.110.2387.0	32,460,272	19-Oct-2012	05:25	x64
Sqlservr.exe	2011.110.2387.0	190,944	19-Oct-2012	05:01	x64
Sqlmin.dll	2011.110.2387.0	26,515,936	19-Oct-2012	05:22	x86
Sqlservr.exe	2011.110.2387.0	160,224	19-Oct-2012	05:17	x86