Microsoft KB Archive/173215

= FIX: COMMIT TRAN Statement May Fail with Msg 602 =

Article ID: 173215

Article Last Modified on 3/14/2006

-

APPLIES TO


 * Microsoft SQL Server 6.5 Standard Edition

-



This article was previously published under Q173215



BUG #: 17193 (WINDOWS: 6.5)



SYMPTOMS
Attempts to perform a COMMIT TRAN may fail with Msg 602 in a database. The transaction is rolled back and the connection broken. DBCC CHECKDB and DBCC NEWALLOC do not report any corruption that did not previously exist.



CAUSE
The following statements occur 13 times or more in the transaction:


 * CREATE TABLE
 * Two or more INSERTs into this table
 * DROP TABLE

These statements may be within a stored procedure. An example of a system stored procedure that performs these operations is sp_statistics.

The following is an example of the error message in the errorlog:

Msg 602, Level 21, State 3

Could not find row in Sysindexes for dbid '4', object '994102582',index '-1'. Run DBCC CHECKTABLE on Sysindexes.

Occasionally, if the above statements are executed a sufficiently large number of times in a transaction, the error log may also contain the following message:

WARNING: Process being freed while holding Dataserver semaphore



WORKAROUND
To work around this problem, do any one of the following:
 * Make the transactions shorter.
 * Create an index on each temporary table involved in the transaction.
 * Ensure that the statements or stored procedures that must be executed more than 13 times in a transaction do not meet the criteria outlined above.
 * Revert to a service pack revision earlier than SQL Server 6.5 Service Pack 3.



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
The following script reproduces the problem scenario: DROP PROCEDURE sp_test GO

CREATE PROCEDURE sp_test AS

SELECT au_id INTO #myauthors FROM authors

INSERT INTO #myauthors SELECT au_id FROM authors

DROP TABLE #myauthors

GO

OR

CREATE PROCEDURE sp_test AS

create table #myauthors (au_id varchar(11))

INSERT INTO #myauthors (au_id) SELECT au_id FROM authors

INSERT INTO #myauthors (au_id) SELECT au_id FROM authors

DROP TABLE #myauthors

GO

Declare @cnt int Select @cnt = 0 begin tran

while (@cnt < 14) begin exec sp_test select @cnt=@cnt+1 end

commit tran

Additional query words: prodsql sp sp3

Keywords: kbbug kbfix kbusage KB173215

-

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

© Microsoft Corporation. All rights reserved.