FIX: COMMIT TRAN Statement May Fail with Msg 602

Retired KB Content Disclaimer

View products that this article applies to.

This article was previously published under Q173215

BUG #: 17193 (WINDOWS: 6.5)

Expand all | Collapse all

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.

Back to the top

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

Back to the top

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.

Back to the top

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 (http://support.microsoft.com/kb/197177/ ) INF: How to Obtain SQL Server 6.5 Service Pack 5a

For more information, contact your primary support provider.

Back to the top

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

Back to the top

APPLIES TO

Microsoft SQL Server 6.5 Standard Edition

Back to the top

kbbug kbfix kbusage KB173215

Back to the top

Retired KB Content Disclaimer

This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.

Back to the top