Post reply

SQL kill processes at a specific time then log to a talbe

  • April 3, 2015 at 5:42 am

    #295640

    So I have a job that shows active processes

    INSERT INTO Query_results(login_name, total_elapsed_time, total_elapsed_time)

    SELECT login_name, total_elapsed_time, total_elapsed_time FROM

    sys.dm_exec_sessions

    I need to then kill all sessions at 11:59pm then log all those that are killed.

    This is so I can schedule a job at that time, I have sessions that are blocking my job.

    Apologies, Im new. Any help will be appreciated!

  • April 3, 2015 at 6:30 am

    #1788489

    Instead of killing all the processes directly, you could run your query, capture the ones you're going to kill, then simply set the database to restricted user and use rollback immediate. That will automatically kill the processes. However, that's not the kind of thing I would do in a production system. It absolutely could lead to data loss.

    "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

  • April 3, 2015 at 7:49 am

    #1788543

    Thanks for the thoughts! deffinatly something I will work on.

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

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