sql-job-monitoring.sql

CREATE PROCEDURE dbo.CustomSQLJobMonitoring
@JobName sysname
, @RunTimeout int
, @RunStatus int output
, @StatusLog nvarchar(1024) output
AS
SET NOCOUNT ON

--Verify that this job exists
IF NOT EXISTS (SELECT 1 FROM msdb.dbo.sysjobs WHERE [name] = @JobName)
BEGIN
    SET @RunStatus = 5 --Unknown
    RAISERROR('Invalid job name ''%s''', 16, 245, @JobName);
    RETURN 1
END;

--Start the job
DECLARE @retval int;
exec @retval = msdb.dbo.sp_start_job @job_name=@JobName;

--If start succeeded, poll for completion
IF @retval = 0 
BEGIN
    PRINT N'Job started successfully';
	SET @StatusLog = N'Job started successfully. '
    WAITFOR DELAY '00:00:05';

    DECLARE @JobRunTime int;
    SET @JobRunTime = 0;
    SET @RunStatus = 5; --Unknown -> default return

    WHILE @JobRunTime < @RunTimeout
    BEGIN
        WAITFOR DELAY '00:00:05';

        --SELECT statement below give the same result as 'sp_help_jobactivity' sys-proc
        SELECT @JobRunTime = CASE WHEN stop_execution_date IS NULL THEN DATEDIFF(SECOND, start_execution_date, GETDATE()) ELSE @RunTimeout END 
        FROM ( 
            SELECT ja.session_id, ja.job_id, j.[name] job_name, ja.run_requested_date, ja.run_requested_source, ja.queued_date, ja.start_execution_date
                , ja.last_executed_step_id, ja.last_executed_step_date, ja.stop_execution_date, ja.next_scheduled_run_date, ja.job_history_id
                , jh.[message], jh.run_status, jh.operator_id_emailed, jh.operator_id_netsent, jh.operator_id_paged
            FROM msdb.dbo.sysjobactivity ja
            JOIN msdb.dbo.sysjobs j ON ja.job_id = j.job_id
            LEFT JOIN msdb.dbo.sysjobhistory jh ON ja.job_history_id = jh.instance_id
            WHERE ja.session_id = (SELECT MAX(session_id) FROM msdb.dbo.sysjobactivity ja1 WHERE ja1.job_id = ja.job_id AND ja1.run_requested_date IS NOT NULL)
                AND j.[name] = @JobName
        ) JobActivity;
    END;

    --Get the final stats
    SELECT @RunStatus=run_status, @JobRunTime=DATEDIFF(SECOND, start_execution_date, stop_execution_date)
    FROM (
        SELECT ja.session_id, ja.job_id, j.[name] job_name, ja.run_requested_date, ja.run_requested_source, ja.queued_date, ja.start_execution_date
            , ja.last_executed_step_id, ja.last_executed_step_date, ja.stop_execution_date, ja.next_scheduled_run_date, ja.job_history_id
            , jh.[message], jh.run_status, jh.operator_id_emailed, jh.operator_id_netsent, jh.operator_id_paged
        FROM msdb.dbo.sysjobactivity ja
        JOIN msdb.dbo.sysjobs j ON ja.job_id = j.job_id
        LEFT JOIN msdb.dbo.sysjobhistory jh ON ja.job_history_id = jh.instance_id
        WHERE ja.session_id = (SELECT MAX(session_id) FROM msdb.dbo.sysjobactivity ja1 WHERE ja1.job_id = ja.job_id AND ja1.run_requested_date IS NOT NULL)
            AND j.[name] = @JobName
    ) JobActivity;

    PRINT N'Job completed in ' + CONVERT(nvarchar, @JobRunTime) + ' seconds.'
	SET @StatusLog += N'Job ' + @JobName + ' completed in ' + CONVERT(nvarchar, @JobRunTime) + ' seconds.'
    IF @RunStatus = 1 RETURN 0; --Success
    ELSE RETURN 1; --Failed
END;
ELSE
BEGIN
    PRINT N'Job could not start';
    SET @RunStatus = 5 --Unknown
    RETURN 1; --failed
END;

Start from SQL:

use master;
DECLARE @RunStatus int, @retval int, @StatusLog nvarchar(1024)
exec @retval=dbo.CustomSQLJobMonitoring @JobName='JobName', @RunTimeout=3600, @RunStatus=@RunStatus output, @StatusLog=@StatusLog output
SELECT @retval, @RunStatus, @StatusLog

python

def run_job(jobname):
    conn = pymssql.connect(db_server, db_user, db_password, 'CM_RMB')
    cursor = conn.cursor()
    sql1 = (
        """
use master;
DECLARE @RunStatus int, @retval int, @StatusLog nvarchar(1024)
exec @retval=dbo.CustomSQLJobMonitoring @JobName=%s, @RunTimeout=3600, @RunStatus=@RunStatus output, @StatusLog=@StatusLog output
SELECT @retval, @RunStatus, @StatusLog
        """ % jobname
    )
    result = cursor.execute(sql1)
    rows = cursor.fetchall()
    print(rows)
    conn.close()


run_job('TestJob')