Script to find session running SQL text and kill session

  • Hi

    Looking for a  SQL script I can run as an agent job that will search for any active sessions running a piece of SQL code and kill the session or sessions running that code if found.

    If anyone could enlighten?

    Thanks in advance

     

     

     

     

     

  • You'll want to reference sys.dm_exec_requests. That's the DMV that shows actively running queries. Combine it with sys.dm_exec_sql_text to search for a string that's currently running something like this:

    SELECT der.session_id
    FROM sys.dm_exec_requests AS der
    CROSS APPLY sys.dm_exec_sql_text(der.sql_handle) AS dest
    WHERE dest.text LIKE '%SELECT *%';

    That will get you the list of session_id values. Put that into a temp table or table variable or a cursor and then kill them them one at a time.

    "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

  • Ok thanks a lot, anyone any idea how to code the second part? To actually do the kill

  • It's just a loop that's needed. Try using the SELECT statement above and create a cursor. Then execute an a dynamic statement with the kill and the session_id value.

    "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

  • Ok thanks but if I knew how to do that I wouldn't be on here....maybe someone else has an idea. ...or there is a similar script out there I have not found.

    • This reply was modified 3 years, 2 months ago by  caz100.
  • Although sometimes unavoidable, killing sessions is actually a pretty dangerous thing to do in SQL Server.  Killing sessions automatically is about 3 times worse.

    Prevention and privs control is the best bet.  What is it that you're trying to prevent?

     

     

    --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)

  • its a temporary solution to an unavoidable issue of blocking ....don't want to kill all sessions blocking just this one running this code. If anyone knows how to script it all please divulge.

    Surprised there isn't a script out there for this ...

    Thanks in advance

     

     

  • Is it a stored procedure or ad hoc code or ???

     

    --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)

  • A piece of code , not a stored procedure but kicked off by an app at wrong time of day and no idea how to turn off from app.

    Could put in a Job to run during business hours and kill and if it occurs if I knew how., maybe I need to post this to dev group as its more scripting.

  • Ok.  So what in the code uniquely identifies that code and ONLY that code?  Which DB_ID does it run against?  What is the USER_ID the code from the front end runs as?

    I ask these questions to help you write a bit of code where the cure won't actually be worse than the cause.

     

    --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 10 posts - 1 through 9 (of 9 total)

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