Category: SQL Server

by Fran Lens 2012-02-18

Scripts: Jobs Not Executed in the Last Year / Job Activity Details

A few days ago i was looking for a script to let me know when was the last execution of a job, so I can review the jobs that were not executed in the last year and delete the ones not needed, but i couldn’t find anything valid, so i had to create one. 

I use the columns last_run_date and last_run_time from sysjobservers table to get this value, the scripts that i found on Internet used other columns from other tables which i will describe why i not chose: 

Script that used the columns run_date and run_time from sysjobhistory table, these columns show the last execution time of the job steps, this option is valid but i not chose it because its require to do an extra work to get the last step.

Script that use the column run_requested_date from sysjobactivity table, this column shows the last execution time of several sessions of the same job, this option is valid but i not chose it because its require to do an extra work to get the last session. 

This is the Script:

/*--------------------------------------------------------------------------------------
-- Title: Jobs Not Executed in the Last Year
-- Author: Fran Lens (http://www.lensql.net)
-- Date: 2012-02-15
-- Description: This query returns the jobs that are not executed in the last year
--------------------------------------------------------------------------------------*/
USE msdb
SELECT * FROM (
SELECT
[Job_Name] = j.name
,[Last_Run_Time] = CASE
WHEN js.last_run_date > 0 THEN substring(convert(varchar(8),js.last_run_date),1,4) +'-'+ substring(convert(varchar(8),js.last_run_date),5,2) +'-'+ substring(convert(varchar(8),js.last_run_date),7,2) +' '+
CASE len(js.last_run_time)
WHEN 6 THEN substring(convert(varchar(8),js.last_run_time),1,2) +':'+ substring(convert(varchar(8),js.last_run_time),3,2) +':'+ substring(convert(varchar(8),js.last_run_time),5,2)
WHEN 5 THEN +'0'+ substring(convert(varchar(8),js.last_run_time),1,1) +':'+ substring(convert(varchar(8),js.last_run_time),2,2) +':'+ substring(convert(varchar(8),js.last_run_time),4,2)
WHEN 4 THEN +'00:'+ substring(convert(varchar(8),js.last_run_time),1,2) +':'+ substring(convert(varchar(8),js.last_run_time),3,2)
WHEN 3 THEN +'00:0'+ substring(convert(varchar(8),js.last_run_time),1,1) +':'+ substring(convert(varchar(8),js.last_run_time),2,2)
WHEN 2 THEN +'00:00:'+ substring(convert(varchar(8),js.last_run_time),1,2)
WHEN 1 THEN +'00:00:0'+ substring(convert(varchar(8),js.last_run_time),1,1)
END
ELSE 'Never Executed'
END
,[Drop_Statement] = 'sp_delete_job @job_name = ' + '''' + j.name + ''''
FROM sysjobs j
INNER JOIN sysjobservers js on j.job_id = js.job_id
)as ttable
WHERE [Last_Run_Time] < (SELECT convert(varchar, getdate()-365, 121))
ORDER BY [Last_Run_Time]

After create this script, i decided to create another one that show all the activity details of the jobs, it looks like the Job Activity Monitor 

This is the other Script:

/*--------------------------------------------------------------------------------------
-- Title: Job Activity Details
-- Author: Fran Lens (http://www.lensql.net)
-- Date: 2012-02-15
-- Description: This query returns information about jobs executions, looks like the Job Activity Monitor
--------------------------------------------------------------------------------------*/
USE msdb
SELECT * FROM (
SELECT
[Job_Name] = j.name
,[Owner] = sp.name
,[Job_Status] = CASE
WHEN j.enabled = 1 THEN 'Enabled'
ELSE 'Disabled'
END
,[Last_Run_Status] = CASE js.last_run_outcome
WHEN 0 THEN 'Failed'
WHEN 1 THEN 'Succeeded'
WHEN 3 THEN 'Cancelled'
WHEN 4 THEN 'In Progress'
WHEN 5 THEN 'Unknown'
END
,[Last_Run_Time] = CASE
WHEN js.last_run_date > 0 THEN substring(convert(varchar(8),js.last_run_date),1,4) +'-'+ substring(convert(varchar(8),js.last_run_date),5,2) +'-'+ substring(convert(varchar(8),js.last_run_date),7,2) +' '+
CASE len(js.last_run_time)
WHEN 6 THEN substring(convert(varchar(8),js.last_run_time),1,2) +':'+ substring(convert(varchar(8),js.last_run_time),3,2) +':'+ substring(convert(varchar(8),js.last_run_time),5,2)
WHEN 5 THEN +'0'+ substring(convert(varchar(8),js.last_run_time),1,1) +':'+ substring(convert(varchar(8),js.last_run_time),2,2) +':'+ substring(convert(varchar(8),js.last_run_time),4,2)
WHEN 4 THEN +'00:'+ substring(convert(varchar(8),js.last_run_time),1,2) +':'+ substring(convert(varchar(8),js.last_run_time),3,2)
WHEN 3 THEN +'00:0'+ substring(convert(varchar(8),js.last_run_time),1,1) +':'+ substring(convert(varchar(8),js.last_run_time),2,2)
WHEN 2 THEN +'00:00:'+ substring(convert(varchar(8),js.last_run_time),1,2)
WHEN 1 THEN +'00:00:0'+ substring(convert(varchar(8),js.last_run_time),1,1)
END
ELSE 'Never Executed'
END
,[Next_Run_Time] = CASE
WHEN ja.next_scheduled_run_date > 0 THEN convert(varchar(19),ja.next_scheduled_run_date,121)
ELSE 'Not Scheduled'
END
,[Description] = j.description
,[Drop_Statement] = 'sp_delete_job @job_name = ' + '''' + j.name + ''''
FROM sysjobs j
INNER JOIN sysjobservers js on j.job_id = js.job_id
INNER JOIN sysjobactivity ja on j.job_id = ja.job_id
INNER JOIN sys.server_principals sp on j.owner_sid = sp.sid
WHERE ja.session_id = (SELECT MAX(session_id) from msdb.dbo.sysjobactivity)
)as ttable
ORDER BY [Last_Run_Time]