Help finding the SQL agent job status messages
-
I am building a report in SSRS which will show the status of all the SQL agent jobs ran on a particular day. IMHO, the status messages created in the job history are not that useful. So, I would like to show the status message which can found by following the path given below:
Integration Services Catalog -> SSISDB -> Solution Name -> Projects -> Project Name -> Packages -> Right click Package Name-> Reports -> Standard Reports -> All Executions -> Click on All Messages
If you follow the exact path, we find out the exact error related to last unsuccessful execution of the job.
Can someone please tell me how to find it?
The message from [msdb].[dbo].[sysjobhistory] is not helpful for me.
-
SysJobHistory is where you want to look, but you'll probably need to pick up the detailed message from the job step that failed rather than the message on the job summary record (step 0).
Try something like this to pick up all the job step records for failed job runs:
WITH Run_History AS
(
SELECT
History.Job_ID,
History.Instance_ID,
Row_Number() OVER(PARTITION BY History.Job_ID ORDER BY History.Instance_ID) AS Run_Order,
msdb.dbo.agent_DateTime(History.run_date, History.run_time) AS Job_Start_Time,
Stuff(Stuff(Right('000000' + LTRIM(STR(History.run_duration)), 6),5,0,':'),3,0,':') AS Job_Run_Duration,
Run_Status
FROM
MSDB.dbo.SysJobHistory History
WHERE
Step_ID = 0
AND msdb.dbo.agent_DateTime(History.run_date, History.run_time) >= @Last_Run_Date
)
SELECT
Job_Run.job_id,
Job_Run.Instance_ID,
Job.Name AS Job_Name,
Job_Run.Job_Start_Time,
Job_Run.Job_Run_Duration,
Job_Run.Run_Status,
Step_History.Step_ID,
Step_History.Step_Name,
Step_History.Run_Status,
msdb.dbo.agent_DateTime(Step_History.run_date, Step_History.run_time) AS Step_Start_Time,
Stuff(Stuff(Right('000000' + LTRIM(STR(Step_History.run_duration)), 6),5,0,':'),3,0,':') AS Step_Run_Duration,
Step_History.SQL_Message_ID,
Step_History.SQL_Severity,
Step_History.Message
FROM
Run_History AS Job_Run
LEFT JOIN
Run_History AS Previous_Run
ON
Job_Run.Run_Order = Previous_Run.Run_Order + 1
AND Job_Run.Job_ID = Previous_Run.Job_ID
JOIN
MSDB.dbo.SysJobs Job
ON
Job.Job_ID = Job_Run.Job_ID
JOIN
MSDB.dbo.SysJobHistory Step_History
ON
Job.Job_ID = Step_History.Job_ID
AND Step_History.Instance_ID BETWEEN IsNull(Previous_Run.Instance_ID, 0) AND Job_Run.Instance_ID
AND Step_History.Step_ID <> 0
AND msdb.dbo.agent_DateTime(Step_History.run_date, Step_History.run_time) >= @Last_Run_Date
WHERE
Job_Run.Run_Status <> 1
ORDER BY
Step_History.Job_ID, Step_History.Instance_ID
-
USE [msdb]
GO
/****** Object: StoredProcedure [dbo].[SQL_Agent_Jobs_Details] Script Date: 3/24/2015 4:02:09 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SQL_Agent_Jobs_Details](@JobName varchar(5000)
,@LastRunDate nvarchar(20)
,@LastRunStatus varchar(50)
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @SQL VARCHAR(8000)
DECLARE @NewLastRunStatus INT
-- Insert statements for procedure here
SET @NewLastRunStatus = (SELECT CASE @LastRunStatus
WHEN 'Created' THEN 1
WHEN 'Running' THEN 2
WHEN 'Canceled' THEN 3
WHEN 'Failed' THEN 4
WHEN 'Pending' THEN 5
WHEN 'Ended unexpectedly' THEN 6
WHEN 'Succeeded' THEN 7
WHEN 'Stopping' THEN 8
ELSE 9
END )
SET @SQL = '
SELECT DISTINCT
E.folder_name
,E.project_name
,S.JobName
,E.package_name
,E.environment_name
--,E.execution_id
,E.executed_as_name
--,E.operation_type
--,E.process_id
, CASE e.[status]
WHEN 1 THEN ''Created''
WHEN 2 THEN ''Running''
WHEN 3 THEN ''Canceled''
WHEN 4 THEN ''Failed''
WHEN 5 THEN ''Pending''
WHEN 6 THEN ''Ended unexpectedly''
WHEN 7 THEN ''Succeeded''
WHEN 8 THEN ''Stopping''
ELSE ''Completed''
END [Status]
,E.start_time
,E.end_time
,E.server_name
,E.machine_name
,EM.event_name
,EM.message_source_name
,EM.message_time
,case EM.[message_type] WHEN 120 THEN ''Error'' else ''TaskFailed'' END [message_type]
,EM.[message]
,EM.execution_path
FROM [SSISDB].[catalog].[executions] E
LEFT OUTER JOIN
[SSISDB].[catalog].[event_messages] EM ON E.[execution_id] = EM.[operation_id]
LEFT JOIN
[MSDB].DBO.SQL_Agent_Jobs_Packages S
ON S.PackageName = E.package_name
WHERE EM.message_type in(120,130) AND 1=1
--AND E.package_name = ''Anna_Maria_Daily_Contacts_Parent.dtsx''
'
IF @JobName IS NOT NULL
BEGIN
SET @SQL = @SQL + ' AND S.JobName IN (' +''''+REPLACE(@JobName,',',''',''')+''')'
END
IF (cast(@LastRunDate as date) IS NOT NULL AND cast(@LastRunDate as date)!='1900-01-01')
BEGIN
SET @SQL = @SQL + ' AND CAST(isnull(EM.message_time, ''1900-01-01'') AS DATE) IN ('+''''+REPLACE(cast(@LastRunDate as date),',',''',''')+''')'
END
IF @LastRunStatus IS NOT NULL
BEGIN
SET @SQL = @SQL + ' AND e.[status] IN (' +''''+REPLACE(@NewLastRunStatus,',',''',''')+''')'
END
SET @SQL = @SQL + ' ORDER BY E.start_time DESC,E.end_time DESC'
--SELECT @SQL
EXEC (@SQL)
END
You can use the above script to create a dynamic sproc which takes the above mentioned params.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply