Microsoft KB Archive/192962

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

Article ID: 192962

Article Last Modified on 3/14/2006

-

APPLIES TO


 * Microsoft SQL Server 6.5 Standard Edition

-



This article was previously published under Q192962



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 6.5. This problem has been corrected in Service Pack 5a for Microsoft SQL Server 6.5.For more information, click the following article number to view the article in the Microsoft Knowledge Base:

197177 INF: How to Obtain SQL Server 6.5 Service Pack 5a

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:

168697 INF: Estimating the Initial SQL Server Memory Setting

Additional query words: prodsql sp sp5proc st

Keywords: kbbug kbfix kbsqlserv650sp5fix KB192962

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.