| Author |
Topic |
|
schuhtl
Posting Yak Master
102 Posts |
 Posted - 2005-08-03 : 17:20:58
|
| This morning I had to kill a processes/job that normally takes two minutes to run but for some reason it was hung for about 7 hours. Nothing was blocking the process before I killed it so I have no idea why it would not complete. After I killed the processes it has been in killed/rollback status for 7 hours and counting. When I run kill 53 with statusonly I get "SPID 53: transaction rollback in progress. Estimated rollback completion: 100%. Estimated time remaining: 0 seconds." When I query the sysprocesses table I get the following: blocked = 0, waittype = 0X000, waittime = 0, lastwaittype = PAGEIOLATCH_SH, waitresource = 1:1:1563. Any suggestions on how to resolve what appears to be a hung "kill" command? The job is scheduled to run again around midnight and I am afraid I am going to have a mess on my hands if I can't get this resolved. Thanks in advanced. |
|
|
Kristen
Test
22859 Posts |
Posted - 2005-08-04 : 00:59:13
|
I feel for you, but I have no practical advice to offer I'm afraid.If it was me I would stop-start SQL. Probably not recommended in real world situations though. Hehehe ... I sound like our help desk - "Have you tried rebooting?"  Kristen |
 |
|
|
Westley
Posting Yak Master
229 Posts |
Posted - 2005-08-04 : 01:01:55
|
| I've been in the same situtation before as well, nothing we can do apart from stop and start SQL, do hope to get an fix that that tho, if anyone knows. |
|
|
|
schuhtl
Posting Yak Master
102 Posts |
Posted - 2005-08-04 : 07:39:45
|
| I ended up stopping and restarting SQL and that did the trick. Thanks!!!! |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-08-04 : 08:23:07
|
| When all you've got is a hammer all your problemns are nails!Kristen |
|
|
|
ssivaprasad
Starting Member
9 Posts |
Posted - 2006-05-05 : 22:49:55
|
| I am also facing this problem. I think I have to cycle SQL Server services.Is there any other way of resolving it?Sivaprasad S |
|
|
|
memarcie
Yak Posting Veteran
91 Posts |
Posted - 2006-11-03 : 12:20:35
|
| I had this same problem today. killed/rolleback was hung for 2 days. I had to reboot the server to end it. |
|
|
|
AdamJB
Starting Member
1 Post |
Posted - 2007-02-09 : 12:30:24
|
| Could it be a distributed transaction accessing remote tables on a linked server? If so, try restarting the DTC Service on the local server. |
|
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2007-02-09 : 15:28:35
|
| I have had this issue once as well, and linked servers were involved.I restarted the sql-service at the time.Thanks for the DTC pointer AdamJB.rockmoose |
|
|
|
swiftr00
Starting Member
1 Post |
Posted - 2007-02-13 : 15:22:36
|
| I just recently had this same problem and the restart of DTC cleared the hung killed sessions. This was much cleaner than restarting SQL Server.Thanks |
|
|
|
dshort
Starting Member
1 Post |
Posted - 2007-07-19 : 11:08:41
|
| I agree, restarting DTC seems to work just fine. It may be a coincidence, but I don't remember running into this problem before service pack 4.... |
|
|
|
|
kill status "KILLED/ROLLBACK" for 7hrs