Brian K. McDonald SQLBIGeek Twitter: @briankmcdonald |
Welcome to the second post of my “SQLBIGeek’s Function Friday” blog series. In this series, I am hoping that I can provide some of the details about functions that I have either found on the internet or something that I had to create to meet a need. If it is something that I come across, I will attempt to make it shorter and/or perform better. If it is something I wrote, it is just something to share with you! J Either way, I hope that you enjoy the series and that you can get some useful ideas or functions to use in your environment.
|
A few weeks ago, I posted a blog about how SQL Server stores the duration of job execution in a strange format. I showed you one way to determine hours, minutes and seconds in a little bit easier to read format than what they provide. Here, is a function that you can use determine those values in seconds.
Script 1: Function
USE AdventureWorks
GO
CREATE FUNCTION dbo.ufn_JobIntToSeconds
(
@run_duration INT
/*=========================================================================
Created By: Brian K. McDonald, MCDBA, MCSD (www.SQLBIGeek.com)
Email: bmcdonald@SQLBIGeek.com
Twitter: @briankmcdonald
Date: 10/29/2010
Purpose: Convert the duration of a job to seconds
A value of 13210 would be 1 hour, 32 minutes and 10 seconds,
but I want to return this value in seconds. Which is 5530!
Then I can sum all of the values and to find total duration.
Usage: SELECT dbo.ufn_JobIntToSeconds (13210)
----------------------------------------------------------------------------
Modification History
----------------------------------------------------------------------------
==========================================================================*/
)
RETURNS INT
AS
BEGIN
RETURN
CASE
--hours, minutes and seconds
WHEN LEN(@run_duration) > 4 THEN CONVERT(VARCHAR(4),LEFT(@run_duration,LEN(@run_duration)-4)) * 3600
+ LEFT(RIGHT(@run_duration,4),2) * 60 + RIGHT(@run_duration,2)
--minutes and seconds
WHEN LEN(@run_duration) = 4 THEN LEFT(@run_duration,2) * 60 + RIGHT(@run_duration,2)
WHEN LEN(@run_duration) = 3 THEN LEFT(@run_duration,1) * 60 + RIGHT(@run_duration,2)
ELSE --only seconds
RIGHT(@run_duration,2)
END
END
GO
--Execute it a few times
SELECT dbo.ufn_JobIntToSeconds(125) --85
SELECT dbo.ufn_JobIntToSeconds(13210) --5530
SELECT dbo.ufn_JobIntToSeconds(2210) --1330
--Clean up if you want
DROP FUNCTION dbo.ufn_JobIntToSeconds
As a usable scenario, now you can query the sysjobhistory table and determine the duration of each step with a similar query to that shown in script 2.
Script 2: Query SysJobHistory
SELECT dbo.ufn_JobIntToSeconds(run_duration) AS [Seconds]
, dbo.ufn_JobIntToSeconds(run_duration)/60 AS [Minutes]
FROM msdb.dbo.sysjobhistory
Please be sure to return for the next BISQLGeek's Function Friday! I hope that you have enjoyed this post. If you did, please take just a moment to rate it below! I would love to hear your comments. Please let me know if you’ve ever had to overcome this kind of issue in your environment.
Also, if you don’t already, please be sure to follow me on twitter at @briankmcdonald. Also note that you can subscribe to an RSS feed of my blogs.
Brian K. McDonald, MCDBA, MCSD
Business Intelligence Consultant – Pragmatic Works
Email: bmcdonald@pragmaticworks.com | Blogs: SQLBIGeek | SQLServerCentral | BIDN
Twitter: @briankmcdonald | LinkedIn: http://tinyurl.com/BrianKMcDonald
