locked
Porceess unable to terminate "killed/rollback" status RRS feed

  • Question

  • Hi 

    SQL server 2008 SP3 , 

    On our database had hand processes and now I killed  . But process are being there for last 5 days and still on "KILLED/ROLLBACK" . Can any one adivse to terminate the process with out rebooting the server as its business critical to reboot .regards

    Tuesday, June 18, 2019 11:54 PM

Answers

  •  Hi ashwan,

     

    >>But process are being there for last 5 days and still on "KILLED/ROLLBACK" .

     

    Would you please use KILL [spid] WITH STATUSONLY to see the percentage of rollback completed?

     

    In addition to the rollback percent complete, the statement below will give you some info about if and what the process is waiting for:

     

    select session_id, percent_complete , last_wait_type , wait_resource, wait_time, wait_type  from sys.dm_exec_requests where status = ’rollback’

     

    There are some solutions in the blog: SQL SERVER – SPID is KILLED/ROLLBACK state. What to Do Next?

     

    Hope this could help you .

    Best regards,

    Dedmon Dai


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by ashwan Tuesday, June 25, 2019 1:21 AM
    Wednesday, June 19, 2019 2:36 AM
  • Thank you expert , I am not sure reboot will resolve this issue either as SQL Server not aware this transactions (As per the Dedmon s comments )

    Cheers

    Udara

    if anything can resolve the issue it is only reboot 

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    • Marked as answer by ashwan Tuesday, June 25, 2019 1:20 AM
    Friday, June 21, 2019 6:49 AM
    Answerer
  • The reboot will roll the transaction back properly.

    This is a common problem and solution.

    • Marked as answer by ashwan Tuesday, June 25, 2019 1:20 AM
    Friday, June 21, 2019 6:08 PM
    Answerer

All replies

  •  Hi ashwan,

     

    >>But process are being there for last 5 days and still on "KILLED/ROLLBACK" .

     

    Would you please use KILL [spid] WITH STATUSONLY to see the percentage of rollback completed?

     

    In addition to the rollback percent complete, the statement below will give you some info about if and what the process is waiting for:

     

    select session_id, percent_complete , last_wait_type , wait_resource, wait_time, wait_type  from sys.dm_exec_requests where status = ’rollback’

     

    There are some solutions in the blog: SQL SERVER – SPID is KILLED/ROLLBACK state. What to Do Next?

     

    Hope this could help you .

    Best regards,

    Dedmon Dai


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by ashwan Tuesday, June 25, 2019 1:21 AM
    Wednesday, June 19, 2019 2:36 AM
  • Thank you Dedmon

    Well the jobs says  for all. 

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

    on the SQL you provided output is none. 

    I am not able to reboot the SQL instance .

    Its a trouble time for me now. 
    thank you 

    Wednesday, June 19, 2019 5:00 AM
  • Do you know what the process did? Was it an execution of an XP or query on a linked server? It that was an external process then it can happen the kill hangs for ever.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Wednesday, June 19, 2019 6:04 AM
    Answerer
  • Thank you Dedmon

    Well the jobs says  for all. 

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

    on the SQL you provided output is none. 

    I am not able to reboot the SQL instance .

    Its a trouble time for me now. 
    thank you 

    For such messages unfortunately reboot may solve things. It has nothing to rollback but is hung AFAIK you do not have any option apart from restarting SQl Server instance, you have already waited for 5 days. Please take proper approval before starting 

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Wednesday, June 19, 2019 6:54 AM
    Answerer
  • Hi ashwan,

     

    There are situations where SQL doesn’t know and has no control of work done of the work done and it has no control to roll back the work.  As mentioned by Shashank,It has nothing to rollback but is hung.These are the cases where its stays in KILLED/ROLLBACK state forever. Restarting the instance may be the only way.

     

    Hope this could help you .

    Best regards,

    Dedmon Dai


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Thursday, June 20, 2019 9:27 AM
  • You need to reboot to resolve the issue.  Sometimes that happens and the rollback will never complete.

    In addition, you need to update to a current SP and CU.  You are running a very old version.

    https://support.microsoft.com/en-us/help/321185/how-to-determine-the-version-edition-and-update-level-of-sql-server-an

    Thursday, June 20, 2019 2:16 PM
    Answerer
  • I agree with Tom. I had the same issue a couple of days ago. I had to restart the SQL Server services to solve it.

    A Fan of SSIS, SSRS and SSAS

    Thursday, June 20, 2019 2:44 PM
  • Thank you expert , I am not sure reboot will resolve this issue either as SQL Server not aware this transactions (As per the Dedmon s comments )

    Cheers

    Udara

    Friday, June 21, 2019 3:38 AM
  • Thank you expert , I am not sure reboot will resolve this issue either as SQL Server not aware this transactions (As per the Dedmon s comments )

    Cheers

    Udara

    if anything can resolve the issue it is only reboot 

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    • Marked as answer by ashwan Tuesday, June 25, 2019 1:20 AM
    Friday, June 21, 2019 6:49 AM
    Answerer
  • The reboot will roll the transaction back properly.

    This is a common problem and solution.

    • Marked as answer by ashwan Tuesday, June 25, 2019 1:20 AM
    Friday, June 21, 2019 6:08 PM
    Answerer