Capture SQL Agent Job History

Aim: To capture SQL Agent job history to a table.

Following on from the previous blog post about adjusting the size of the SQL Agent job history log, the following scripts can be used to create a job that regularly captures the SQL Agent job history before it is lost.

First, create the table to store the job history data. I usually have a database for administration data like this – in this case it is called DBA.

DROP TABLE IF EXISTS [DBA].[dbo].[JobHistory];
GO
CREATE TABLE [DBA].[dbo].[JobHistory] (
    [Status] VARCHAR(11) NULL
    ,[Date] VARCHAR(19) NULL
    ,[Step ID] INT NULL
    ,[Server] NVARCHAR(128) NULL
    ,[Job Name] NVARCHAR(128) NULL
    ,[Step Name] NVARCHAR(128) NULL
    ,[Message] NVARCHAR(4000) NULL
    ,[Duration] INT NULL
    ,[SQL Severity] INT NULL
    ,[SQL Message ID] INT NULL
    ,[Operator Emailed] NVARCHAR(128) NULL
    ,[Operator Net sent] NVARCHAR(128) NULL
    ,[Operator Paged] NVARCHAR(128) NULL
    ,[Retries Attempted] INT NULL
    ,[Date_BIGINT] BIGINT NULL
);

Then create a SQL Agent job with the following step:

DECLARE @MaxDate_BIGINT BIGINT;

SET @MaxDate_BIGINT = (
    SELECT ISNULL(MAX([Date_BIGINT]), 0)
    FROM [DBA].[dbo].[JobHistory]
);

WITH JobHistory AS (
    SELECT H.[run_status]
        ,CAST(H.[run_date] AS VARCHAR(8)) AS [run_date]
        ,CAST(H.[run_time] AS VARCHAR(6)) AS [run_time]
        ,H.[step_id] AS [Step ID]
        ,H.[server] AS [Server]
        ,J.[name] AS [Job Name]
        ,H.[step_name] AS [Step Name]
        ,H.[message] AS [Message]
        ,H.[run_duration] AS [Duration]
        ,H.[sql_severity] AS [SQL Severity]
        ,H.[sql_message_id] AS [SQL Message ID]
        ,E.[name] AS [Operator Emailed]
        ,N.[name] AS [Operator Net sent]
        ,P.[name] AS [Operator Paged]
        ,H.[retries_attempted] AS [Retries Attempted]
        ,CAST((CAST(H.[run_date] AS VARCHAR(8)) + CAST(H.[run_time] AS VARCHAR(6))) AS BIGINT) AS [Date_BIGINT]
    FROM [msdb].[dbo].[sysjobs] J
    INNER JOIN [msdb].[dbo].[sysjobhistory] H
    ON J.[job_id] = H.[job_id]
    LEFT OUTER JOIN [msdb].[dbo].[sysoperators] E
    ON H.[operator_id_emailed] = E.[id]
    LEFT OUTER JOIN [msdb].[dbo].[sysoperators] N
    ON H.[operator_id_netsent] = N.[id]
    LEFT OUTER JOIN [msdb].[dbo].[sysoperators] P
    ON H.[operator_id_paged] = P.[id]
)
INSERT INTO [DBA].[dbo].[JobHistory] (
    [Status]
    ,[Date]
    ,[Step ID]
    ,[Server]
    ,[Job Name]
    ,[Step Name]
    ,[Message]
    ,[Duration]
    ,[SQL Severity]
    ,[SQL Message ID]
    ,[Operator Emailed]
    ,[Operator Net sent]
    ,[Operator Paged]
    ,[Retries Attempted]
    ,[Date_BIGINT]
)
SELECT CASE [run_status]
        WHEN 0 THEN 'Failed'
        WHEN 1 THEN 'Succeeded'
        WHEN 2 THEN 'Retry'
        WHEN 3 THEN 'Canceled'
        WHEN 4 THEN 'In Progress'
        ELSE 'Unknown'
    END AS [Status]
    ,RIGHT([run_date], 2) + '/'
         + SUBSTRING([run_date], 5, 2) + '/'
         + LEFT([run_date], 4) + ' '
         + LEFT([run_time], 2) + ':'
         + SUBSTRING([run_time], 3, 2) + ':'
         + RIGHT([run_time], 2)
    AS [Date]
    ,[Step ID]
    ,[Server]
    ,[Job Name]
    ,[Step Name]
    ,[Message]
    ,[Duration]
    ,[SQL Severity]
    ,[SQL Message ID]
    ,[Operator Emailed]
    ,[Operator Net sent]
    ,[Operator Paged]
    ,[Retries Attempted]
    ,[Date_BIGINT]
FROM JobHistory
WHERE [Date_BIGINT] > @MaxDate_BIGINT;

Set a suitable schedule for the job, e.g. a daily schedule is appropriate if the oldest data in the log is removed after 1-2 days. Once enabled, the job will capture the data from the SQL Agent log before it is lost.

To prevent the table from growing too large, it will likely be necessary to clear the data from the table by truncating it or deleting the older data once it is no longer of interest, or to set up a job to clear the data regularly.