SQL 2014 FAIL_PAGE_ALLOCATION 129

Question

SQL 2014 FAIL_PAGE_ALLOCATION 129

ran.ovadia

Ten Centuries

Points: 1183
More actions
August 9, 2017 at 5:04 am

#338678

Hi,
I migrated by DBs from SQL2008R2 to SQL2014 on a same server model: Phyiscal with 128 GB RAM.
Since the migration, when we run heavy queries we often get the error: Failed to allocate pages: FAIL_PAGE_ALLOCATION 129.
BTW, we don't use BPE.
Also the Percent of Commited Memory in WS is 99.
Can anyone help me to understand the issue and find a fix for that?
Thanks,
Ran.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

NorthernSoul SSCertifiable Points: 6869 More actions · Answer 1

ran.ovadia - Wednesday, August 9, 2017 5:04 AM
Hi,
I migrated by DBs from SQL2008R2 to SQL2014 on a same server model: Phyiscal with 128 GB RAM.
Since the migration, when we run heavy queries we often get the error: Failed to allocate pages: FAIL_PAGE_ALLOCATION 129.
BTW, we don't use BPE.
Also the Percent of Commited Memory in WS is 99.
Can anyone help me to understand the issue and find a fix for that?
Thanks,
Ran.

What have you set the max server memory to and is the server a dedicated database server?

Thanks

NorthernSoul SSCertifiable Points: 6869 More actions · Answer 2

NorthernSoul - Wednesday, August 9, 2017 5:36 AM
ran.ovadia - Wednesday, August 9, 2017 5:04 AM
Hi,
I migrated by DBs from SQL2008R2 to SQL2014 on a same server model: Phyiscal with 128 GB RAM.
Since the migration, when we run heavy queries we often get the error: Failed to allocate pages: FAIL_PAGE_ALLOCATION 129.
BTW, we don't use BPE.
Also the Percent of Commited Memory in WS is 99.
Can anyone help me to understand the issue and find a fix for that?
Thanks,
Ran.
What have you set the max server memory to and is the server a dedicated database server?
Thanks

I also for got to add: Is there any more detail in the error log you can supply?

Thanks

ran.ovadia Ten Centuries Points: 1183 More actions · Answer 3

Hi,

I tried 120 GB for SQL, also 80... nothing.
In the error log there is the usual logs after buffer failure.

NorthernSoul SSCertifiable Points: 6869 More actions · Answer 4

ran.ovadia - Wednesday, August 9, 2017 8:23 AM
Hi,
I tried 120 GB for SQL, also 80... nothing.
In the error log there is the usual logs after buffer failure.

What is the total memory for your server and can you confirm if it is a dedicated database server?
When you say ''nothing'' do you mean nothing happened or that you were still getting the error?

Thanks

ran.ovadia Ten Centuries Points: 1183 More actions · Answer 5

The server runs only SQL. 2 Instances while in the other it is all good.
In the error log during this time there are just rows regarding the error while the server failed to allocate the page in memory.

NorthernSoul SSCertifiable Points: 6869 More actions · Answer 6

ran.ovadia - Wednesday, August 9, 2017 8:39 AM
The server runs only SQL. 2 Instances while in the other it is all good.
In the error log during this time there are just rows regarding the error while the server failed to allocate the page in memory.

Can you answer my previous questions please?

Thanks

ran.ovadia Ten Centuries Points: 1183 More actions · Answer 7

Nothing as nothing happened... the error still occurs.
As for the server, Is it only SQL server.

10x.

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 8

You stated you have 2 instances on this one server - what are the min/max memory settings for each instance? You also stated that the committed memory is 99% which would mean there is no memory available even for the OS.

When you have multiple instances you need to look at all instances and insure that max memory for all instances total less than the memory available on the server. A server with 128GB of memory and two instances should have no more than 104GB of total max memory allocated. That would be 52GB per instance or 70GB for one and 34GB for the other instance.

Windows needs quite a bit of memory available to manage the memory for SQL Server. Even with a single instance server I would not allocate more than 112GB to that instance - and then only if I was absolutely sure we would never run into any issues that could max the server memory. Also be aware that memory allocation is only for the buffer cache - other memory is utilized for CLR, SSIS, SSAS, SSRS and any other services running on that system.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs