As a database administrator working for a growing company, I had to do a lot of bulk data loading. During one such migration and loading, I kept getting the Microsoft SQL Server Error 701.
After some research and conversations with my colleagues, I came to know that this error occurs due to memory issues. Also, I managed to fix this issue and in the process learned a lot. In this article, I’m going to share all these learnings with you.
So, read on.
What Does Error 701 Mean?
When you upload bulk data or run a large query in SQL Server 2005 and 2008, the operation can fail due to insufficient memory.
Often, you’ll get a pop-up error message that says, “There is insufficient system memory to run this query.”
What does this mean?
Let’s take a step back here to understand how SQL Server allocates memory.
Memory Management in SQL
Every SQL Server comes with a Memory Management Unit (MMU) that’s responsible for dynamically allocating memory based on the prevailing workload.
It uses the Fill-and-Flush algorithm where the default configuration doesn’t limit memory use. In other words, SQL’s MMU keeps allocating memory until it runs out of it. As there are no upper limits by default, you don’t get notifications when memory usage reaches a certain threshold.
Typically, SQL determines memory management based on two aspects – the type of parameters and the size of your batch request. Let’s look at each of these aspects briefly.
Type of Parameters
SQL handles certain parameters like sql_variant a bit differently. When your client system sends a batch of requests with these parameters, the SQL Server accumulates all of it before allocating memory. So, if you can send multiple queries with the “sql_variant” parameter, SQL can run out of memory.
Batch Size
The second aspect is the batch size. When you send large requests quickly, the SQL Server may not have enough memory for executing all of them. This is further compounded if your requests also include the parameters discussed above.
In all, the memory management in SQL Server, especially in SQL Server 2005, is not designed for such large loads and variants. The SQL Server 2008 and subsequent versions have better memory management algorithms though.
Now, going back to error 701, when SQL’s MMU runs out of memory, it’s unable to complete the operation and throws this error.
Undoubtedly, this can be frustrating when you’re in the middle of executing a large transaction. Nevertheless, every problem has a solution, and here are some ways to troubleshoot the Microsoft SQL Server error 701.
Solutions To Fix Windows Error 701
We briefly looked at SQL’s memory management earlier and how it can lead to error 701. Here are some solutions to fix this issue.
Reduce The Batch Size
This solution is a no-brainer really!
Instead of sending large batches of requests, reduce the number of requests to avoid the memory allocation problem. That said, it’s not easy to know what is the “right” batch size and may require some trial and error.
While this may be a bit time-consuming at first to understand the batch size limit, it can become easier later on.
Change The Parameter Types
As discussed earlier, parameters like the sql_variant are handled differently by the SQL Server. To avoid error 701, consider replacing these parameter types with other similar ones that perform the same function but are handled differently by the SQL Server.
While both these solutions are fairly straightforward and address the core cause of error 701, they are not easy to implement. This is why I’d like to show you some solutions that are essentially a workaround to the above fixes, but they are more practical and easier to implement.
A Dedicated Server
One good workaround is to increase the memory allocation, so the SQL Server never runs out of it, even while running large batches with the sql_variant parameter type.
You can do this by running the SQL Server instance on a dedicated server that’s not shared with other applications. This way, all the available memory can be used by the SQL Server alone.
Change The Server Memory Configuration
Three configuration parameters determine memory allocation and usage in SQL Servers and they are:
- Min memory per query
- Min memory
- Max memory
Increase the value of the “max memory” parameter based on the memory available in the system. A rule of thumb is to evaluate how much memory your largest batch request will need and add 10% more as a buffer. This should be the value of your max memory configuration parameter.
The idea here is to ensure that your SQL Server never runs out of memory to execute the largest batch file. Needless to say, ensure that your batch file size doesn’t increase beyond the allocated memory.
Free Unused Memory
Look at different ways to free unused memory as this will ensure more optimal use of your memory. Also, you don’t need costly memory upgrades or dedicated servers, and in turn, this will save you some money.
That said, ensure that the available memory after freeing up the used ones is sufficient to meet the SQL query requirements.
Check The Available Memory
As a precondition for evaluating large SQL batch queries, check the available memory. To do this, use the DBCC MEMORYSTATUS command. This command will display the available memory, and you can evaluate if it’s sufficient to run your largest query.
Third-party Tools
There are many third-party tools available today to continuously evaluate the performance of your SQL Server. These tools check the usage of memory, CPU, and other resources, and send notifications when these values are close to an established threshold.
You can leverage these tools to ensure that your SQL Server always has enough memory to run the queries. You can ensure this by either reducing your batch size or increasing your SQL Server memory, whichever is feasible.
Thus, these are some solutions to fix the Microsoft SQL Server error 701.
