As many of you know I use DotNetNuke to monitor the operation of a rather complex Material Handling Application.  Part of the monitoring includes SQL Server Jobs.  The application has several jobs that routinely generate reports and collect and summerize data at shift changes. 

We have some other tools that monitor the health of the various servers and specifically the SQL Server application.  Those other tools will page me if they discover something out of the norm.  One issue I have had in recent months is the occasional page that a SQL Server Job had failed.   A review of the Job summary revealed no such failure. What?  So, here is where DNN and a little T-SQL helped.

SQL Server maintains the SQL Job History in tables within the MSDB database. So a rather straight forward Select can provide the all job step detail.

SELECT
  j.name as JobName,
  jh.step_name as StepName,
  CONVERT(CHAR(10), CAST(STR(jh.run_date,8, 0) AS dateTIME), 111) as RunDate,
  STUFF(STUFF(RIGHT('000000' + CAST ( jh.run_time AS VARCHAR(6 ) ) ,6),5,0,':'),3,0,':') as RunTime,
  jh.run_duration as StepDuration,
  CASE jh.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,
  jh.message as MessageGenerated
FROM
  sysjobhistory jh
  inner join sysjobs j ON j.job_id = jh.job_id
ORDER BY
  j.name,
  jh.run_date,
  jh.run_time

The above query is off interest because it uses a function I had never used before -- STUFF.  I does what the name implies, stuffs character into string and optionally removes character from the same point in the original string.  

The query results is two fields defining the event time, I prefer a single field.  This presents a rather harder challenge to create a combined date time with resonable formatting.  The effort is due to the source fields in the SYSJOBHISTORY table are integers. So here is somewhat different select with only a single event time field:

SELECT 
  CONVERT(varchar, 
  CAST (
    SUBSTRING(cast(jh.run_date as varchar(8)),1,4)
    + '/' + SUBSTRING(cast(jh.run_date as varchar(8)),5,2)
    + '/' + SUBSTRING(cast(jh.run_date as varchar(8)),7,2)
    + ' '
    + CASE len(rtrim(cast(jh.run_time as varchar)))
        when 1 then
            '00:00:0' + cast(jh.run_time as varchar)
        when 2 then
            '00:00:' + cast(jh.run_time as varchar)
        when 3 then
            '00:0' + substring(cast(jh.run_time as varchar),1,1)
           + ':' + substring(cast(jh.run_time as varchar),2,2)
     when 4 then
            '00:' + substring(cast(jh.run_time as varchar),1,2)
           + ':' + substring(cast(jh.run_time as varchar),3,2)
     when 5 then
           '0' + substring(cast(jh.run_time as varchar),1,1)
           + ':' + substring(cast(jh.run_time as varchar),2,2)
           + ':' + substring(cast(jh.run_time as varchar),4,2)
        else
           substring(cast(jh.run_time as varchar),1,2)
            + ':' + substring(cast(jh.run_time as varchar),3,2)
            + ':' + substring(cast(jh.run_time as varchar),5,2) 
      END as datetime), 20) as [Run date],
  jh.run_duration as [Duraton],
  j.name as [Job Name],
  jh.message as [Message], 
  jh.run_status as [sts]
FROM
  msdb.dbo.sysjobhistory jh with (nolock)
  join msdb.dbo.sysjobs j with (nolock) on jh.Job_id = j.Job_id
ORDER BY
  [run date] desc, 
    jh.step_id

The select will report all job steps in execution order, so if you have several jobs running at the same time, the step history may be intermixed.

Please Note: I am not the original author of these selects, I found them on the web some time ago and have modified them for my needs.  Applogies to the original authors as I did not record your names.

Using SGSV (SQLGridSelectedView), I added several filters to the display

1. A dropdown listing all the different job - so now I can narrow the select to only a specific job.  I selected the jobs using a filter select of
   
   select
     distinct j.name as jobname, j.Job_id as jobid
   from
     msdb.dbo.sysjobhistory jh with (nolock)
     join msdb.dbo.sysjobs j with (nolock) on jh.Job_id = j.Job_id
   Order by 
     jobname
   
   The filter clause is j.job_id = '[parm:jobid]'  this will add a where clause to select only one job, but there may be many one that one execution of the job reported.
    
2. A conditional filter to display only summary steps that adds the following condition jh.step_id = 0
    
3. A conditional filter to display only failed steps that adds the following condition jh.run_status = 0

The above make a nice addition to determine what has happen .. unfortantely I can not show you a screenshot for privcy reasons, so I leave this an exercise for the reader.

Back to the orginal reason for using DNN.  The page I received indicated that a job had failed, but I found no failed jobs -- so by executing the about and selecting only failed job-steps, I discovered I have one job-step that had failed -- the step was marked with continue as the step was not critial to the success of overall job, so the job did run to a "sucessful" conclusion, thus no failed jobs. 

So DNN was able to help me solve the mystery.  The nice part of this is I now have a repeatable select to perform whenever I encounter a failed SQL Server job.

Hope this helps,
Paul.