Post reply

Issuing a kill command

  • December 19, 2013 at 8:12 am

    #287621

    Hi.

    This is a straightforward one (i think ..).

    Does issuing a kill command to a session roll back any transactions that may have happened during the time period of the query ?

  • December 19, 2013 at 8:17 am

    #1674842

    No. Only open, uncommitted transactions.

  • December 19, 2013 at 12:12 pm

    #1674952

    Wait, define "during time period of query"

    SQL Server suggests that it supports nested commits, but it doesn't really. If you have a transaction and it had three transactions inside it, if one of them rollsback, they're all rolling back and the kill command does that.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • December 23, 2013 at 2:56 am

    #1675529

    Thanks for your replies guys.

    @Grant - what I basically meant with by 'during the time period of a query' is that if a query has run for say 10mins and because of this we wish to kill it because it was causing performance issues ( it was a test query by one of the devs which he was going to maunally commit at the end once he had seen how long it took to run ), when i issue the kill command i wanted to know that everything would be how it was prior to the command being issued.....does that make sense ? Or does the fact that he hadn't committed it anyway mean things would be as they where once I kill it ?

  • December 23, 2013 at 3:14 am

    #1675531

    Any statements executed prior to a BEGIN TRANSACTION statement would not be rolled back. Everything after would be.

    It's worth noting every statement has its own implicit transaction and they are committed upon successful completion. If you start an explicit transaction it won't be committed until you issue the COMMIT statement.

  • December 23, 2013 at 8:01 am

    #1675615

    Yeah, that makes more sense, and yes, it should just roll back to where things were.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • December 23, 2013 at 8:44 am

    #1675636

    I would advise that killing a SPID should be considered a final and very desparate act because killing a SPID (it's documented on CONNECT as a major problem) will sometimes leave a SPID in a "0% to go" rollback that sometimes eats a whole CPU. The only way such a thing can currently be repaired is to bounce the SQL Service.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 7 posts - 1 through 7 (of 7 total)

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