Error: 701, Severity: 17, State: 135 - There is insufficient system memory in resource pool 'default' to run this query.

A few days ago one of our customers started to complain their queries were failing because of the following error:

Error: 701, Severity: 17, State: 135 - There is insufficient system memory in resource pool 'default' to run this query.

In the SQL Server Error Log we could see a bunch of entries with the same error message which indicated the SQL Instance was having memory pressure issues. 

To give you a little bit of background, this SQL Instance had allocated only 1GB of Memory with only one User Database; for this particular scenario it was quit obvious to determine which database was consuming all the memory however when we have multiple databases on the same Instance it is important to determine which database is consuming the available memory.

The following script can be used to determine whether a database is hogging the buffer pool and should perhaps be moved to its own server or just increase the Maximum Server Memory Setting for the SQL Instance if available Memory in the server exists. 

*Note: Remember to leave enough memory to the operating system before Increasing Maximum Server Memory.


SET TRAN ISOLATION LEVEL READ UNCOMMITTED
SELECT
ISNULL(DB_NAME(database_id), 'ResourceDb') AS DatabaseName
, CAST(COUNT(row_count) * 8.0 / (1024.0) AS DECIMAL(28,2))
AS [Size (MB)]
FROM sys.dm_os_buffer_descriptors
GROUP BY database_id
ORDER BY DatabaseName

The solution for this case was just increasing the Maximum Server Memory Setting, we never saw those error entries again.