Im constantly needing to get a collection of information on a agent job, to look why its failed, or how long its expected to take, whats it schedule, etc etc.. after building out a collection of scripts either from the community or myself and having to open a different one for each task i finally decided to put it all in one place, so i can get all the information i might need in one output, using SQLCMD i simply pass in the 3 variables of Server, Agent Name and history (How many success’s and fails i want to see) and bingo, all my questions are answered.

/*
Query - Agent Job Information
-- Turn on SQLCMD

Agent Job Basic Information
Agent Schedule Basic Information
Agent Steps Information
Agent History Success
Agent History Failure

Original code from:
http://www.mssqltips.com/sqlservertip/2561/querying-sql-server-agent-job-information/
http://sqlbelle.com

*/
-- Server
:SETVAR Server "test"
-- Agent Job
:SETVAR AgentJob "test Agent job"
-- History
:SETVAR History 10

/* Queries */
:CONNECT $(Server)
-- Summary of Job
SELECT [sJOB].[name] AS [JobName] ,
CASE [sJOB].[enabled]
WHEN 1 THEN 'Yes'
WHEN 0 THEN 'No'
END AS [Enabled] ,
CASE WHEN [sSCH].[schedule_uid] IS NULL THEN 'No'
ELSE 'Yes'
END AS [Scheduled] ,
[sCAT].[name] AS [JobCategory] ,
[sJOB].[description] AS [JobDescription] ,
[sJOB].[date_modified] AS [LastModifiedDate]
FROM [msdb].[dbo].[sysjobs] AS [sJOB]
LEFT JOIN [msdb].[dbo].[syscategories] AS [sCAT] ON [sJOB].[category_id] = [sCAT].[category_id]
LEFT JOIN [msdb].[dbo].[sysjobschedules] AS [sJOBSCH] ON [sJOB].[job_id] = [sJOBSCH].[job_id]
LEFT JOIN [msdb].[dbo].[sysschedules] AS [sSCH] ON [sJOBSCH].[schedule_id] = [sSCH].[schedule_id]
WHERE sJOB.name = '$(AgentJob)';

-- Schedule
SELECT sSCH.name AS [ScheduleName] ,
sJOBSCH.next_run_date ,
sJOBSCH.next_run_time ,
sSCH.enabled ,
CASE sSCH.freq_type
WHEN 1 THEN 'One time Only'
WHEN 4 THEN 'Daily'
WHEN 8 THEN 'Weekly'
WHEN 16 THEN 'Monthly'
WHEN 32 THEN 'Monthly relative to Freq interval'
WHEN 64 THEN 'Runs when SQL Server Agent starts'
WHEN 128 THEN 'Runs when the computer is idle'
END AS [Frequency] ,
CASE sSCH.freq_interval
WHEN 1 THEN 'Once'
WHEN 4 THEN 'Daily'
WHEN 8 THEN 'Weekly'
WHEN 16 THEN 'Monthly'
WHEN 32 THEN 'Monthly relative to Freq interval'
WHEN 64 THEN 'Runs when SQL Server Agent starts'
WHEN 128 THEN 'Runs when the computer is idle'
END AS [Frequency Interval] ,
CASE sSCH.freq_subday_type
WHEN 1 THEN ''
WHEN 2 THEN 'Seconds'
WHEN 4 THEN 'Minutes'
WHEN 8 THEN 'Hours'
END AS [Frequency Sub Day Type] ,
sSCH.freq_subday_interval ,
CASE sSCH.freq_relative_interval
WHEN 0 THEN ''
WHEN 1 THEN 'First'
WHEN 2 THEN 'Second'
WHEN 4 THEN 'Third'
WHEN 8 THEN 'Fourth'
WHEN 16 THEN 'Last'
END AS [Frequency Relative Interval] ,
sSCH.freq_recurrence_factor ,
sSCH.date_modified
FROM [msdb].[dbo].[sysjobs] AS [sJOB]
LEFT JOIN [msdb].[dbo].[sysjobschedules] AS [sJOBSCH] ON [sJOB].[job_id] = [sJOBSCH].[job_id]
LEFT JOIN [msdb].[dbo].[sysschedules] AS [sSCH] ON [sJOBSCH].[schedule_id] = [sSCH].[schedule_id]
WHERE [sJOB].[name] = '$(AgentJob)';
GO

-- Step information
SELECT [sJSTP].[step_id] AS [StepNo] ,
[sJSTP].[step_name] AS [StepName] ,
CASE [sJSTP].[subsystem]
WHEN 'ActiveScripting' THEN 'ActiveX Script'
WHEN 'CmdExec' THEN 'Operating system (CmdExec)'
WHEN 'PowerShell' THEN 'PowerShell'
WHEN 'Distribution' THEN 'Replication Distributor'
WHEN 'Merge' THEN 'Replication Merge'
WHEN 'QueueReader' THEN 'Replication Queue Reader'
WHEN 'Snapshot' THEN 'Replication Snapshot'
WHEN 'LogReader' THEN 'Replication Transaction-Log Reader'
WHEN 'ANALYSISCOMMAND' THEN 'SQL Server Analysis Services Command'
WHEN 'ANALYSISQUERY' THEN 'SQL Server Analysis Services Query'
WHEN 'SSIS' THEN 'SQL Server Integration Services Package'
WHEN 'TSQL' THEN 'Transact-SQL script (T-SQL)'
ELSE sJSTP.subsystem
END AS [StepType] ,
[sPROX].[name] AS [RunAs] ,
[sJSTP].[database_name] AS [Database] ,
[sJSTP].[command] AS [ExecutableCommand] ,
CASE [sJSTP].[on_success_action]
WHEN 1 THEN 'Quit the job reporting success'
WHEN 2 THEN 'Quit the job reporting failure'
WHEN 3 THEN 'Go to the next step'
WHEN 4
THEN 'Go to Step: '
+ QUOTENAME(CAST([sJSTP].[on_success_step_id] AS VARCHAR(3)))
+ ' ' + [sOSSTP].[step_name]
END AS [OnSuccessAction] ,
[sJSTP].[retry_attempts] AS [RetryAttempts] ,
[sJSTP].[retry_interval] AS [RetryInterval (Minutes)] ,
CASE [sJSTP].[on_fail_action]
WHEN 1 THEN 'Quit the job reporting success'
WHEN 2 THEN 'Quit the job reporting failure'
WHEN 3 THEN 'Go to the next step'
WHEN 4
THEN 'Go to Step: '
+ QUOTENAME(CAST([sJSTP].[on_fail_step_id] AS VARCHAR(3)))
+ ' ' + [sOFSTP].[step_name]
END AS [OnFailureAction]
FROM [msdb].[dbo].[sysjobsteps] AS [sJSTP]
INNER JOIN [msdb].[dbo].[sysjobs] AS [sJOB] ON [sJSTP].[job_id] = [sJOB].[job_id]
LEFT JOIN [msdb].[dbo].[sysjobsteps] AS [sOSSTP] ON [sJSTP].[job_id] = [sOSSTP].[job_id]
AND [sJSTP].[on_success_step_id] = [sOSSTP].[step_id]
LEFT JOIN [msdb].[dbo].[sysjobsteps] AS [sOFSTP] ON [sJSTP].[job_id] = [sOFSTP].[job_id]
AND [sJSTP].[on_fail_step_id] = [sOFSTP].[step_id]
LEFT JOIN [msdb].[dbo].[sysproxies] AS [sPROX] ON [sJSTP].[proxy_id] = [sPROX].[proxy_id]
WHERE [sJOB].[name] = '$(AgentJob)'
ORDER BY [StepNo]
GO

-- Steps history SUCCESS
SELECT TOP $(History) j.name as JobName ,
h.step_id,
h.step_name as StepName ,
CONVERT(CHAR(10), CAST(STR(h.run_date, 8, 0) AS DATETIME), 111) RunDate ,
STUFF(STUFF(RIGHT('000000' + CAST (h.run_time AS VARCHAR(6)), 6), 5, 0,
':'), 3, 0, ':') RunTime ,
h.run_duration StepDuration ,
CASE h.run_status
WHEN 0 THEN 'failed'
WHEN 1 THEN 'Succeded'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Cancelled'
WHEN 4 THEN 'In Progress'
END AS ExecutionStatus ,
h.message MessageGenerated
FROM [msdb].[dbo].[sysjobhistory] h
INNER JOIN [msdb].[dbo].[sysjobs] j ON j.job_id = h.job_id
WHERE j.name = '$(AgentJob)'
AND h.run_status = 1
AND h.step_id 0
ORDER BY h.run_date DESC ,
h.run_time DESC
GO

-- Steps history FAILURE
SELECT TOP $(History) j.name JobName ,
h.step_id,
h.step_name StepName ,
CONVERT(CHAR(10), CAST(STR(h.run_date, 8, 0) AS DATETIME), 111) RunDate ,
STUFF(STUFF(RIGHT('000000' + CAST (h.run_time AS VARCHAR(6)), 6), 5, 0,
':'), 3, 0, ':') RunTime ,
h.run_duration StepDuration ,
CASE h.run_status
WHEN 0 THEN 'failed'
WHEN 1 THEN 'Succeded'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Cancelled'
WHEN 4 THEN 'In Progress'
END AS ExecutionStatus ,
h.message MessageGenerated
FROM [msdb].[dbo].[sysjobhistory] h
INNER JOIN [msdb].[dbo].[sysjobs] j ON j.job_id = h.job_id
WHERE j.name = '$(AgentJob)'
AND h.run_status = 0
--AND h.step_id 0
ORDER BY h.run_date DESC ,
h.run_time DESC
GO
Advertisements