Every once in a while, I see the error
Failed to allocate BUFs: FAIL_BUFFER_ALLOCATION 64
in the SQL Server log. Oddly this seems to get thrown at off hours where there is no job or backup running at all.
This is a physical install of SQL Server with 128GB RAM. Here are the version details:
Microsoft SQL Server 2014 – 12.0.4213.0 (X64) Jun 9 2015 12:06:16 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.3 (Build 9600: )
Looking for some guidance.
Best Answer
This message means you are getting out of memory error (OOM). SQL Server is looking to allocate 64 KB of memory, but is unable to do so.
You are on an unsupported service pack level (SP1 + GDR). You should try patching up to the most recent level (SP2 CU13 at the time of writing). See SQL Server 2014 build versions. If it is a bug, it may well have been fixed.
It could also be due to general memory pressure. Check you have
max server memoryset correctly. For 128GB physical memory, you could probably set this to around 100GB as a starting point, depending on what else is running on the server.Also check for other activities running at the time e.g. reports, index maintenance, log shipping, etc. Bear in mind that something could be scheduled outside of SQL Server Agent e.g. Agent on another instance, PowerShell, windows task scheduler and so on. It might even be a monitoring tool running some aggregated data collection. In any case, setting SQL Server's maximum server memory configuration parameter appropriately would address this.