FIX: Handled Access Violation in SQL Server with Low Memory and Procedure Cache

ID: Q192962

The information in this article applies to:

Microsoft SQL Server version 6.5

BUG #: 18028 (SQLBUG_65)

SYMPTOMS

A stored procedure, using multiple server-side scroll cursors, may cause a handled exception or access violation (AV) in SQL Server when it is running under low memory and procedure cache conditions.

Note that the number of cursors in the stored procedure is not important. For example, either a single stored procedure using multiple cursors or one stored procedure using one cursor but being used simultaneously by multiple users can cause the AV.

CAUSE

This problem occurs because SQL Server is running low on memory and procedure cache. This issue has occurred with SQL Server memory set to 16 MB and the procedure cache set to 1 percent. These settings were configured to simulate stress on these resources.

WORKAROUND

To work around this problem, do any one of the following:

Assign more memory to SQL Server.

Increase the procedure cache parameter.

Break up the stored procedure so that the procedure cache is less stressed.

Try to use fewer cursors.

STATUS

Microsoft has confirmed this to be a problem in SQL Server version 6.5. This problem has been corrected in U.S. Service Pack 5a for Microsoft SQL Server version 6.5. For information about downloading and installing the latest SQL Server Service Pack, see http://support.microsoft.com/support/sql/.

For more information, contact your primary support provider.

MORE INFORMATION

You can monitor the SQL Server cache by using DBCC SQLPERF (LRUSTATS). For further information on this command,please see the SQL Server Books Online. For recommended SQL Server memory configurations, see the following article in the Microsoft Knowledge Base:

Q168697 INF: Estimating the Initial SQL Server Memory Setting

Additional query words: prodsql sp sp5proc st


Keywords          : SSrvErr_Log SSrvStProc SSrvTran_SQL kbbug6.50 kbfix6.50.SP5 
Version           : winnt:6.5
Platform          : winnt 
Issue type        : kbbug

Last Reviewed: May 4, 1999