Post reply

How to kill Job and associaed command dynamically(through commands)

  • January 23, 2014 at 12:36 am

    #284775

    Hi,

    I am using Sql server 2008 r2

    I Created the Job through sp_add_job

    eg, Job name = 'Test_job'

    with step command = Exec SPname

    This SP takes half hour to execute.

    Now,my requirement is, I want to Kill the job,and query running associated with this should also be stopped.

    also all this should be done using commands not the wizard.

    Please help me,as I need this urgently.

    Thanks in advance!!!!

  • January 23, 2014 at 2:28 am

    #1682662

    By running the job it starts a session. What you need is find the spid and execute

    KILL spid

    USE msdb

    GO

    EXEC dbo.sp_help_jobactivity

    GO

    you can find the job and see some details of it.

    Regards,

    IgorMi

    Igor Micev,
    My blog: www.igormicev.com

  • January 23, 2014 at 4:44 am

    #1682710

    You can query sys.dm_exec_requests in combination with sys.dm_exec_sql_text to identify the query and session and then kill it.

    ----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • January 23, 2014 at 5:17 am

    #1682719

    I do a kill on current users (there should be none) just before a full backup. I create a temp table of users with:

    SELECT

    P.spid as SPID,

    P.loginame as CurLogin,

    P.hostname as HostName,

    D.name as DBName,

    P.cmd as Command,

    P.cpu as CPUTime,

    P.physical_io as DiskIO,

    P.last_batch as LastBatch,

    P.[program_name] as ProgramName

    FROM sys.sysprocesses P

    LEFT JOIN sys.databases D ON P.dbid = D.database_id

    WHERE P.spid > 50

    AND P.loginame NOT IN ('SpecialLogin')

    AND P.hostname NOT IN ('SpecialHost')

    -- select nothing for KnownKeep that is active within the last hour from kill list

    AND NOT (D.name = 'KnownKeep'

    AND p.last_batch > DATEADD(hour, -1, GETDATE())); This may be of help. I am going to look into the other suggestions to see if they may be of help for my process.

  • January 23, 2014 at 5:50 am

    #1682733

    Session ID > 50 is not a good way to determine system processes. Instead use a join to sys.dm_exec_sessions to use is_user_process something like this:

    SELECT *

    FROM sys.dm_exec_requests AS der

    JOIN sys.dm_exec_sessions AS des

    ON der.session_id = des.session_id

    WHERE des.is_user_process = 1 ;

    ----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • January 23, 2014 at 5:59 am

    #1682737

    Thanks for the tip. The code was developed in SQL2000 and never updated properly.

Viewing 6 posts - 1 through 5 (of 5 total)

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