SQL Server - Getting job history details

Question

We use the below mentioned Stored Procedure to get the history of our JOBS

SELECT --sysjobhistory.server,
             sysjobs.name
             AS
             job_name,
             CASE sysjobhistory.run_status
               WHEN 0 THEN 'Failed'
               WHEN 1 THEN 'Succeeded'
               ELSE '???'
             END
             AS
             run_status,
             CAST(
             Isnull(Substring(CONVERT(VARCHAR(8), run_date), 1, 4) + '-' +
                           Substring(CONVERT(VARCHAR
                                     (8), run_date), 5, 2) + '-' +
                    Substring(CONVERT(VARCHAR(
                              8), run_date), 7, 2), '') AS DATETIME)
             AS
             [Run DATE],

             Isnull(Substring(CONVERT(VARCHAR(7), run_time+1000000), 2, 2) + ':'
                     +
                           Substring(CONVERT(VARCHAR(7), run_time+1000000), 4, 2
                            )
                    +
                    ':' +
                    Substring(CONVERT(VARCHAR(7), run_time+1000000), 6, 2), '') 
             AS
             [Run TIME],
             Isnull(Substring(CONVERT(VARCHAR(7), run_duration+1000000), 2, 2) +
                     ':' +
                           Substring(CONVERT(VARCHAR(7), run_duration+1000000),
                           4,
                           2)
                    + ':' +
                    Substring(CONVERT(VARCHAR(7), run_duration+1000000), 6, 2),
             ''
             ) AS
             [Duration],
             Isnull(Substring(CONVERT(VARCHAR(7), run_time+run_duration+1000000), 2, 2) + ':'
                     +
                           Substring(CONVERT(VARCHAR(7), run_time+run_duration+1000000), 4, 2
                            )
                    +
                    ':' +
                    Substring(CONVERT(VARCHAR(7), run_time+run_duration+1000000), 6, 2), '')
             AS
             [Total TIME],             
             sysjobhistory.step_id,
             sysjobhistory.step_name,
             sysjobhistory.MESSAGE AS Msg
      FROM   msdb.dbo.sysjobhistory
             INNER JOIN msdb.dbo.sysjobs
               ON msdb.dbo.sysjobhistory.job_id = msdb.dbo.sysjobs.job_id
      WHERE  sysjobhistory.run_date <= Datepart(yyyy, @dateparam) * 10000 +
                                             Datepart(mm, @dateparam) * 100 +
                                      Datepart
                                      (
                                             dd, @dateparam)
             AND sysjobs.name = @JobName --remove this line if you want to show all jobs for the specified day

---

But can anyone advise how to get error details if the job has failed for example (we are looking for this information which we can view thru log viewer)

Date        29/09/2011 07:57:04
Log     Job History Step ID     5
Server       
Job Name        
Step Name       extract
Duration        00:02:13
Sql Severity        0
Sql Message ID      0
Operator Emailed        
Operator Net sent       
Operator Paged      
Retries Attempted       0

Message (Need this error message also to come)

Answer 1

you can use the following script to grab the details of each step..first you have to get the failed job's job_id and pass to msdb.dbo.sp_help_jobhistory stored procedure.

I got this script by running trace while doing the activity on job activity monitor.

I hope this helps you on what you are looking for

To find STEP level messages, look at the message column where step_id <> 0

These records actually provide useful error messages.

declare @tmp_sp_help_jobhistory table
(
    instance_id int null, 
    job_id uniqueidentifier null, 
    job_name sysname null, 
    step_id int null, 
    step_name sysname null, 
    sql_message_id int null, 
    sql_severity int null, 
    message nvarchar(4000) null, 
    run_status int null, 
    run_date int null, 
    run_time int null, 
    run_duration int null, 
    operator_emailed sysname null, 
    operator_netsent sysname null, 
    operator_paged sysname null, 
    retries_attempted int null, 
    server sysname null  
)

insert into @tmp_sp_help_jobhistory 
exec msdb.dbo.sp_help_jobhistory 
    @job_id = '329cac18-328d-499b-9216-593244d164b0', --change this job_id according to your requirement
    @mode='FULL' 

SELECT
    tshj.instance_id AS [InstanceID],
    tshj.sql_message_id AS [SqlMessageID],
    tshj.message AS [Message],
    tshj.step_id AS [StepID],
    tshj.step_name AS [StepName],
    tshj.sql_severity AS [SqlSeverity],
    tshj.job_id AS [JobID],
    tshj.job_name AS [JobName],
    tshj.run_status AS [RunStatus],
    CASE tshj.run_date WHEN 0 THEN NULL ELSE
    convert(datetime, 
            stuff(stuff(cast(tshj.run_date as nchar(8)), 7, 0, '-'), 5, 0, '-') + N' ' + 
            stuff(stuff(substring(cast(1000000 + tshj.run_time as nchar(7)), 2, 6), 5, 0, ':'), 3, 0, ':'), 
            120) END AS [RunDate],
    tshj.run_duration AS [RunDuration],
    tshj.operator_emailed AS [OperatorEmailed],
    tshj.operator_netsent AS [OperatorNetsent],
    tshj.operator_paged AS [OperatorPaged],
    tshj.retries_attempted AS [RetriesAttempted],
    tshj.server AS [Server],
    getdate() as [CurrentDate]
FROM @tmp_sp_help_jobhistory as tshj
ORDER BY [InstanceID] ASC