THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Allen White

Getting Job History Correctly

In my last blog post I walked through a way to grab the duration from the EnumHistory method of the JobServer/Job object. As I worked through getting a solution in place for a client I found that the duration calculation was only part of the problem.

First, when EnumHistory() is invoked, it runs the sp_help_jobhistory stored procedure. You can create a filter object which will supply the filters you want to the results, but I didn't do that. Given that it's invoked in my script for each job on a server, what I found, surprisingly, was that for each job it called the stored procedure, sending the results into a temporary table, and then selects from that temp table the rows where the job ID is that of the current job. So, it's returning the results for ALL jobs, every time, for each job. If you've got a lot of jobs on your server, that's a lot of data it's returning unnecessarily.

Another problem I found is when I want to get all history that occurred since a given point in time. The sp_help_jobhistory procedure has parameters to select start date and start time, so it would seem that populating those values would yield the desired results, right? No, it filters them as separate columns, and what you'll get are the history rows where the job was run on or since the specified date, but only those rows where the start time of the job occurred after the specified start time, on any qualifying date. So, if the start date is February 22, 2012 at 22:00, then a job that ran on February 27, 2012 at 17:00 would not appear in your result set.

(By the way, there's also parameters to the sp_help_jobhistory procedure for JobID and for JobName. Why the EnumHistory method didn't bother to use these is beyond me.)

In any event, to get the history that occurred since a point in time, in PowerShell invoke the get-date cmdlet, once for the date and once for the time, casting the time as an integer variable, like this (where $gatherdt contains the date/time for your starting point):

$strtdt = get-date $gatherdt -format yyyyMMdd
[int]$strttm = get-date $gatherdt -format HHmmss

Then you can call the sp_help_jobhistory procedure, like this:

$q = "exec msdb.dbo.sp_help_jobhistory @mode='FULL',@oldest_first=1,@start_run_date='$strtdt'"
$jobhist = invoke-sqlcmd -ServerInstance $inst -Database 'msdb' -query $q

You'll get history for all jobs, but only since 00:00 on the starting date. You can then test the results against the start time by ensuring that on the start date, you only process the rows received after the start time like this:

#Now extract from the job history the results of the jobs run since the last gather date
[int]$strtdt = get-date $gatherdt -format yyyyMMdd
$jobhist | foreach {
	# Filter data occurring before last gather date
	$ignore = $True
	$jdt = $_.run_date
	$jdtt = $_.run_time
	if (($jdt -gt $strtdt) -or (($jdt -eq $strtdt) -and ($jdtt -gt $strttm))) {
		$ignore = $False
		}

(I recast the start date as an integer because that's how the data is returned from the proc.) For each row I set the $ignore variable to $True, get the incoming date and time, then test to see that the date is greater than the start date or that the date is equal to the start date and the time is greater than the start time. If it passes those tests I set $ignore to $False, and that allows me to then process then incoming row.

Allen

Published Thursday, March 01, 2012 10:03 PM by AllenMWhite

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Jack Donnell said:

Here is a query to use for history. Has some bugs with getting some date history:

SELECT SERVERPROPERTY('ServerName') [ServerName/Instance]

,job_name

,CASE WHEN step_id = 0 then 'Total Run Time' else CAST(step_id as varchar(10))End  as step_id

,step_name

,datename(DW,run_datetime)StartDate

,run_datetime

,run_datetime + CASE WHEN run_duration = '00:00:0*' then '00:00:00' ELSE run_duration END [run_end_time]

,CASE WHEN run_duration = '00:00:0*' then '00:00:00' ELSE run_duration END run_duration

,RTRIM(SUBSTRING([Results], 1, 18)+'...') as [Results]

,Run_status

FROM

(

   SELECT job_name,step_id,step_name, run_datetime,

       SUBSTRING(run_duration, 1, 2) + ':' + SUBSTRING(run_duration, 3, 2) + ':' +

       SUBSTRING(run_duration, 5, 2) AS run_duration

       ,Run_status

       ,[Results]

   FROM

   (

       SELECT DISTINCT

           j.name as job_name, step_name,step_id,run_date as rd,

           run_datetime = CONVERT(DATETIME, RTRIM(run_date)) +  

               (run_time * 9 + run_time % 10000 * 6 + run_time % 100 * 10) / 216e4,

           run_duration = RIGHT('000000' + CONVERT(varchar(6), run_duration), 6)

           ,Run_status

           ,h.message [Results]

       FROM msdb..sysjobhistory h

       inner join msdb..sysjobs j

       on h.job_id = j.job_id

   ) t

) t

WHERE

--job_Name = 'MY IMPORTANT JOB'  and -- Specific Job name

--step_id <> '0' AND -- Show step completion times

step_id = 0  AND -- Show only the run times

--step_id = 1  AND -- Show specific step

run_datetime > CONVERT(varchar(10),GetDate()-1,121)  ---Current Day

-- run_datetime >'2012-02-13 02:40:00.000' and run_datetime<'2012-02-13 03:14:00.000' --Date Range

and run_status = 1 -- Successful(1), Failure(0)

ORDER BY  job_name, step_id, run_datetime;  

March 2, 2012 1:07 AM
 

Maulin Patel said:

Here is the script to get the JOB history information for all the JOBS ran on the specified date.

/*

This script will find the JOB History information for last 7 days only based on the specified DATE means if any JOB is running since more than 7 days will not be shown in a FINAL result

*/

if object_id('tempdb..#Step1')>0 Drop table #Step1

if object_id('tempdb..#Step2')>0 Drop table #Step2

Declare @sTime datetime,@eTime datetime

---- Specify here the DATE to find the JOB History and STATUS for all the JOBS ran on this DATE

Set @sTime='2014-01-11'  ----Specify the DATE format YYYY-MM-DD

select DISTINCT

  j.name as job_name,

  h.Step_name as Step_Name,

  h.[run_status] as [Status],

run_date1= convert(varchar(10),CONVERT(DATEtime, RTRIM(run_date)),121) ,

run_time1 = (CONVERT(datetime,RTRIM(run_date)) + Stuff(Stuff(RIGHT('000000' + CONVERT(varchar(6), run_time), 6),3,0,':'),6,0,':')),

  run_duration = RIGHT('000000' + CONVERT(varchar(6), run_duration), 6)

  Into #Step1

from msdb..sysjobhistory h

inner join msdb..sysjobs j

on h.job_id = j.job_id

where CONVERT(DATEtime, RTRIM(run_date))>=convert(varchar(10),@sTime,121) and h.step_id=0

Select job_name,Step_Name,[Status],run_date1,run_time1,run_duration,

Total_time=(DateAdd(HOUR,convert(int,substring(run_duration,1,2)),(DateAdd(minute,convert(int,SUBSTRING(run_duration,3,2)),(DATEADD(SECOND,convert(int,SUBSTRING(run_duration,5,2)),run_time1))))))

Into #Step2

from #Step1

Select job_name as Job_Name, Step_Name,

Case [Status] when 0 then 'Failed' else 'Success' end as [Status]

,datename(DW,run_time1)StartDate

,run_time1 as Start_Time,

Stuff(Stuff(RIGHT('000000' + CONVERT(varchar(6), run_duration), 6),3,0,':'),6,0,':') as Run_Duration, Total_time as End_Time  

from #Step2

where (run_time1 >= @sTime) OR (Total_time >= @sTime)

OR (run_time1<=@sTime)

order by Start_Time

Drop table #Step1

Drop table #Step2

January 11, 2014 12:20 PM

Leave a Comment

(required) 
(required) 
Submit

About AllenMWhite

Allen White is a consultant and mentor for Upsearch Technology Services in Northeast Ohio. He has worked as a Database Administrator, Architect and Developer for over 30 years, supporting both the Sybase and Microsoft SQL Server platforms over that period.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement