Ошибка 701 sql

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

Here at Bobcares, we have seen several such SQL related issues as part of our Server Management Services for web hosts and online service providers.

Today we’ll take a look at the cause for this error and how to fix it.

Why does Microsoft SQL Server error 701 occur

Now let’s see in detail why actually does this error occurs.

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.

In such cases, SQL will throw 701 errors. Here is an image of the error message ‘Application Log error 701’

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

How we fix Microsoft SQL Server error 701

Now let’s see what solution our Support Engineers provide.

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.

Here 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.

• First, right-click on SQL Server Instance and select properties.
• The server properties window will appear on the screen, now click on the “memory” tab from the left side pane.
• 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:

max server memory
min server memory
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.

DBCC FREESYSTEMCACHE
DBCC FREESESSIONCACHE
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 any further assistance in fixing SQL errors? – We’re available 24*7]

Conclusion

In short, SQL Server Memory pressure or insufficient memory issue can be raised due to huge data load or other memory-intensive operations that lead to a 701 error in the SQL server.

var google_conversion_label = «owonCMyG5nEQ0aD71QM»;

This question actually seems to come up every so often here. Mark has the correct (and most commonly employed) answer, but let me try to add what I can to make this clearer.

The error message is a little misleading. SQL Server tells you that it doesn’t have enough memory to run the query, but what it really means is that it doesn’t have enough memory to parse the query.

When it comes to running the query, SQL Server can use all it wants — gigabytes if necessary. Parsing is another story; the server has to build a parse tree and there is only a very limited amount of memory available for that. I’ve never found the actual limit documented anywhere but for a typical batch full of INSERT statements, it can’t handle more than a few MB at a time.

So I am sorry to tell you this but you cannot make SQL Server execute this script exactly as it is written. No way, no how, doesn’t matter what settings you tweak. You do, however, have a number of options to work around it:

Specifically, you have three options:

1. Use GO statements. This is used by SSMS and various other tools as a batch separator. Instead of a single parse tree being generated for the entire script, individual parse trees are generated for each segment of the batch separated by GO. This is what most people do, and it is very simple to still make the script transactionally-safe, as others have demonstrated and I won’t repeat here.

2. Instead of generating a massive script to insert all of the rows, keep the data in a text file (i.e. comma-separated). Then import it using the bcp utility. If you need this to be «scriptable» — i.e. the import needs to happen in the same script/transaction as the CREATE TABLE statement, then use BULK INSERT instead. Although BULK INSERT is a non-logged operation, believe it or not, it can still be placed within a BEGIN TRAN / COMMIT TRAN block.

3. If you really, really want the INSERT to be a logged operation, and don’t want the insertions to happen in batches, then you can use OPENROWSET to open up a text file, excel file, etc. as an ad-hoc «table», and then insert this into your newly-created table. I’m normally loath to ever recommend the use of OPENROWSET, but as this is clearly an administrative script, it’s not really a major problem.

Previous comments suggest that you’re uncomfortable with #1, although that may just be because of an incorrect assumption that it can’t be done in a single transaction, in which case see Thomas’s answer. But if you’re dead-set on going another way, I suggest going with #2, creating a text file and using BULK INSERT. An example of a «safe» script would be:

BEGIN TRAN

BEGIN TRY

    CREATE TABLE MyTable (...)

    BULK INSERT  MyTable
    FROM 'C:\Scripts\Data\MyTableData.txt' 
    WITH (
        FIELDTERMINATOR = ',',
        ROWTERMINATOR = '\r\n',
        BATCHSIZE = 1000,
        MAXERRORS = 1
    )

    COMMIT

END TRY

BEGIN CATCH

    ROLLBACK

END CATCH

Hopefully this helps put you on the right track. I’m pretty sure this covers all of your available «in the box» options — beyond these, you’d have to start writing actual application programs or shell scripts to do the work, and I don’t think that level of complexity is really warranted here.