Microsoft SQL Server throws error 701 if there is any insufficient memory issue.

Here at Ibmi Media, we regularly help our Customers to fix SQL related issues as part of our Server Management Services.

In this context, we shall look into the main causes of this error and how to get rid of it.

What triggers Microsoft SQL Server error 701 ?

During any bulk data load or during any memory-intensive operations, the database instance might face some SQL Server memory issues.

We may also come across some insufficient memory issue while trying to execute numerous queries on large data sets at the same time in SQL Server.

Mainly this error occurs if SQL Server is running out of memory and has no more memory available to allocate to its transactions.

In such cases, SQL will throw 701 errors.

How to fix Microsoft SQL Server error 701 ?

If we are facing any memory issue in the SQL server then we first need to troubleshoot the issue and find the root cause for the memory consumption.

Below is the step by step process to find the cause of memory consumption and possible solutions.

1. SQL Server instance running on a dedicated server

First, make sure that the SQL Server instance is running on a dedicated server and not with other applications.

In case, if other applications are also using the database server, then make sure they are not eating most of the memory.

If other applications are taking more memory then migrate it to other database servers. If it is not possible to migrate then allocate the maximum memory value to your SQL Server instance. 

You can do it using SQL Server Management Studio.

i. First, right-click on SQL Server Instance and select properties.

ii. The server properties window will appear on the screen, now click on the “memory” tab from the left side pane.

iii. You can change the value of min and max server memory as per your need in the right-side panel.

2. Check server memory configuration

Secondly, check the server memory configuration. Here are the SQL Server memory configuration parameters that we need to check:

i. max server memory

ii. min server memory

iii. min memory per query

If there is any difference in the value of max server memory and min server memory then increase the value of ‘max server memory’.

3. Check the workload

Check the workload in terms of the number of concurrent sessions. Also, check the SQL Server transactions that are using huge memory values. Make a report on them and try to optimize such that it will take minimum memory.

4. Find Index usages for long-running queries

Find out index usages for long-running queries. We need to do this because without proper indexing your system DISK I/O increases and it directly affects your memory.

5. Run DBCC commands

We also recommend running the below DBCC commands to free several SQL Server memory caches.

i. DBCC FREESYSTEMCACHE

ii. DBCC FREESESSIONCACHE

iii. DBCC FREEPROCCACHE

6. The output of the DBCC MEMORYSTATUS command

Getting the output of DBCC MEMORYSTATUS command is also a great way to look into the memory status.

7. Performance monitor counters for memory

Start collecting performance monitor counters for memory. Performance counters like the value of SQL Server: Buffer Manager\Buffer Cache Hit Ratio, SQL Server: Memory Manager, and Page Life Expectancy will be of great help in identifying memory pressure on the SQL Server system.

Then you can analyze these counters and decide whether the issue is coming during some specific operations or system is continuously facing memory pressure.

Based on these analyses, you can choose to either increase the system memory, increase the SQL Server memory allocation or reschedule some of the memory specific transactions to the off-hours when the load is minimum on the system.

[Need urgent assistance in fixing SQL errors? – We're available 24*7. ]