locked
Rollback a transaction in another session RRS feed

  • Question

  • Hi Guys,

    Is there a way to rollback an active transaction running in another session without killing the process.

    Thanks!


    me

    Thursday, July 26, 2018 11:04 PM

Answers

  • Hi lilutchay,

    According to your description, my understanding is that you want to rollback a transaction from other session, but you still want to keep this session.

    But we can't kill/rollback a transaction from another session without killing the owner session. Setting the database to single-user mode will do the thing like kill, but it will kill all the connections.

    Best Regards,

    Teige


    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.

    • Proposed as answer by Shanky_621MVP Friday, July 27, 2018 6:45 AM
    • Marked as answer by Olaf HelperMVP Saturday, August 11, 2018 4:43 AM
    Friday, July 27, 2018 6:09 AM

All replies

  • Kill performs an clean rollback.

    Another way is ALTER DATABASE SINGLE_USER WITH ROLLBACK IMMEDIATE (but will rollback all connections).


    Friday, July 27, 2018 12:47 AM
  • Hi lilutchay,

    According to your description, my understanding is that you want to rollback a transaction from other session, but you still want to keep this session.

    But we can't kill/rollback a transaction from another session without killing the owner session. Setting the database to single-user mode will do the thing like kill, but it will kill all the connections.

    Best Regards,

    Teige


    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.

    • Proposed as answer by Shanky_621MVP Friday, July 27, 2018 6:45 AM
    • Marked as answer by Olaf HelperMVP Saturday, August 11, 2018 4:43 AM
    Friday, July 27, 2018 6:09 AM
  • Hi Guys,

    Is there a way to rollback an active transaction running in another session without killing the process.

    NO that is not possible AFAIK, unless the process foes into deadlock and is identified as deadlock victim and SQl Server will then kill this process and rollback it. 

    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

    Friday, July 27, 2018 6:47 AM