asked
SQL SERVER MEMORY CAP REACHED TO ITS MAXIMUM STATE AND THAN THREW AN OUT OF MEMORY EXCEPTION.
We have set a memory cap limit of 43GB in SQL server instance, so thereafter it keeps 42 GB near about and keeps near about 1 GB for its processing operations. But then we received near about 500,000 record to process via one of our external application, and then due to lack of memory space our system start to log the Out of Memory exception (An exception of type 'System.OutOfMemoryException' occurred and was caught). In this case we restart SQL server instance and it works correctly for some days, but thereafter it reaches to its max memory cap limit the same issue continues and we restart server again and it continues. Is there any way we can get rid of this. Please Help! ![alt text][1] [1]: /storage/temp/2984-1.png
You have some answers below. This web site runs by you voting and marking the best answers. For each helpful answer below, indicate this by clicking on the thumbs up symbol next to those answers. If any one answer lead to a solution, indicate this by clicking on the check mark next to that answer. This helps indicate your solution for anyone else with the same question.
It looks like you need to set the Maximum Server Memory to a lower number -- that setting defines (roughly) the maximum amount of memory that SQL Server will use, which should be most of but NOT ALL of the actual memory in the physical server (if this is a virtual machine, I may defer to others, but first I'd suggest that you confirm that the host's memory is not over allocated). And by and large, SQL Server will use the entirety of the Maximum Server Memory -- any excess not needed for computations will be used to improve performance by, for example, caching tables.
You do not state the total amount of memory in the server, but:
(1) you should always leave enough memory for the operating system and any other services (how much? opinions vary, as these links show, but I'd suggest you try at least 4 GB: https://www.sqlservercentral.com/blogs/suggested-max-memory-settings-for-sql-server-20052008 and https://www.brentozar.com/blitz/max-memory/ and
(2) however much you have left for the operating system and other services right now, it sounds like it is not enough. Try dropping the maximum server memory by 1 GB and see if that helps. SQL Server will adjust to manage itself within the lower amount of memory.
I doubt that SQL Server itself is the issue here. SQL will generally behave where memory is concerned. What is the external application you refer to actually doing ? and what technology is it using ? I have seen memory issues with SSIS in particular when dealing with large numbers of records.
As previously mentioned, reduce your max memory setting. Try it at say 32000 ( instead of 44032 ). This will release 12GB for other processes. ( The Operating System usually needs 4GB to be safe ).
What is the external application doing and what is it using ? This may not be an issue with SQL Server. SQL Server will handle 500,000 records easily, regardless of what you are doing with them and it generally behaves itself where memory is concerned. I have seen issues like this with SSIS in the past. Could your exeternal application be using SSIS or something similar ?
20 People are following this question.
