FAIL_PAGE_ALLOCATION

Question

Post reply

FAIL_PAGE_ALLOCATION

andreas.kreuzberg

SSCertifiable

Points: 6191
More actions
November 10, 2023 at 1:30 pm

#4325177

Hello everybody,
today one of our aoag crashed. Suddenly we were not able to connect via SSMS to the aoag listener, or to the actice primary node.
We got this entry in the ERRORLOG, but I was not able to found something in the internet:
spid143,Unknown,Failed allocate pages: FAIL_PAGE_ALLOCATION 16
(ERRORLOG from the sql server, who was the primary when the error ocurred)
11/10/2023 13:44:30,spid83s,Unknown,A connection timeout has occurred on a previously established connection to availability replica 'srdbpw127' with id [A7B7B4DF-FEEC-48D1-8449-74A496D09E9D]. Either a networking or a firewall issue exists or the availability replica has transitioned to the resolving role.
(ERRORLOG from the sql server, who was the secondary whe the error ocurred)

We were able to do a failover, and the aoag was fine. But we had to restart the sql server service, to be able to connect to the sql server.
In Red Gate SQL Monitor we could see, that Red Gate was able to collect the windows counter, but was not able to collect the sql server counter.
I have no idea whats wrong:
virtuell sql server 2022 (up to date)
windows server 2022 (up to date)
8 CPU
64 GB RAM
I thougt about NUMA problems, could this be a valid reason for this error?
thanks a lot,
kind regards,
Andreas
February 5, 2024 at 7:50 am

This was removed by the editor as SPAM
February 5, 2024 at 7:51 am

This was removed by the editor as SPAM

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

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

Steve Jones - SSC Editor SSC Guru Points: 735816 More actions · Answer 1

This help? Low memory? https://dba.stackexchange.com/questions/141236/failed-allocate-pages-fail-page-allocation-1

A few other links point to this.

andreas.kreuzberg SSCertifiable Points: 6191 More actions · Answer 2

Hello Steve,

thanks for this link. I will try my best, but we are not able to change the query, and we don't know when the error occurs.

Maybe the OS got less memory, we reserved 6 GB for the OS, or is this "only" memory pressure in the sql server?

We do Statistic Updates every 6h, maybe we should do this more often?

This is a database from a "callcenter", where the reports where build. Maybe there are a lot of data changes every minute.

Best regards,

Andreas

andreas.kreuzberg SSCertifiable Points: 6191 More actions · Answer 3

Hi,

we added some RAM, and the windows server got 96GB. We reserved 10GB for the OS.

The error was not reported since the upgrade.

Best regards,

Andreas

Steve Jones - SSC Editor SSC Guru Points: 735816 More actions · Answer 4

Steve Jones - SSC Editor

SSC Guru

Points: 735816

November 20, 2023 at 11:32 pm

#4331377

Thanks for the note

Jeff Moden SSC Guru Points: 1004224 More actions · Answer 5

andreas.kreuzberg wrote:

We do Statistic Updates every 6h, maybe we should do this more often?

Hopefully, you're REALLY selective about which stats get updated.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

andreas.kreuzberg SSCertifiable Points: 6191 More actions · Answer 6

Hello Jeff,

we take a look at the statistics at 10 a clock in the morning.

Only a view statistics were not up to date. And they were changed by the sql server himself.

SELECT name AS index_name, 
       STATS_DATE(OBJECT_ID, index_id) AS StatsUpdated
FROM sys.indexes
       ORDER BY StatsUpdated DESC

There is a lot of traffic in the database, but with the amount of RAM we should be able to run the application without error.

Thanks for your help and your ideas.

Best regards,

Andreas