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.

 

 

Thank you,

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.