SQL Server fails to start and If you look at the SQL Server Error log you will find "Failed allocate pages: FAIL_PAGE_ALLOCATION" and SQL Server generating exception dump. Similar to the SQL Server error log below.
Note: This blog is applicable when you out get of memory error during startup (or) with event ID: 2019 in system event log. For general troubleshooting of SQL Server out of memory errors follow steps in Troubleshooting SQLServer Memory
{
2013-01-02 12:31:20.91 Server Microsoft SQL Server 2008 R2 (SP1) – 10.50.2500.0 (Intel X86)
Jun 17 2011 00:57:23
Copyright (c) Microsoft Corporation
Enterprise Edition on Windows NT 5.2 <X86> (Build 3790: Service Pack 2)
2013-01-02 12:31:20.91 Server (c) Microsoft Corporation.
2013-01-02 12:31:20.91 Server All rights reserved.
2013-01-02 12:31:20.91 Server Server process ID is 1583.
2013-01-02 12:31:20.91 Server Authentication mode is MIXED.
2013-01-02 12:31:20.91 Server Logging SQL Server messages in file ‘C:\Microsoft SQL Server\MSSQL10_50.MSSQLWIKIServer\MSSQL\Log\ERRORLOG’.
2013-01-02 12:31:20.91 Server This instance of SQL Server last reported using a process ID of 9240 at 1/3/2013 7:31:20 PM (local) 1/4/2013 12:31:20 AM (UTC). This is an informational message only; no user action is required.
2013-01-02 12:31:20.91 Server Registry startup parameters:
-d C:\Microsoft SQL Server\MSSQL10_50.MSSQLWIKIServer\MSSQL\DATA\master.mdf
-e C:\Microsoft SQL Server\MSSQL10_50.MSSQLWIKIServer\MSSQL\Log\ERRORLOG
-l C:\Microsoft SQL Server\MSSQL10_50.MSSQLWIKIServer\MSSQL\DATA\mastlog.ldf
2013-01-02 12:31:20.92 Server SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
2013-01-02 12:31:20.92 Server Detected 24 CPUs. This is an informational message; no user action is required.
2013-01-02 12:31:20.94 Server Address Windowing Extensions is enabled. This is an informational message only; no user action is required.
2013-01-02 12:31:27.33 Server Failed allocate pages: FAIL_PAGE_ALLOCATION 1
2013-01-02 12:31:27.33 Server
Memory Manager KB
—————————————- ———-
VM Reserved 1534584
VM Committed 51576
AWE Allocated 0
Reserved Memory 1024
Reserved Memory In Use 0
2013-01-02 12:31:27.33 Server Error: 17311, Severity: 16, State: 1. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.
2013-01-02 12:31:27.33 Server Using ‘dbghelp.dll’ version ‘4.0.5’
2013-01-02 12:31:27.34 Server **Dump thread – spid = 0, EC = 0x00000000
2013-01-02 12:31:27.34 Server ***Stack Dump being sent to C:\Microsoft SQL Server\MSSQL10_50.MSSQLWIKIServer\MSSQL\LOG\SQLDump0008.txt
2013-01-02 12:31:27.34 Server * *******************************************************************************
2013-01-02 12:31:27.34 Server *
2013-01-02 12:31:27.34 Server * BEGIN STACK DUMP:
2013-01-02 12:31:27.34 Server * 01/03/13 19:31:27 spid 4344
2013-01-02 12:31:27.34 Server *
2013-01-02 12:31:27.34 Server * ex_handle_except encountered exception C0000005 – Server terminating
}
Why would SQL Server fail with out of memory error (FAIL_PAGE_ALLOCATION)during the startup? Only possible reason that I could think of is Paged or NonPaged pool is empty.
How to prove if my Paged / NonPaged pool is empty? Look at the system event log for the Event ID: 2019
You will find error in system event log similar to one you see below.
{
Event Type: Error
Event Source: Srv
Event Category: None
Event ID: 2019
Date: 2013-01-02
Time: 12:31:00 PM
User: N/A
Computer: MSSQLWIKIServer
Description:
The server was unable to allocate from the system nonpaged pool because the pool was empty.
}
Above error indicates nonpaged pool is empty, When Nonpaged pool is empty every application would fail. How to identify who is consuming Nonpaged pool?
Use poolmon.exe from windows support tools. (Steps are documented in This KB).
If you r OS is windows 2003 or above you can simple run the exe from command prompt and identify who is consuming (Leaking J) space in Paged / NonPaged pool.
Below is sample output of poolmon.exe which I collected from my test system
Memory consumption by each tag is printed in above output. After finding the tag which is leaking the memory (Highest bytes)identify the Driver which is using the tag by using find command or strings utility from sysinternals (search for TAG in drivers folder %Systemroot%\System32\Drivers). Once you identify the driver, check if there are any known issue with the driver or you may have to contact the vendor of the driver to identify why the driver is consuming large amount of pooled /Non-pooled memory.
If you liked this post, do like us on Facebook at https://www.facebook.com/mssqlwiki , join our Facebook group MSSQLWIKI and post your SQL Server questions to get answered by experts.
Karthick P.K |
My Facebook Page |My Site| Blog space| Twitter
Disclaimer
The views expressed on this website/blog are mine alone and do not reflect the views of my company. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.