SQLTeam.com | Weblogs | Forums

Error “There is insufficient system memory in resource pool 'internal' to run this query.” even after updating to SP3 on SQL Server 2008 R2

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.
Buffer Pages Distr

  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 ?

This is s good read. Talks about adhoc queries. Sit with your devs and review this