SQL Server Job is in Rollback state

Question

Hi Can anyone tell me how to stop a SQL server job which is in Roll Back State. I tried to kill the Process Id but it do not work I tried to Stop it manually but No joy. Please tell me?

Answer 1

Patience, the job will have to rollback. Depending on the ressources that are needed to be rolled back, this can take some time.

-Jens

---

Jens K. Suessmeyer http://blogs.msdn.com/Jenss

Answer 2

Just in case anyone else comes across this...

The rollback may not actually be doing anything except burning clock cycles.  It's a known and common problem and MS has closed the Connect item for the problem as "Call Customer Support".  If you can prove that the rollbacks aren't actually rolling back anything, the general concensus on that Connect item is that you will need to bounce the SQL Service on the machine that's affected.  Warning... if you bounce the service where a rollback actually is in progress, the DB will either continue the rollback or, very rarely, roll forward.  In either case, the database will remain offline and unusable until the roll is complete and there's nothing you can do about it (or at least nothing I've found, yet).  You won't even be able to drop and restore the DB.

If you decide to bounce the server, don't do it on my say so.

Here's the link for the Connect item.  I recommend you Google for scripts that determine if a rollback is actually occurring or not.

https://connect.microsoft.com/SQLServer/feedback/details/433703/killed-rollback#tabs

---

--Jeff Moden