FIX: Sysprocesses Is Incorrectly Showing Rollback Status Even Though the Transaction Is Not Rolling Back

Article translations Article translations
Article ID: 289691 - View products that this article applies to.
This article was previously published under Q289691
BUG #: 352036 (SHILOH_BUGS)
Expand all | Collapse all

On This Page

SYMPTOMS

Under normal processing conditions, sysprocesses output may show system process IDs (SPIDs) with a status of Rollback when that transaction is not truly being rolled back but is, in fact, being committed. You can see this Rollback status when the CMD column indicates INSERT, UPDATE, or COMMIT TRAN.

RESOLUTION

To resolve this problem, obtain the latest service pack for Microsoft SQL Server 2000. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:
290211 INF: How to Obtain the Latest SQL Server 2000 Service Pack

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article. This problem was first corrected in Microsoft SQL Server 2000 Service Pack 1.

MORE INFORMATION

Steps to Reproduce Behavior

  1. Open two windows in Query Analyzer.
  2. On the first window, start the following query that monitors for a status of Rollback:
    while 1=1
    begin
      declare @time datetime
      select @time = getdate()
      if exists (select * from master.dbo.sysprocesses where status = 'rollback')
      select @time as CurrentTime,status,* 
        from master.dbo.sysprocesses where status = 'rollback'
      waitfor delay '00:00:05'
    end
    					
  3. On the second window, start the following query to loop through the code until the query in the first window captures the problem:
    use pubs
    create table test
    (col1 int)
    go
    declare @i int 
    select @i =1 
    while @i < 100000
    begin 
      insert into test values('1')
      select @i= @i+1
    end
    go
    drop table test
    go
    					
  4. When you observe the problem, stop both queries by either pressing the CONTROL-C keyboard combination, or by clicking the red STOP button in Query Analyzer.

    In the results, note that the SPID has a status of Rollback, although the data has been inserted in the database. Also note that the waittype shows 0x0081 (waiting on writelog) while the status is Rollback.

Properties

Article ID: 289691 - Last Review: November 6, 2003 - Revision: 3.2
APPLIES TO
  • Microsoft SQL Server 2000 Standard Edition
Keywords: 
kbbug kbfix kbsqlserv2000sp1fix KB289691

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com