Fix SQL Server Error 802: There is insufficient memory available in the buffer pool.
We receive SQL Server error 802 when buffer pool of SQL Server instance is full and cannot grow further. Here, i will discuss about this error 802 insufficient memory available in the buffer pool in this article along with its possible solution to fix it. Below is the error text you might get on your SQL Server system.
SQLServer Error: 802, There is insufficient memory available in the buffer pool. [SQLSTATE 42000]
SQL Server Error 802 – Insufficient Memory Available in the Buffer Pool
The main reason behind getting SQL Server error 802 is your SQL Server instance is under memory pressure and there is not enough memory available to allocate further to buffer pool or buffer pool is restricted to not use memory beyond a certain limit. This might be because of various reasons as i have stated in below section. One of the very easy and straight forward approach to fix this error is to increase more memory to SQL Server system and modify max server memory in SQL Server accordingly.
Analysis & Solution
I would suggest to go deeper in this issue before taking any decision to allocate more memory to the system where SQL Server is running. There could be multiple temporary reasons as well which can cause SQL Server to generate below error log:
SQL Server Error: 802, There is insufficient memory available in the buffer pool. [SQLSTATE 42000]
Let’s discuss this issue further and ensure to not forget below points before taking any decisions.
- Check system memory utilization and see if other application (not SQL Server) is not taking enough memory. Try to fix it why a particular application is taking more memory.
- Make sure you have allocated enough memory in min server memory & max server memory setting. You can get these setting by executing sp_configure stored procedure. Sometimes, our server has enough memory but we missed to allocate appropriate memory to SQL Server. Rule is to allocate 80% of server memory to SQL Server under max server memory setting.
- Ensure to enabled Lock Pages in memory on your SQL Server Instance.
- Validate SQL Server version and Ensure you haven’t exceeded SQL Server edition limits. Like suppose you are running with SQL Server 2012 standard edition which allows only 64 GB of RAM support and if you have allocated more than 64GB RAM then it will not be useful. Either reduce the max server memory setting to 64 or upgrade your SQL Server to leverage bigger chunk of memory utilization.
- Observe your SQL Server workload. Are you getting this error during a specific time frame or operations? Or Are you getting this error when some job or number of adhoc transactions are increased? Gather these details with the help of memory clerks and DBCC MEMORYSTATUS T-SQL statements. Tune your quires and ensure to not run ad hoc queries in large numbers because their plan is not saved in cache and they have to generate sql plan every time you execute them.
- Collect Memory related counters like SQL Server: Buffer Manager, SQL Server: Memory Manager, Page Life Expectancy etc. You can read attached article to understand Top 10 counters to identify memory pressure.
- Review another sp_configure setting min memory per query.
- You can try clearing cache by running below DBCC commands:
- DBCC FREESYSTEMCACHE
- DBCC FREESESSIONCACHE
- DBCC FREEPROCCACHE
If you still have memory pressure after analyzing and implementing solutions given in above section then you have only two options left and this is given below:
- Increase Server memory and accordingly increase max server memory in sp_configure
- Reduce your workload on this SQL Server Instance by offloading or segregating the transactions during off peak hours.
Please comment us about your issue experience and let me know how you have fixed your issue.
- How to Fix SQL Server Error 701: There is insufficient system memory to run this query
- Understanding Hybrid Buffer Pool in SQL Server
- SQL Server Interview Questions & Answers
- Fix SQL Error 18456: failed to open the explicitly specified database - September 18, 2021
- Fix Always ON Connection Timeout Error 35206 in SQL Server - July 23, 2021
- How to Enable Preview Features in Azure Data Studio - July 15, 2021