-5

I’m looking for a single file that does these things in order.

  1. Declare a variable to hold a job name
  2. Run a query to check whether that job declared is currently running
  3. Set up an if loop If the job is running, execute the stored procedure that kills it.

My question is to write a query that declare a variable to hold a job name and gives us result the job is still running or not if the job is running execute the stored proc to kill it.

My manager was asking me to listing running jobs and suspended jobs and declare a variable to hold job name and setup an IF loop like if job is running run proc to kill it.

0
1

First, get your variables declared.

Second, develop your query to find the job name.

Third, develop your logic check, did the job name X run? Count and set to a flag essentially.

Fourth, execute the stop command for the job name variable.

Note: If you are stopping a SQLCMD job, it may not stop.

Reference: Stop a Job (MSDN)

USE MSDB
GO
DECLARE @variablename VARCHAR(255)
DECLARE @variablecount INT
SELECT
@variablename = N'jobname'

SET @variablecount = (
                    SELECT
                        COUNT(*)
                    FROM
                        (
                            SELECT
                                sj.name
                            FROM
                                msdb.dbo.sysjobactivity AS sja
                            INNER JOIN msdb.dbo.sysjobs AS sj
                            ON  sja.job_id = sj.job_id
                            WHERE
                                sja.start_execution_date IS NOT NULL AND
                                sja.stop_execution_date IS NULL AND
                                sj.name = @variablename
                        ) subquery
                    )

IF @variablecount = 1
EXEC dbo.sp_stop_job @variablename  

Here is the code that will grab ALL jobs running, put them into a variable, and loop through killing them. Test this in your own testing instance before putting this code anywhere vital.

This is an awful practice and no one should use this method. I would probably just stop SQL Agent if I had to kill every job. I would also never develop myself into a hole where you have to run something to kill every job, you should just un-schedule them or put checks and balances into place.

   -- Set Database
USE MSDB;
-- Declare variable table to hold job names
DECLARE @variablenametable TABLE
    (
        Id INT PRIMARY KEY
                IDENTITY(1 , 1)
        ,variablename VARCHAR(255)
    );
-- Holds the job name to stop
DECLARE @variablename VARCHAR(255); 
-- Dynamic SQL holder
DECLARE @variablenamekill NVARCHAR(MAX);
-- Holds the ID of the job in the loop
DECLARE @ID INT = 0;
-- Check if jobs are running
IF EXISTS ( SELECT
                sj.name
            FROM
                msdb.dbo.sysjobactivity AS sja
            INNER JOIN msdb.dbo.sysjobs AS sj
            ON  sja.job_id = sj.job_id
            WHERE
                sja.start_execution_date IS NOT NULL AND
                sja.stop_execution_date IS NULL )
    BEGIN 
-- Insert names of jobs running into the table variable
        INSERT  INTO @variablenametable
                (
                    variablename
                )
                SELECT
                    sj.name
                FROM
                    msdb.dbo.sysjobactivity AS sja
                INNER JOIN msdb.dbo.sysjobs AS sj
                ON  sja.job_id = sj.job_id
                WHERE
                    sja.start_execution_date IS NOT NULL AND
                    sja.stop_execution_date IS NULL; 

-- Begin loop               
        WHILE (1 = 1)
            BEGIN   
    -- Grab first name of job to kill
                SELECT
                    @variablename = (
                                        SELECT TOP 1
                                            variablename
                                        FROM
                                            @variablenametable
                                    );  
    -- Sets dyanmic SQL to kill     
                SET @variablenamekill = N'EXEC msdb.dbo.sp_stop_job ' + QUOTENAME(@variablename) + '';

    -- Kills jobs
                EXEC sp_executesql @variablenamekill;

    -- Removes job name from the list of jobs to kill   
                DELETE FROM
                    @variablenametable
                WHERE
                    variablename = @variablename;

    -- No more jobs, stops killing
                IF (
                    SELECT
                        COUNT(*)
                    FROM
                        @variablenametable
                    ) <= 0
                    BREAK;
    -- End Loop             
            END;
    -- End elseif
    END;
ELSE
    PRINT 'No Jobs' 

I left the variable names intentionally vague so you can alter them as you would prefer. Exists would be more efficient because it will stop the subquery when it finds any records rather than count, the count has more potential for reporting user at a later timer. You can see some examples of subbing out the exists command here: SQL Server: EXISTS Condition.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.