spid stuck in ROLLBACK

Posted on 2006-03-30
Last Modified: 2008-01-09
I killed a spid earlier - it's command is currently KILLED/ROLLBACK and has been for a very long time.  (couple hours, plus)
the cputime and disk i/o are very minimal and haven't changed at all, subsequent attempts to kill the spid return this:

spid 1: transaction rollback in progress.  Estimated rollback completion: 100%.  Estimated time remaining: 0 seconds.

but, status is RUNNABLE
and certain retrievals are actually blocked by this spid
very important, please advise as soon as possible
Question by:dbaSQL
    LVL 15

    Accepted Solution

    Only way I have been able to clear this is to stop and start SQL Server. :(
    LVL 17

    Author Comment

    so you've seen this before then?  do you know what causes it?  a restart isn't feasible right now at all.
    LVL 15

    Expert Comment

    I've had two cases when it happened.

    1.) A stored procedure was stuck in an infinite loop.  Killing the spid stopped the infinite loop but the process stayed around until I did a stop\start.

    2.)  I was copying DTS packages from one msdb database to another msdb database on another server.  The copy was being done via a trigger that had a misspelling in the table name.  The trigger would not end and both msdb databases were hung until I stopped/started SQL Server.

    These were both stupid mistakes I made while trying to do something "quick". :(   Fortunately the number of users at the site was small so no real problem with the stop/start.
    LVL 27

    Expert Comment

    I have also seen this with xp_ procedures (xp_cmdshell, etc). These are external stored procedures that run in the SQL Server address space, but SQL Server does not have any knowledge or control of them.

    DonKronos is right - you will have to stop/start the service to clear this spid out.
    LVL 17

    Author Comment

    well, unfortunately i can't do that right now.  but thank you both for your advice

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
    The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
    This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
    Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    9 Experts available now in Live!

    Get 1:1 Help Now