There is insufficient system memory in resource pool ‘internal’ to run this query in SQL Server 2014.

There is insufficient system memory in resource pool ‘internal’ to run this query in SQL Server 2014.

We have encountered “Insufficient Memory “issue on SQLServer cluster and found below error in Event Viewer and Error.log

Below are the main reasons for the issue.

• The physical memory is completely used and not available for SQLServer.
• SQLServer engine max memory allocation has been reached limit.
• Virtual Memory is full.

First find out which process are consuming memory, if any tools or application processes outside of sql server consuming and then you can close or kill the process, if not important.

Run the below command to find out memory Status.

DBCC MEMORYSTATUS

You can also run the below commands to clear the memory.

1. Query:

SET NOCOUNT ON

SELECT

    b.Date as Snapshot_Taken,

    DATEDIFF(ss,b.Date,GETDATE()) As Seconds_Delta,

    a.name AS RG_Pool_name,

    a.statistics_start_time as Last_Statistics_Update_start_time,

    (a.read_bytes_total  - b.read_bytes_total)/1024/1024/1024 AS Read_GB_Delta,

    (a.write_bytes_total - b.write_bytes_total)/1024/1024/1024 AS Write_GB_Delta,

    --a.used_memgrant_kb - b.used_memgrant_kb AS Used_memgrant_kb_delta,

    a.cache_memory_kb - b.cache_memory_kb AS Cache_memory_kb_Delta,

    a.total_memgrant_count - b.total_memgrant_count AS Total_memgrant_count_Delta,

    (a.max_memory_kb/1024/1024) AS Max_Mem_GB,

    (a.used_memory_kb/1024/1024) AS Used_Mem_GB

FROM sys.dm_resource_governor_resource_pools a

JOIN tempdb.dbo.__RG_resource_pools    b ON a.name = b.name

2. Query:

USE tempdb

SET NOCOUNT ON

IF OBJECT_ID('tempdb.dbo.__RG_resource_pools') IS NOT NULL BEGIN

    DROP TABLE tempdb.dbo.__RG_resource_pools

END

SELECT GETDATE() as Date, *

INTO tempdb.dbo.__RG_resource_pools   

FROM sys.dm_resource_governor_resource_pools

3. Query:

SELECT

spid,status,sid,hostname,program_name,cmd,cpu,physical_io,blocked,dbid,

convert(sysname, rtrim(loginame)) as loginname,spid as 'spid_sort',

substring( convert(varchar,last_batch,111) ,6 ,5 ) + ' ' + substring( convert(varchar,last_batch,113) ,13 ,8 ) as 'last_batch_char',last_batch, DB_NAME(dbid)

from master.dbo.sysprocesses (nolock)

order by loginname

Query Output:

Restart the System after apply the Query.