I am running Microsoft SQL Server 2014 Enterprise Edition SP1 (build 12.0.4213.0 (X64)), with 288GB of RAM, and max memory set to 280GB (I arrived at that number based on many recommended suggestions from SQL Server MVPs).
Twice in the last month I have received:
There is insufficient memory available in the buffer pool
…followed by other errors, most notably:
The log for database 'db' is not available
The database goes into suspect mode. If I restart the SQL service, it comes back fine. I have an SSD that is 1,863GB in size and buffer pool extension on it with a size of 1,859GB in size (I left almost a 5 GB cushion on the SSD).
The error seems to indicate RAM memory pressure. At the time it errors, the server is under high pressure. But this is not uncommon, however, the errors appearing are new. Should I be targeting my RAM modules for testing? Or is somehow the Buffer Pool Extension causing the error?
We monitor this box with SolarWinds DPA, plus I use DMVs to get realtime info, so I have a real good idea of what was running at the time. Most waits were memory related.
The buffer pool extension data was around 1TB when it went down, so there was plenty of room there. This is a production server, with a very small maintenance window one day a week. Db is in full recovery model, with 1 full backup a day, and trans logs backed up every 30 minutes.
- This is a dedicated SQL Server box.
- The first error that shows is the insufficient memory error.
- The database that errored is 3.22 TB on an iSCSI mapped drive, set to auto grow by 100GB. There is 800GB free currently. Log file is 646GB in size, set to autogrow by 30GB. There is 154GB free. All dbs together is 4TB. All mdf's and ldf's are on their own iSCSI mapped drive.
No hardware or disk related errors in Event Viewer. The only error in the SQL Server error log before it dumps is:
04/04/2016 07:42:32,spid77,Unknown,Failed to allocate BUFs: FAIL_BUFFER_ALLOCATION 24
sys.dm_os_loaded_modules shows only my ODBC driver.
I think raising or lower the SQL Server max memory wont change the problem. SQL Server should be able to release memory on its own. We have been running in this configuration for years without a problem. However, this new issue has happened twice recently. The only changes I can think of are that we added an SSD for the buffer pool extension.
I did a diagnostic test on the RAM to see if anything turned up, it did not. I also updated SQL Server 2014 to SP1 CU5, however the error occurred again. This time though, it did not take the database into suspect/recovery mode. So that helped. The questions still remains as to why it is occurring.
Best Answer
The best answer I can come up with is that Sql really doesn't have any room in the buffer pool to give up. Meaning, we have too many queries running, or the queries running have plans that are taking up too much space. This link was somewhat helpful No Free Buffers. My solution at this point is to evaluate the queries that were running when the error occurs, so see if I can tune them.