Error/Fix: There is insufficient system memory in resource pool ‘default’ to run this query.

Recently I was reading about Temporal Tables feature that has been introduced with SQL Server 2016. So, as usual, I thought to get the knowledge in practice with my virtual lab. I tried starting SQL Services, but to my surprise, SQL Services did not start even after multiple attempts. Checking the SQL Server Error Logs found below message:-

Error: 701, Severity: 17, State: 137.
There is insufficient system memory in resource pool ‘default’ to run this query.
SQL Server shutdown has been initiated

Resolution:-
The message indicates that SQL Instance need more memory to start. In my case SQL had 768 MB Max memory with 1 GB total Box physical memory. So, the first step taken was to increase Box memory from 1 GB to 4 GB. But since the SQL Max memory was configured for 768 MB, SQL Services could not start. And I needed the SQL Services up and running before I can update the Max Memory of SQL Instance. This is the situation where we need to start the SQL Services in Single-User Mode.

So at first, I started SQL Services using below command in Command Prompt-

Replace the ‘MSSQLSERVER‘ with service name for your Sql Instance by checking services.msc.

Once the SQL Services started in Single-User mode, I simply connected to SQL Instance using SQLCMD utility and updated the Max Memory Configuration using TSQL.

Friends, please feel free to correct me by comments. If you like the article, do Like & Share. Happy Coding 🙂