Process is still "cancelling"

  • I had a process that was running for many hours from my SSMS. It was BDCC shrinkfile. I tried to Cancel query from SSMS, but again, it shows "cancelling query" for many hours.

    sys.dm_exec_requests shows 80.60408 percent_complete for that session_id and does not move. What is solution ?

    Thanks

  • The solution is to wait. It is rolling back everything that was done and may take some time.

    Jared
    CE - Microsoft

  • This is what I also thought, but hoped that there is some solution. I've been waiting for many hours already. Actually it is cancelling longer than was original execution. Will recycling the server help ? Most of our databases, if not all of them, are in simple recovery model.

  • SQL Guy 1 (3/13/2012)


    This is what I also thought, but hoped that there is some solution. I've been waiting for many hours already.

    Wait until it's finished, it's not uncommon for rollbacks to take longer than the roll forward. Check that it isn't blocked by anyone.

    Will recycling the server help ?

    Maybe, or maybe you'll have to continue waiting with the database offline and unavailable to anyone.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Sometimes, restore this database is faster in my experience.

  • But can we restore a database in at least one connection is not closed ?

  • SQL Guy 1 (3/13/2012)


    But can we restore a database in at least one connection is not closed ?

    no Restore requires exclusive access to the database. with the canceling of the query it is rolling back the transaction so even ALTER DATABASE dbName SET SINGLE_USER WITH ROLLBACK IMMEDIATE wont really work for you as it would need to roll back the transaction your all ready trying to rollback.

    How many times can i say rollback in one sentence and still make sense?


    For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

    For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

    Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
    Jeff Moden's Cross tab and Pivots Part 1[/url]
    Jeff Moden's Cross tab and Pivots Part 2[/url]

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply