We are getting this error :
“There is insufficient system memory in resource pool 'internal' to run this query.”
everyday, and the error lasts for about a minute, after that the queries are executed normally with no error.
At first we assumed this was a bug in SQL Server 2008 R2 SP2 10.52.4000.0 according to the article https://support.microsoft.com/en-sg/help/982854/fix-error-there-is-insufficient-system-memory-in-resource-pool-interna, and updated to Service pack 3, but the error still continues.
We use the Express edition, and are aware that there is a limit on the ram that SQL server instance can use to run queries. According to this article here: https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms143685(v=sql.105)?redirectedfrom=MSDN
The table here shows the limit on SQL Server Express as 1 GB.
On analysing the perfmon logs, the counter
MSSQL$SQLEXPRESS:Memory Manager\Total Server Memory (KB) had value 1443840 KB
and
MSSQL$SQLEXPRESS:Memory Manager\Target Server Memory (KB) had value 1443840 KB
We assume this is how high the SQL Server Express Editions can consume.
Also this was the Memory Consumption report that was taken after we had encountered the problem. Here we see that Stolen Pages are too high, tried searching regarding this and came up with the result that high stolen page value can cause Memory pressure.
- 1.Could this High Stolen page value be a reason for the error the we encounter?
- 2.The reason these Stolen pages being this high, are they not freed automatically?
- 3.How to Free these Stolen pages ?
