cmd KILLED/ROLLBACK process for 7 hours

Question

cmd KILLED/ROLLBACK process for 7 hours

DBA-640728

SSChampion

Points: 12898
More actions
May 22, 2012 at 6:52 am

#258517

last night I had to kill a processes/job that normally takes 1 min to run but for some reason it was hung for about 8+ hours. Nothing was blocking the process before I killed it so I have no idea why it would not complete. After I killed the processe it has been in killed/rollback status for 7 hours+. When I run kill 55 with statusonly I get "SPID 55: transaction rollback in progress. Estimated rollback completion: 100%. Estimated time remaining: 0 seconds." the spid is running with program name .Net SqlClient Data Provider (biztalk). .
After doing some research i read that by restarting the DTC or sql service might resolve the problem. Does anyone suggest to try anything else?
If i restart the DTC in my cluster, can it create any issues with my other transactions?

Viewing 10 posts - 1 through 9 (of 9 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Restart the SQL service.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

DBA-640728 SSChampion Points: 12898 More actions · Answer 2

thanks Gila, can this spid create any issues if it remains running for a couple of hours? blocking , high cpu , etc? or since it is in hung status is just running on the background without causing performance issues? i am asking to see if i can hold off in restarting the service untill off hours.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

it's probably not doing anything, you can confirm that by watching sys.dm_exec_requests, the CPU and reads columns. You will very likely need to restart sometime to get rid of it, if something's been sitting at 100% rollback, 0 sec for ages, it's stuck waiting for something.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

DBA-640728 SSChampion Points: 12898 More actions · Answer 4

i just checked running kill 55 with statusonly

and i get :

SPID 55: transaction rollback in progress. Estimated rollback completion: 0%. Estimated time remaining: 0 seconds.

but it has been there for hours.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Same as above.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

DBA-640728 SSChampion Points: 12898 More actions · Answer 6

thanks Gila, i checked select * from master..sysprocesses

where status = 'runnable'

order by CPU

desc

and this spid is in the top cpu column utilizing 298787621.

also when i run SELECT *

FROM sys.dm_exec_requests

order by cpu_time desc

this spid is on the top cpu_time.

that means it is creating cpu performance issues correct, so i have to restart now?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Watch it over time and see if it is using CPU and doing more IOs

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Sergiy SSC Guru Points: 110208 More actions · Answer 8

DBA-640728 (5/22/2012)
last night I had to kill a processes/job that normally takes 1 min to run but for some reason it was hung for about 8+ hours. Nothing was blocking the process before I killed it so I have no idea why it would not complete. After I killed the processe it has been in killed/rollback status for 7 hours+. When I run kill 55 with statusonly I get "SPID 55: transaction rollback in progress. Estimated rollback completion: 100%. Estimated time remaining: 0 seconds." the spid is running with program name .Net SqlClient Data Provider (biztalk). .
After doing some research i read that by restarting the DTC or sql service might resolve the problem. Does anyone suggest to try anything else?
If i restart the DTC in my cluster, can it create any issues with my other transactions?

Your process must be doing something outside od SQL Server: command shell, remote server, etc.

If it's stuck there (e.g. "The file with this name already exits. Overwrite?" ) SQL Server has no control over it.

Kill the remote process and SQL transaction will commit/rollback depending on the error handling in your process.

_____________
Code for TallyGenerator

DBA-640728 SSChampion Points: 12898 More actions · Answer 9

thank you all, i had to restart service, the CPU and IOs kept increasing and we started having performance issues.