Attempted to kill processes, but they remain in Killed/Rollback status

Question

Attempted to kill processes, but they remain in Killed/Rollback status

VA123

SSCarpal Tunnel

Points: 4272
More actions
October 20, 2011 at 6:55 am

#244924

Using sql server 2008 r2 64-bit with the database engine and Reporting Services running on the same machine.
Several days ago we had two reports execute that called stored procedures that use linked servers to an AS400 machine. There was a network problem that broke the connection to the AS400. The SQL Activity Monitor showed that these two processes were still running after several days and the Task Manager shows the CPU at 100%. We attempted to kill the processes yesterday in Activity Monitor, but the Task State still shows "Running" and the Command still shows "Killed/Rollback" after 20 hours.
The sys.sysprocesses table is showing the linked server name in the waitresource field and the status is runnable.
Should we reboot the server to finally kill the 2 processes? I read that a reboot may put the database in suspect state.
Thanks.

Viewing 5 posts - 1 through 4 (of 4 total)

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

Can you restart the distributed transaction coordinator instead of rebooting? If the queries don't modify any local data, that might kill them without having to reboot.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

MysteryJimbo SSC-Insane Points: 24203 More actions · Answer 2

I've had a couple of these "ghost" rollbacks in the past. The only way to get rid of them is to restart the sql service.

I would check what locks the process was holding first and make sure it was safe to do so.

Not something to do lightly.

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 3

I had a ghost rollback a couple weeks ago, it was in the killed/rollback state and kept reporting that it was 4% complete, but the time kept rising, when I finally did a restart it was reporting over 50,000,000 seconds to complete, or about 1.6 YEARS. But nothing was being blocked because the process was a backup. I avoided a SQL restart because I didn't really want to recover a 1.1TB database..

CEWII

VA123 SSCarpal Tunnel Points: 4272 More actions · Answer 4

Thanks, but restarting the Distributed Transaction Coordinator did not end the processes. I resorted to a server reboot since the server is also running Reporting Services.