Credits:
Author: ChillyDBA
Date: 23 Jul 2012
Description
Retrieves a list of all SQLAgent scheduled jobs, regardless of their type and active status.
The list is augmented with last run status, duration, message and notification data.
It requires the presence of the following functions:
** NOTE: This code was written for SQL 2005 and greater. There are slightly different views for obtaining data from SQL 2000, or for obtaining data in SQL 2005+ that has been subjected to an upgrade from SQL 2000. While upgrading maintenance plans generally works, I have always found it beneficial in the long term to recreate them from scratch. The feature set is better, and there are some tasks that are not 100% happy working across editions. I have therefore not included versions of the code that support SQL 2000 version maintenance plans.
Code
Stored Procedure:
DROP PROC usp_GetMaintPlanJobLastRunSummary GO
CREATE PROC usp_GetMaintPlanJobLastRunSummary AS
/**************************************************** Purpose: To list all SQL Maintenance Plan jobs scheduled using SQL Agent and the last run details if they exist. Utilised the following function(s) to retrieve results and/or to enhance readability: dbo.udf_GetMSDBJobList - gets a basic set of job details dbo.udf_IntToDateTime - converts the MSDB date and time integers into a datetime variable NOTE: This SP is basically the usp_GetMSDBJobLastRunSummary sp with an additional join via the sysmaintplan_subplans table to screen out all non-MaintPlan jobs. Author: ChillyDBA History: 23 July 2012 - Intial Issue *****************************************************/
;WITH LastRun AS ( SELECT jh.job_id AS Job_ID, MAX(jh.instance_id) AS Instance_ID FROM msdb.dbo.sysjobhistory jh (NOLOCK) WHERE jh.step_id = 0 --'(Job OutCome)' GROUP BY jh.job_id ) SELECT jobs.Job_ID AS Job_ID, jobs.JobName AS JobName, jobs.Category AS JobCategory, jobs.Enabled AS JobEnabled, jobs.Scheduled AS JobScheduled, jobs.Description AS JobDescription, jobs.Occurs AS JobOccurs, jobs.OccursDetail AS JobOccursDetail, jobs.Frequency AS JobFrequency, jobs.Duration AS JobDuration, jobs.JobValidUntil AS JobValidUntil, jh.sql_message_id AS JobSQLMessageID, jh.sql_severity AS JobSQLSeverity, jh.MESSAGE AS JobMessage, dbo.udf_IntToDateTime(jh.run_date, jh.run_time) AS JobLastRunDate, run_duration AS JobLastRunDuration, CASE jh.run_status WHEN 0 THEN 'Failed' WHEN 1 THEN 'Succeeded' WHEN 2 THEN 'Retry' WHEN 3 THEN 'Cancelled' END AS JobLastRunStatus, soe.name AS OperatorEmailed, son.name AS OperatorNetSent, sop.name AS OperatorPaged, jh.server AS ServerName FROM dbo.udf_GetMSDBJobList (NULL, 'A') jobs
-- NOTE: we can join directly to subplans as this is the first table in the maintplan storage -- that contains a link to SQLAgent jobs. This ensures only MaintPlan jobs are returned in the results INNER JOIN msdb.dbo.sysmaintplan_subplans sp ON jobs.job_id = sp.job_id LEFT OUTER JOIN LastRun lr ON jobs.job_id = lr.job_id LEFT OUTER JOIN msdb.dbo.sysjobhistory jh (NOLOCK) ON lr.job_id = jh.job_id AND lr.Instance_ID = jh.Instance_ID LEFT OUTER JOIN msdb..sysoperators soe ON jh.operator_id_emailed = soe.id LEFT OUTER JOIN msdb..sysoperators son ON jh.operator_id_netsent = son.id LEFT OUTER JOIN msdb..sysoperators sop ON jh.operator_id_paged = sop.id
|
 Updating...
Andy Hughes, Jul 23, 2012, 10:21 AM
|