FIX: COMMIT TRAN Statement May Fail with Msg 602 (173215)

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