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

Handle Duration Results from EnumHistory in PowerShell

Lately I've been very busy with client work, and one of my projects is to capture the history for SQL Agent jobs across all their servers so they can evaluate when to schedule jobs. (Yes, I know SQL Sentry has an excellent product for this, but right now there are budget constraints.) Anyway, I can do this in PowerShell with SMO.

Once you connect to a server in SMO, the JobServer object has a collection called Jobs. For each job in that collection, you can call the EnumHistory method and it will return both job and job step results, including duration.

On the Technet page describing the datatable returned when calling EnumHistory, it indicates that the Duration property is the duration of the job or step in seconds. Unfortunately this is blatantly incorrect.

Let's say a long running job returns a duration value of 93413. Well, converting seconds to hours, minutes and seconds returns 1.01:56:53 (one day, 1 hour, 56 minutes and 53 seconds). But if you look in the View History window for the job in SQL Server Agent you'll see that the job ran in 9 hours, 34 minutes and 13 seconds. Yes, the duration of 93413 translates to 9:34:13.

I'm not sure who made the decision to record the time this way, because parsing it back out so that it's useful is problematic. I need to get this done, so I wrote this PowerShell function for just that purpose.

# Function to turn the Duration from EnumHistory() bigint into useable time value
Function Convert-Duration {
param ([int]$sec)

#Now break it down into its pieces
if ($sec -gt 9999) {$hh = [int][Math]::Truncate($sec/10000); $sec = $sec - ($hh*10000)}
else {$hh = 0}
if ($sec -gt 99) {$mm = [int][Math]::Truncate($sec/100); $sec = $sec - ($mm*100)}
else {$mm = 0}

#Format and return the time value
$dur = ("{0:D2}" -f $hh) + ':' + "{0:D2}" -f $mm + ':' + "{0:D2}" -f $sec
$dur
}

The function takes an integer value as a parameter, and returns a string formatted to be useful as a time string, so this code:

$sec = 93413
$dur = Convert-Duration $sec
$dur

returns a result of 09:34:13, which I then use to pass to a stored procedure parameter defined as a datatype of TIME.

I had to add the

[Math]::Truncate
directive on the division because PowerShell rounds when doing integer division, and I needed it to just truncate fractions.

 

I hope that helps you, because with that function my job is much easier.

Allen

Published Friday, February 17, 2012 9:50 PM by AllenMWhite
Filed under: ,

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

 

ALZDBA said:

I think they will classify this as a legacy  documentation  flaw.

If I recall well, it has been like that already in SQL 7.0

(for sure in sql2000)

it's nice to see a valid powershell solution to convert it into useful valid data.

February 18, 2012 2:52 AM
 

AllenMWhite said:

In researching some issues with performance I've found that the sp_help_jobhistory system procedure returns the duration value in this format, so this function is useful if you're using that process as well.

February 20, 2012 8:52 AM
 

Allen White said:

In my last blog post I walked through a way to grab the duration from the EnumHistory method of the JobServer/Job

March 1, 2012 9:04 PM
 

Grifter said:

Nice one ! Ran accross the same issue, you saved me the time !

thx,

March 13, 2012 3:50 PM
 

Anant said:

Thanks heaps. you've helped me save a lot of time with this one...

November 13, 2013 6:12 PM
 

eccentricDBA said:

Thank you.  Just had the need for this an you were the first page I found when searched.

December 5, 2013 1:28 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