INF: How to Troubleshoot Message 701 and Server Cursors

• Microsoft SQL Server, versions 6.0 and 6.5

SUMMARY

If server cursors are not closed properly, the procedure cache may be consumed. When you attempt to open other cursors or run any Transact-SQL command that requires procedure cache (such as stored procedures, triggers, or views), you may encounter the following error:

   Msg 701 There is insufficient system memory to run this query.

MORE INFORMATION

SQL Server 6.0 and 6.5 allocates internal data structures in the server procedure cache when a server cursor is declared. Failing to properly close server cursors may result in undesirable procedure cache consumption, and eventually produce error 701.

One primary consideration to keep in mind when evaluating cursor usage is whether your application really needs a server cursor. If you have a small result set and do not require the row operations that are provided by server cursors, you may want to consider processing your results without using a server cursor. For interfaces such as DB-Library, the use of server cursors is explicitly based on the API needed (for example, dbcursoropen). However, for an ODBC application, a server cursor may be used without your knowledge, based on how you have called SQLSetStmtOption(). Check the documentation for your programming interface on how to control the use of server cursors. The "PROGRAMMING INTERFACES AND SERVER CURSORS" section of this article provides a brief description of how server cursors are used with many of the popular Microsoft programming interfaces for SQL Server results set processing.

If you encounter error 701 in your application, consider tracing server cursor usage as a first step. The following section describes methods to aid in this process. It is important to understand that error 701 may also occur independently of server cursor usage. An application may intermittently generate this error as a result of heavy concurrent use of stored procedures. Therefore, if you encounter error 701 and you cannot trace any server cursor use, it is very possible that procedure cache must be increased using the 'procedure cache' or 'memory' configuration options.

Tracing Cursor Operations

Regardless of the programming interface that uses sever cursors, developers should ensure that cursors are closed properly, to avoid unnecessary procedure cache usage. One quick method to determine whether cursors are properly closed is to trace the SQL commands run by SQL Server. You can use the SQLTrace utility for SQL Server 6.5, or you can use trace flag 4032 for SQL Server 6.0 or 6.5.

SQLTrace provides an option to save trace output to a script or log file. Use this file after testing the application as a "trace output" for analysis. The RPC event must be selected when setting up a filter to capture the required information. See the SQL Server 6.5 documentation and SQLTrace online Help for more information about setting up filters and capturing a script file.

If you are using SQL Server 6.0, set trace flag 4032 to capture incoming SQL commands to the server. The following are two basic methods you can use to do this:

• Start Sqlservr.exe with the command line parameters -T4032 and -T3605.
• Run dbcc traceon(-1, 3605, 4032) from Isqlw.exe or Isql.exe to set up capturing of SQL commands.

If you use a server cursor for any of the programming interfaces (excluding ANSI Transact-SQL cursors), search the trace output (with a utility such as Windows NT Server Findstr.exe) for the sp_cursoropen and sp_cursorclose stored procedures. If the application has run to completion or has completed results set processing, every call to sp_cursoropen should have a corresponding sp_cursorclose. There is no need to know or understand the calling convention of these procedures. They are used "behind-the-scenes" to invoke server cursors for SQL Server, if you are not using ANSI Transact- SQL cursors. For ANSI Transact-SQL cursors, you should see a match for DECLARE and DEALLOCATE statements. For Transact-SQL cursors, it is the DEALLOCATE statement, not the CLOSE, that actually frees up cursor procedure cache resources.

If the trace output from the application shows 100 calls to sp_cursoropen (or Transact-SQL OPEN), but no calls to sp_cursorclose (or Transact-SQL DEALLOCATE) or fewer than 100, you may well encounter an error such as error 701. In this situation, evaluate the application code to determine why the cursor is not being closed.

SQL Server 6.5 also provides new performance monitor counters you can use to trace procedure cache usage. If an application encounters error 701, you can use these counters to get a quick understanding of procedure cache consumption. However, there is no counter or command to tell you the difference between procedure cache consumption from a server cursor and normal stored procedure plan usage. For more information about potential problems using these counters, please see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q155766
   TITLE     : BUG: Perfmon Terminates While Monitoring SQL Procedure Cache

PROGRAMMING INTERFACES AND SERVER CURSORS

For ANSI Transact-SQL cursors, procedure cache allocation and deallocation occurs on the DECLARE and DEALLOCATE commands, respectively. However, if the application was developed with other programming interfaces that use server cursors, it may not be apparent that a cursor has been left open.

Although closing the connection to the server with any of the interfaces listed below will essentially deallocate procedure cache resources for the cursor, it is recommended that server cursors be explicitly closed based on the suggestions listed below. Always refer to the product documentation based on the programming interface of choice for the most current information on server cursor usage. For more information, see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q156489
   TITLE     : INF: Overview of SQL Server, ODBC, and DB_Library Cursors

ANSI Transact-SQL Cursors

ANSI Transact-SQL Cursors were introduced in SQL Server 6.0. A DECLARE statement results in procedure cache allocation for an internal structure that describes the cursor definition. The DEALLOCATE statement is necessary to free up the procedure cache. Please note that running the CLOSE statement does NOT free up the procedure cache for the cursor.

In SQL Server 6.5, a new SET option, CURSOR_CLOSE_ON_COMMIT, is now available. This option closes an open cursor if you run a COMMIT. However, this option does not deallocate procedure cache resources. A DEALLOCATE cursor is still required to free up cursor procedure cache resources.

DB-Library:

A server cursor is used with DB-Library cursor routines (such as dbcursoropen or SQLCursorOpen%) if you are connected to a computer running SQL Server 6.0 or 6.5 and you do not have the DBCLIENTCURSOR option set. The server cursor opened by dbcursoropen (SQLCursorOpen% for DB-Library for Visual Basic) must be closed and deallocated by explicitly calling dbcursorclose (SQLCursorClose for DB-Library for Visual Basic).

ODBC API:

For ODBC applications that use the ODBC API and the ODBC SQL Server driver, server cursors are always used to retrieve result sets, except under the following conditions:

• The user sets ODBC_CURSORS to SQL_CUR_USE_ODBC and declares the cursor to be static.
• The user declares the cursor to be forward-only, read-only, rowset = 1.

   ARTICLE-ID: Q138280
   TITLE     : INF: SQLGetInfo Returned with SQL_PRESERVE_CURSORS

   ARTICLE-ID: Q139655
   TITLE     : INF: Tracing SQL Generated by MS SQL Server ODBC Driver

   ARTICLE-ID: Q157802
   TITLE     : INF: SQL Server ODBC Driver Performance Analysis Tools

Server cursors can be used with the ODBC SQL Server driver by setting the rdoDefaultCursorDriver or CursorDriver property. Even if the option is set, server cursors will not be used if the cursor is read-only and forward- only.

Server cursors opened by RDO must be closed using the Close method for the object. Otherwise, the server cursor will not be closed until the application exits.

For more information about RDO and server cursors, please see Chapter 11, "Using Remote Data Objects and the RemoteData Control" in the Visual Basic 4.0 Enterprise Edition Documentation.

Microsoft Foundation Classes (MFC) Database Classes:

MFC CRecordSet Classes support the use of server cursors using the SQL Server ODBC driver if the record set is defined as a dynaset. The keyset server cursor is opened when the Open method of the derived class is invoked. Explicitly call the Close method of the CRecordSet derived class to close the server cursor.

For more information on the CRecordSet class in MFC, please see the MFC Class Library Reference in the Visual C++ documentation.