Article ID: 181563 - View products that this article applies to.
This article was previously published under Q181563
This article has been archived. It is offered "as is" and will no longer be updated.
SAP SQL Servers may experience any of the following symptoms if the 'open objects' configuration value for SQL Server is set too low:
The cause of the access violations and when SQL Server stops responding while the server is operating under normal production is believed to occur as 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 Microsoft Knowledge 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 the third condition, when SQL Server stops responding while running a DBCC statement. However, it has been observed that an overlapping execution of DBCC CHECKCATALOG() will often aggravate this problem, if not serve as the catalyst for it to arise.
Generally, in advising a configuration setting for the 'open objects' configuration option, it is suggested that you find the sum total of all objects in the sysobjects tables for each database on the server, and set the 'open objects' parameter to at least that value. By default, the 31H Kernel sets the 'open objects' configuration value to 50,000 during SAP installation. Due to the heavy use of temporary objects by the SAP application, the configuration value for 'open objects' should be set to at least 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 Trace Flag 7502 for SQL Server.
To avoid the condition where SQL Server stops responding while running a DBCC statement, execute DBCC CHECKCATALOG() either before or after DBCC CHECKDB(), but do not start DBCC CHECKCATALOG() at the same time as DBCC CHECKDB(), nor while DBCC CHECKDB() is running.
For more information on Bug 17420, see the following article in the Microsoft Knowledge Base:
(https://support.microsoft.com/kb/178296/EN-US/ ): BUG: Concurrent Insert w/ Open Object Reuse May Cause Hang
For more information on Bug 17338, see the following article in the Microsoft Knowledge Base:
(https://support.microsoft.com/kb/175126/EN-US/ ): BUG: Problems with Server Cursors on a Stored Procedure