Welsh Corgi (7/7/2012)
I had killed the processes 17 hours before I restarted the Service.
The percent complete of the rollback was 0% on all four processes.
What I found odd was that the Last Batch Dates were one 6/09/2012 and three 6/10/2012.
They were executing Stored Procedures with an OPENQUERY Statement to a remote DB2 Database on an AS400.
Actually, this is a well know problem to those using OPENQUERY to a remote DB2 database on an AS400. I went through the very same problem just about a week ago. It turns out that there's a "CONNECT" item on the subject and MS closed it with a "Call Customer Support when this happens" answer.
What happens with these things is that the rollbacks were actually successful and the "0%" to go message (in these instances) is actually correct. The problem with the continued attempts at rollbacks is that the SPIDs are actually consuming a whole lot of CPU time. It's like they're stuck in a loop or something. They also keep locks on the database which can sometimes interfere with things such as dropping the database (which I do every night on my test restore system).
Once all other methods to try to overcome this problem have been exhausted, bouncing the service (or even the machine itself) is the only way to kill these things. The problem with that is that not all rollbacks are so kind. If there actually is something to rollback, the rollback (maybe roll forward) will continue during the startup. I've built some code at work that I used to determine if there was actually anything to rollback to see if it was actually safe to bounce the service.
There's one more thing that you have to do after that... alert the DB2 folks because killing the job will leave a long running job open on the DB2 side, as well. It does about the same thing as it does in SQL Server... it spins its wheels eating a lot of CPU to do nothing.
is pronounced ree-bar and is a Modenism for R
First step towards the paradigm shift of writing Set Based code: Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair
How to post code problemsHow to post performance problemsForum FAQs