none
SP_WHO2 command reading KILLED/ROLLBACK RRS feed

  • Question

  • I ran a normal select query against a linked server. I cancelled it in SSMS after about 10 seconds of running.

    The cancel process took was taking ages, So i then killed the local spid.

    A message was returned saying "transaction rollback in progress. estimated rollback completion: 0%. estimated time remaining: 0 seconds."

    I checked SP_WHO2 and the command column is reading KILLED/ROLLBACK.

    The database which I am querying is scheduled to be restored from a backup tomorrow morning. Will this affect the restore process?

    I am a little bit worried that I have done something silly. Can anyone put my mind at rest... 



    Mr Shaw

    Monday, February 23, 2015 6:18 PM

Answers

  • Can you run KILLSPID WITH STATUSONLY; and check how much % and time left.

    Thanks,

    -SreejitG

    • Marked as answer by Mr Shaw Monday, February 23, 2015 7:09 PM
    Monday, February 23, 2015 6:34 PM
  • Linked server is connected to other DB sources or external files like excel?

    Can you check on sp_lock any locking or blocking causing the rollback not to happen?

    Check with below code to see if there is any long running process waiting for any waits.

    SELECT
            r.session_id
    ,       r.start_time
    ,       TotalElapsedTime_ms = r.total_elapsed_time
    ,       r.[status]
    ,       r.command
    ,       DatabaseName = DB_Name(r.database_id)
    ,       r.wait_type
    ,       r.last_wait_type
    ,       r.wait_resource
    ,       r.cpu_time
    ,       r.reads
    ,       r.writes
    ,       r.logical_reads
    ,       t.[text] AS [executing batch]
    ,       SUBSTRING(
                                    t.[text], r.statement_start_offset / 2,
                                    (       CASE WHEN r.statement_end_offset = -1 THEN DATALENGTH (t.[text])
                                                     ELSE r.statement_end_offset
                                            END - r.statement_start_offset ) / 2
                             ) AS [executing statement]
    ,       p.query_plan
    FROM
            sys.dm_exec_requests r
    CROSS APPLY
            sys.dm_exec_sql_text(r.sql_handle) AS t
    CROSS APPLY    
            sys.dm_exec_query_plan(r.plan_handle) AS p
    ORDER BY
            r.total_elapsed_time DESC;

    • Marked as answer by Mr Shaw Monday, February 23, 2015 7:09 PM
    Monday, February 23, 2015 6:51 PM
  • Let me be rude who asked you to kill, you first kill and then you ask for solution. If you are beginner and still learning why you  killed it you could have taken someones help. And then you ask very basic question. If it is killed it will take time to rollback you have no option nut to wait.

    Let it be a learning to you to not fiddle with SQL Server unless you know what you are doing.


    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 Article

    MVP


    Monday, February 23, 2015 6:52 PM
    Moderator

All replies

  • Can you run KILLSPID WITH STATUSONLY; and check how much % and time left.

    Thanks,

    -SreejitG

    • Marked as answer by Mr Shaw Monday, February 23, 2015 7:09 PM
    Monday, February 23, 2015 6:34 PM
  • It is still showing 

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


    Mr Shaw

    Monday, February 23, 2015 6:36 PM
  • Linked server is connected to other DB sources or external files like excel?

    Can you check on sp_lock any locking or blocking causing the rollback not to happen?

    Check with below code to see if there is any long running process waiting for any waits.

    SELECT
            r.session_id
    ,       r.start_time
    ,       TotalElapsedTime_ms = r.total_elapsed_time
    ,       r.[status]
    ,       r.command
    ,       DatabaseName = DB_Name(r.database_id)
    ,       r.wait_type
    ,       r.last_wait_type
    ,       r.wait_resource
    ,       r.cpu_time
    ,       r.reads
    ,       r.writes
    ,       r.logical_reads
    ,       t.[text] AS [executing batch]
    ,       SUBSTRING(
                                    t.[text], r.statement_start_offset / 2,
                                    (       CASE WHEN r.statement_end_offset = -1 THEN DATALENGTH (t.[text])
                                                     ELSE r.statement_end_offset
                                            END - r.statement_start_offset ) / 2
                             ) AS [executing statement]
    ,       p.query_plan
    FROM
            sys.dm_exec_requests r
    CROSS APPLY
            sys.dm_exec_sql_text(r.sql_handle) AS t
    CROSS APPLY    
            sys.dm_exec_query_plan(r.plan_handle) AS p
    ORDER BY
            r.total_elapsed_time DESC;

    • Marked as answer by Mr Shaw Monday, February 23, 2015 7:09 PM
    Monday, February 23, 2015 6:51 PM
  • Let me be rude who asked you to kill, you first kill and then you ask for solution. If you are beginner and still learning why you  killed it you could have taken someones help. And then you ask very basic question. If it is killed it will take time to rollback you have no option nut to wait.

    Let it be a learning to you to not fiddle with SQL Server unless you know what you are doing.


    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 Article

    MVP


    Monday, February 23, 2015 6:52 PM
    Moderator
  • Thank you for all your help.

    SQL Server has now sorted itself out.

    I'm am sorry that I didn't ask the forum earlier... everything is a learning curve. 


    Mr Shaw

    Monday, February 23, 2015 7:04 PM