SQL Server Error 802 can be resolved easily with the Bobcares by your side. 

At Bobcares, we offer solutions for every query, big and small, as a part of our Server Management Services.

Let’s take a look at how our Support Team is ready to help customers with SQL Server Error 802.

What is SQL Server Error 802?

One of our customers had the misfortune of running into the following SQL server error message recently:

SQLServer Error: 802, There is insufficient memory available in the buffer pool. [SQLSTATE 42000]

After careful analysis, our Support Techs have come to the conclusion that this specific error is due to a lack of memory for buffer pool allocation or buffer pool restriction when it comes to using memory beyond a particular limit.

We can resolve this by increasing the memory allocation to the SQL Server System. Fortunately, our Support Team is well-equipped to deal with errors like these.

How to resolve SQL Server Error 802

1. First, head to the system memory utilization and check if any other application is using more memory.
2. Then, check the min server memory & max server memory settings and ensure there is sufficient memory.

   We can access these settings by executing sp_configure procedure. Our Support Team recommends allocating 80% of the server memory to the SQL server via the max server memory setting.
3. Next, confirm Lock Pages in memory are enabled on the SQL Server Instance.
4. After that, it is time to validate the SQL Server version. For instance, if we are using SQL Server 2012, it offers only 64GB RAM. In case we allocate more than 64GB RAM, we are most likely to run into SQL Server Error 802.

   Hence, it is wise to reduce the max server memory value or upgrade the SQL Server to avoid the error.
5. Then, take a close look at the SQL Server workload and answer the following questions:

   • Does the error occur during a particular time frame or operation?
   • Does the error pop up when a specific job or number of adhoc transactions go up?

   We can collect these details via memory clerks as well as DBCC MEMORYSTATUS T-SQL statements.
6. Now, it is time to gather memory-related counters like SQL Server: Memory Manager, SQL Server: Buffer Manager,  Page Life Expectancy, and so on. You can refer to this article for further reference.
7. After that, review the sp_configure setting min memory per query.
8. Next, clear the cache with these DBCC commands like DBCC FREESYSTEMCACHE, DBCC FREESESSIONCACHE and DBCC FREEPROCCACHE.

Additional Troubleshooting Tips

Here are a few additional troubleshooting tips by our skilled Support Engineers to resolve this error:

• Try increasing Server memory as well as max server memory in sp_configure.
• Another option would be to lower the workload on this specific SQL Server instance via offloading. We can also segregate transactions during off-peak hours.

[Looking for a solution to another query? We are just a click away.]

Conclusion

In brief, our skilled Support Engineers at Bobcares demonstrated how to go solve SQL Server Error 802.