Killed process

[MDXer]

I had a process that I had to kill. The process had ran over night and didn't seem to be progressing. I used the KILL <<SPID>> to end the process. I know and expected the spid to then enter a KILLED\ROLLBACK status. However when I check the state of the rollback with KILL <<SPID>> WITH STATUSONLY. the roll back shows 0% complete with 27605 seconds remaining. I have checked the status a number of times and no progress is visible in the rollback. The spid was killed approx 9 hours ago.

Details on the process are.

A number of select statements populating or updating 2 #temp tables then writing the result to a physical table. The spid was killed prior to any data being input into the final table. I have people requesting the server be rebooted but I have explained this will more than likely place the DB in suspect mode and the recovery could take longer than the roll back. I figured the proccess could take as long as 16 hours to rollack but with it still at 0% I am at a loss.

Any ideas?

Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[mrdenny]

The rollback can take quite a while to complete. If you bring SQL down and bring it back up the entire server will be locked up until the rollback is complete.

Your best bet is to let it roll back over night.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[MDXer]

Update

So after 38 hours the proc still had not progressed beyond 0% research showed that the Cognos service was tieing up CPU cycles (Client machine not my choice running cognos on the same server). So I added a file to the file group to force a checkpoint and then rebooted the server. Result the DB came backup wihout going into recovery mode. My thinking is that because the proc was writing to tempDB and hadn't begun to insert Data into the DW and the DW runs in Simple mode that no major transactions were still open.

I understood the risks in taking this approach and by no means would recommend it to anyone else but figured I would post the final outcome.

Shoot Me! Shoot Me NOW!!!
- Daffy Duck