PRB: Server May Stop Responding or Get an Access Violation w/ Low Open Objects

This article was previously published under Q181563
This article has been archived. It is offered "as is" and will no longer be updated.
Symptoms
SAP SQL Servers may experience any of the following symptoms if the 'openobjects' configuration value for SQL Server is set too low:
  • Access violations.
  • SQL Server stops responding while operating under normal production.
  • SQL Server stops responding while doing DBCC CHECKDB().
Cause
The cause of the access violations and when SQL Server stops respondingwhile the server is operating under normal production is believed to occuras a result of one or both of the following two known bugs:
BUG 17420, Concurrent Insert w/ Open Object Reuse May Cause Hang
BUG 17338, Problems with Server Cursors on a Stored Procedure

For more information on these bugs, see the articles in the MicrosoftKnowledge Base listed in the MORE INFORMATION section of this article.

As of the writing of this article, a bug has not yet been filed for thethird condition, when SQL Server stops responding while running a DBCCstatement. However, it has been observed that an overlapping execution ofDBCC CHECKCATALOG() will often aggravate this problem, if not serve as thecatalyst for it to arise.
Workaround
Generally, in advising a configuration setting for the 'open objects'configuration option, it is suggested that you find the sum total of allobjects in the sysobjects tables for each database on the server, and setthe 'open objects' parameter to at least that value. By default, the 31HKernel sets the 'open objects' configuration value to 50,000 during SAPinstallation. Due to the heavy use of temporary objects by the SAPapplication, the configuration value for 'open objects' should be set to atleast twice the sum of the total objects in all user databases, or 50,000,whichever is higher.

Additionally, if cursors are known to be in use on the server, enable TraceFlag 7502 for SQL Server.

To avoid the condition where SQL Server stops responding while running aDBCC statement, execute DBCC CHECKCATALOG() either before or after DBCCCHECKDB(), but do not start DBCC CHECKCATALOG() at the same time as DBCCCHECKDB(), nor while DBCC CHECKDB() is running.
More information
For more information on Bug 17420, see the following article in theMicrosoft Knowledge Base:
178296 : BUG: Concurrent Insert w/ Open Object Reuse May Cause Hang

For more information on Bug 17338, see the following article in theMicrosoft Knowledge Base:
175126 : BUG: Problems with Server Cursors on a Stored Procedure
AV AVs hang hangs hung freeze freezes frozen lock locks locked up crash crashes crashed fail fails failed failure
Properties

Article ID: 181563 - Last Review: 11/01/2013 22:59:00 - Revision: 4.0

  • Microsoft SQL Server 6.5 Standard Edition
  • kbnosurvey kbarchive kbprb KB181563
Feedback