May 22, 2012 at 6:52 am
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?
May 22, 2012 at 7:08 am
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
May 22, 2012 at 7:18 am
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.
May 22, 2012 at 7:22 am
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
May 22, 2012 at 7:28 am
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.
May 22, 2012 at 7:33 am
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
May 22, 2012 at 7:43 am
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?
May 22, 2012 at 9:43 am
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
May 22, 2012 at 11:36 pm
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
May 29, 2012 at 11:07 am
thank you all, i had to restart service, the CPU and IOs kept increasing and we started having performance issues.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply