Archive
Archive for July 20, 2017
SQL DBA – Query to check Status of last running SQL Jobs
July 20, 2017
Leave a comment
To check the status of current running jobs you can use the “Job Activity Monitor” feature in SSMS, but sometimes opening and refreshing the tool takes time.
Here is a simple query you can run in SSMS or from any custom tool to get the status of current running jobs:
–> Query #1: This query uses sysjobs view to get the list of all jobs and sysjobhistory view to get the latest status record.
SELECT j.name AS JobName ,CONVERT(VARCHAR,DATEADD(S,(run_time/10000)*60*60 /* hours */ +((run_time - (run_time/10000) * 10000)/100) * 60 /* mins */ + (run_time - (run_time/100) * 100) /* secs */ ,CONVERT(DATETIME,RTRIM(run_date),113)),100) AS RunTimeStamp ,CASE WHEN j.enabled = 1 THEN 'Enabled' ELSE 'Disabled' END JobStatus ,CASE WHEN jh.run_status = 0 THEN 'Failed' WHEN jh.run_status = 1 THEN 'Succeeded' WHEN jh.run_status = 2 THEN 'Retry' WHEN jh.run_status = 3 THEN 'Cancelled' ELSE 'Unknown' END JobRunStatus FROM msdb.dbo.sysjobs j OUTER APPLY( SELECT TOP 1 * FROM msdb.dbo.sysjobhistory jh WHERE jh.job_id = j.job_id AND jh.step_id = 0 order by jh.instance_id DESC) jh where j.name like 'ADF%' ORDER BY j.name, jh.run_date, jh.run_time
–> Query #2; This query uses sysjobs view to get the list of all jobs and sysjobservers view to get the current status of job.
SELECT j.name AS JobName ,IIF(js.last_run_date > 0, DATETIMEFROMPARTS(js.last_run_date/10000, js.last_run_date/100%100, js.last_run_date%100, js.last_run_time/10000, js.last_run_time/100%100, js.last_run_time%100, 0), NULL) AS RunTimeStamp ,CASE WHEN j.enabled = 1 THEN 'Enabled' ELSE 'Disabled' END JobStatus ,CASE WHEN js.last_run_outcome = 0 THEN 'Failed' WHEN js.last_run_outcome = 1 THEN 'Succeeded' WHEN js.last_run_outcome = 2 THEN 'Retry' WHEN js.last_run_outcome = 3 THEN 'Cancelled' ELSE 'Unknown' END JobRunStatus FROM msdb.dbo.sysjobs j JOIN msdb.dbo.sysjobservers js on js.job_id = j.job_id where j.name like 'ADF%' ORDER BY j.name, js.last_run_date, js.last_run_time
And if you noted in both the queries above, I’ve used different way to calculate the RunTimeStamp, first by parsing the rum_time column, second by using DateTimeFromParts() function.
Advertisements