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

For Spanish version visit this link Scripts: Jobs no ejecutados en el último año / Actividad de Jobs Detallada
 
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]

About Fran Lens
SQL Server Analyst at Avanade Spain http://www.lensql.net

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: