Archiving Job History Table Data

Why do you care about job run times and why do you need to archive it? Job run times are very important piece of information used for measuring the performance of a database. There are other variables used for measuring the performance as well and job run times is one I use frequently to see if any jobs are taking longer.

Most database management systems today store the job run time in their system tables or views. With SQL Server, you can only go back to 15 days. Similarly, ORACLE and other DBMS have some cut off on how long to store it. Sometime two weeks worth of data isn't enough to find a trend. Will you see your job in a history table that runs monthly or quarterly? No, you don't!

How are you going to store the run time statistics beyond two weeks?
Job that runs monthly or quarterly may not be in a job history table, therefore I would like to write my own custom process to store stats past 15 days. In order to get a solid result, I would like to keep it for last 6 months. Keeping 6 months old history is enough to capture daily, monthly and quarterly jobs.

With this blog, I will show step-by-step instructions on how to store last six month's worth of job history data.

• Create a Table
• Create a Procedure
• Schedule a Job
• Analyze Data

First, you will need a table to store all the detail about the job run times. You will at least need to record  job name, job run date, job run times in seconds and in minutes.
Let's being by creating a table.

Create Table

USE [Your DB]
GO
/****** Object:  Table [dbo].[job_run_times]    Script Date: 12/01/2014 09:43:34 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[job_run_times](
 [name] [sysname] NOT NULL,
 [run_status] [int] NOT NULL,
 [durationHHMMSS] [varchar](8000) NULL,
 [start_date] [datetime] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO

Create Procedure: 
 This procedure does two things: Inserts job details to a table and then purge data older then 6 months.

CREATE PROCEDURE sp_job_run_times 
AS 
    -- Inserting run time STATS of all the jobs to job_run_times table 
    INSERT INTO [Your DB].dbo.job_run_times 
    SELECT j.NAME, 
           h.run_status, 
           durationHHMMSS = Stuff(Stuff(Replace(Str(h.run_duration, 7, 0), ' ', 
                                        '0') 
                                  , 4, 0, 
                                  ':'), 7, 0, ':'), 
           [start_date] = CONVERT(DATETIME, 
                          Rtrim(run_date) + ' ' 
                          + Stuff(Stuff(Replace(Str(Rtrim(h.run_time), 6, 0), 
                          ' ', 
                          '0'), 
                                         3, 0, ':'), 6, 0, ':')) 
    FROM   msdb.dbo.sysjobs AS j 
           INNER JOIN (SELECT job_id, 
                              instance_id = Max(instance_id) 
                       FROM   msdb.dbo.sysjobhistory 
                       GROUP  BY job_id) AS l 
                   ON j.job_id = l.job_id 
           INNER JOIN msdb.dbo.sysjobhistory AS h 
                   ON h.job_id = l.job_id 
                      AND h.instance_id = l.instance_id 
    ORDER  BY CONVERT(INT, h.run_duration) DESC, 
              [start_date] DESC 

    --Purging data older than six month if any 
    DECLARE @Retention_Date DATETIME = Dateadd(mm, -6, Getdate()) 
    DELETE FROM [Your DB].[dbo].[job_run_times] 
    WHERE  start_date < @Retention_Date; 
GO 

Schedule a Job:
Now, you are required to schedule a job to run the procedure we created. This job should be schedule to run once daily. The job should execute the procedure as shown below.

USE [Your-DB]
GO
DECLARE @RC int
EXECUTE @RC = [dbo].[sp_job_run_times] 
GO

How to Analyzed Data?
We have a job that runs daily to collected the stats about the job. Now, we need to analyze the data we collected to see if there’s a downward of upward pattern? If you see the run times going up, then that is a red flag. You will need to dive into the detail about the job to find out why it is taking longer each day, week or month?
The query below will give you the average time taken in seconds and in minutes for each jobs. You also have the flexibility to run for a specific time frame. I will like to run the query for each month interval and look for pattern.

WITH MY_CTE(name, run_status, durationhhmmss, start_date)
  AS (SELECT name,
             run_status,
              durationhhmmss,
             start_date
      FROM   [Periop-Stage].dbo.job_run_times
      WHERE  start_date BETWEEN '20141114' AND '20141121')
          -- Add your own date range here 
SELECT name,
    Avg(( Cast(Substring(Replace(durationhhmmss, ':', ''), 1, 3) AS INT) * 60
          * 60 +
          Cast
                (Substring(Replace(durationhhmmss, ':', ''), 4, 2) AS INT) *
          60 +
          Cast (
               Substring(Replace(durationhhmmss, ':', ''), 6, 2) AS INT) )) AS
     avg_seconds,
    Avg(( Cast(Substring(Replace(durationhhmmss, ':', ''), 1, 3) AS INT) * 60
          * 60 +
              Cast
                (Substring(Replace(durationhhmmss, ':', ''), 4, 2) AS INT) *
              60 +
          Cast (
               Substring(Replace(durationhhmmss, ':', ''), 6, 2) AS INT) ) /
        60)
    avg_min
FROM   MY_CTE
GROUP  BY name;

What if you are to analyze with whatever stats you have in job history table? If you are OK with last 15 days worth of data, then the query below will show you the total run time for each jobs in minutes and seconds. SQL Server Job history table stores run duration in "hhmmss" format, which I converted to total seconds and in total minutes.Try it out!

SELECT DISTINCT( curr.job_id ), 
               curr.NAME, 
               history.run_date, 
               date_executed=CONVERT(DATETIME, CONVERT(VARCHAR(8), run_date)) 
                             + ' ' 
                             + Stuff(Stuff(RIGHT(1000000+run_time, 6), 3, 0, ':' 
                             ), 6, 0, ':'), 
               secs_duration=run_duration / 10000 * 3600 + 
                             run_duration%10000 / 100 * 60 + 
                             run_duration%100, 
               mins_durations =( ( run_duration / 10000 * 3600 + 
                                   run_duration%10000 / 100 * 60 
                                   + 
                                   run_duration%100 
                                 ) / 60 ) 
FROM   msdb.dbo.sysjobhistory history 
       LEFT JOIN msdb.dbo.sysjobs curr 
              ON curr.job_id = history.job_id 
WHERE  history.step_id = 0 --Job Outcome 
       AND history.run_status = 1 
ORDER  BY curr.NAME, 
          history.run_date;