How to find that job is running?

  • For your viewing pleasures... here is another working sample tested on sql 8 - 10.50

    IF EXISTS ( SELECT * FROM tempdb..sysobjects WHERE id = OBJECT_ID(N'#tmp_enum_jobs') )

    DROP TABLE #tmp_enum_jobs;

    ELSE

    CREATE TABLE #tmp_enum_jobs (

    job_id UNIQUEIDENTIFIER NOT NULL

    , last_run_date NVARCHAR(20) NOT NULL

    , last_run_time NVARCHAR(20) NOT NULL

    , next_run_date NVARCHAR(20) NOT NULL

    , next_run_time NVARCHAR(20) NOT NULL

    , next_run_schedule_id INT NOT NULL

    , requested_to_run INT NOT NULL

    , request_source INT NOT NULL

    , request_source_id NVARCHAR(256) NULL

    , running INT NOT NULL

    , current_step INT NOT NULL

    , current_retry_attempt INT NOT NULL

    , job_state INT NOT NULL );

    DECLARE @job_owner NVARCHAR(256)

    DECLARE @is_sysadmin INT

    SET @is_sysadmin = ISNULL(IS_SRVROLEMEMBER('sysadmin'), 0)

    SET @job_owner = SUSER_SNAME()

    INSERT INTO #tmp_enum_jobs

    EXECUTE master.dbo.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner

    UPDATE #tmp_enum_jobs

    SET last_run_time = RIGHT('000000' + last_run_time, 6)

    , next_run_time = RIGHT('000000' + next_run_time, 6);

    SELECT @@SERVERNAME AS Server_Name

    , j.name AS Job_Name

    , j.enabled AS Enabled

    , CASE t.running

    WHEN 1 THEN 'Running'

    ELSE CASE h.run_status

    WHEN 2 THEN 'Inactive'

    WHEN 4 THEN 'Inactive'

    ELSE 'Completed'

    END

    END AS Current_Status

    , COALESCE(t.current_step, 0) AS Current_Step

    , CASE WHEN t.last_run_date > 0

    THEN CONVERT (DATETIME, SUBSTRING(t.last_run_date, 1, 4) + '-'

    + SUBSTRING(t.last_run_date, 5, 2) + '-'

    + SUBSTRING(t.last_run_date, 7, 2) + ' '

    + SUBSTRING(t.last_run_time, 1, 2) + ':'

    + SUBSTRING(t.last_run_time, 3, 2) + ':'

    + SUBSTRING(t.last_run_time, 5, 2) + '.000', 121)

    ELSE NULL

    END AS Last_Run_Time

    , CASE h.run_status

    WHEN 0 THEN 'Fail'

    WHEN 1 THEN 'Success'

    WHEN 2 THEN 'Retry'

    WHEN 3 THEN 'Cancel'

    WHEN 4 THEN 'In progress'

    END AS Last_Run_Outcome

    /*, CASE WHEN h.run_duration > 0

    THEN ( h.run_duration / 1000000 ) * ( 3600 * 24 )

    + ( h.run_duration / 10000 % 100 ) * 3600 + ( h.run_duration

    / 100 % 100 )

    * 60 + ( h.run_duration % 100 )

    ELSE NULL

    END AS Last_Run_Duration */

    , CASE WHEN LEN(h.run_duration) > 6 THEN STUFF(STUFF(h.run_duration,LEN(h.run_duration)-3,0,':'),LEN(h.run_duration),0,':')

    ELSE STUFF(STUFF(REPLACE(STR(h.run_duration,6,0),' ','0'),3,0,':'),6,0,':')

    END AS Last_Run_Duration

    FROM #tmp_enum_jobs t

    LEFT JOIN msdb.dbo.sysjobs j (NOLOCK) ON t.job_id = j.job_id

    LEFT OUTER JOIN msdb.dbo.syscategories c (NOLOCK) ON j.category_id = c.category_id

    LEFT OUTER JOIN msdb.dbo.sysjobhistory h (NOLOCK) ON t.job_id = h.job_id

    AND t.last_run_date = h.run_date

    AND t.last_run_time = h.run_time

    --AND h.step_id = 0

    WHERE t.running = 1

    DROP TABLE #tmp_enum_jobs;

    Cheers,

    ~Leon

Viewing post 16 (of 15 total)

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