Automatic Data Collection about Completed Tasks in MS SQL Server

Total: 1 Average: 5

Introduction

It is important for a database administrator to know what tasks and how have they been completed. To simplify this process, it is better to automate it, rather than perform it manually.

In this article, I will analyze on a particular example how to automatically collect data about completed tasks of the SQL Server Agent.

Solution

Algorithm:

  1. Create an instance to select tasks:
    USE [DATABASE_NAME]
    GO
    
    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    CREATE view [srv].[vJobRunShortInfo] as
    SELECT sj.[job_id] as Job_GUID
          ,j.name as Job_Name
          ,case sj.[last_run_outcome]
            when 0 then 'Error'
            when 1 then 'Successful'
            when 3 then 'Canceled'
            else case when sj.[last_run_date] is not null and len(sj.[last_run_date])=8 then 'Inconsistent state'
                    else NULL
                    end
           end as LastFinishRunState
          ,sj.[last_run_outcome] as LastRunOutcome
          ,case when sj.[last_run_date] is not null and len(sj.[last_run_date])=8 then
            DATETIMEFROMPARTS(
            substring(cast(sj.[last_run_date] as nvarchar(255)),1,4),
            substring(cast(sj.[last_run_date] as nvarchar(255)),5,2),
            substring(cast(sj.[last_run_date] as nvarchar(255)),7,2),
            case when len(cast(sj.[last_run_time] as nvarchar(255)))>=5 
            then substring(cast(sj.[last_run_time] as nvarchar(255)),1,len(cast(sj.[last_run_time] as nvarchar(255)))-4)
              else 0
              end,
            case when len(right(cast(sj.[last_run_time] as nvarchar(255)),4))>=4 
            then substring(right(cast(sj.[last_run_time] as nvarchar(255)),4),1,2)
            when len(right(cast(sj.[last_run_time] as nvarchar(255)),4))=3  
            then substring(right(cast(sj.[last_run_time] as nvarchar(255)),4),1,1)
               else 0
               end,
            right(cast(sj.[last_run_duration] as nvarchar(255)),2),
                                0
                            )
           else NULL
           end as LastDateTime
           ,case when len(cast(sj.[last_run_duration] as nvarchar(255)))>5 
           then substring(cast(sj.[last_run_duration] as nvarchar(255)),1,len(cast(sj.[last_run_duration] as nvarchar(255)))-4)
                when len(cast(sj.[last_run_duration] as nvarchar(255)))=5 
           then '0'+substring(cast(sj.[last_run_duration] as nvarchar(255)),1,len(cast(sj.[last_run_duration] as nvarchar(255)))-4)
                else '00'
           end
           +':'
           +case when len(cast(sj.[last_run_duration] as nvarchar(255)))>=4 then substring(right(cast(sj.[last_run_duration] as nvarchar(255)),4),1,2)
                 when len(cast(sj.[last_run_duration] as nvarchar(255)))=3  then '0'+substring(right(cast(sj.[last_run_duration] as nvarchar(255)),4),1,1)
                 else '00'
           end
           +':'
           +case when len(cast(sj.[last_run_duration] as nvarchar(255)))>=2
            then substring(right(cast(sj.[last_run_duration] as nvarchar(255)),2),1,2)
                 when len(cast(sj.[last_run_duration] as nvarchar(255)))=2  
            then '0'+substring(right(cast(sj.[last_run_duration] as nvarchar(255)),2),1,1)
                 else '00'
           end as [LastRunDurationString]
          ,sj.last_run_duration as LastRunDurationInt
          ,sj.[last_outcome_message] as LastOutcomeMessage
          ,j.enabled as [Enabled]
      FROM [msdb].[dbo].[sysjobservers] as sj
      inner join msdb.dbo.sysjobs_view as j on j.job_id=sj.job_id;
    
    GO

    To do this, use the sysjobservers and sysjobs_view instances.

  2. Create a table to store selected data:
    USE [DATABASE_NAME]
    GO
    
    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    CREATE TABLE [srv].[ShortInfoRunJobs](
        [Job_GUID] [uniqueidentifier] NOT NULL,
        [Job_Name] [nvarchar](255) NOT NULL,
        [LastFinishRunState] [nvarchar](255) NULL,
        [LastDateTime] [datetime] NOT NULL,
        [LastRunDurationString] [nvarchar](255) NULL,
        [LastRunDurationInt] [int] NULL,
        [LastOutcomeMessage] [nvarchar](255) NULL,
        [LastRunOutcome] [tinyint] NOT NULL,
        [Server] [nvarchar](255) NOT NULL,
        [InsertUTCDate] [datetime] NOT NULL,
        [ID] [int] IDENTITY(1,1) NOT NULL,
     CONSTRAINT [PK_ShortInfoRunJobs] PRIMARY KEY CLUSTERED 
    (
        [ID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO
    
    ALTER TABLE [srv].[ShortInfoRunJobs] ADD  CONSTRAINT [DF_ShortInfoRunJobs_InsertUTCDate]  DEFAULT (getutcdate()) FOR [InsertUTCDate]
    GO
  3. Create a task in the SQL Server Agent and get information about those tasks that either have been executed for a long time (more than 30 seconds) or failed to be completed. You need to collect this information for the last two days:
    USE [DATABASE_NAME];
    GO
    
    truncate table [srv].[ShortInfoRunJobs];
    
    INSERT INTO [srv].[ShortInfoRunJobs]
               ([Job_GUID]
               ,[Job_Name]
               ,[LastFinishRunState]
               ,[LastDateTime]
               ,[LastRunDurationString]
               ,[LastRunDurationInt]
               ,[LastOutcomeMessage]
               ,[LastRunOutcome]
               ,[Server])
        SELECT [Job_GUID]
              ,[Job_Name]
              ,[LastFinishRunState]
              ,[LastDateTime]
              ,[LastRunDurationString]
              ,[LastRunDurationInt]
              ,[LastOutcomeMessage]
              ,LastRunOutcome
              ,@@SERVERNAME
          FROM [srv].[vJobRunShortInfo]
          where [Enabled]=1
          and ([LastRunOutcome]=0
          or [LastRunDurationInt]>=30)
          and LastDateTime>=DateAdd(day,-2,getdate());
    GO

    Here you can set a filter to remove all unnecessary tasks. For example, those tasks that refer to replication as it takes much more time to be completed.

Generate an HTML-report to send it to the administrators’ email:

USE [DATABASE_NAME]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [srv].[GetHTMLTableShortInfoRunJobs]
    @body nvarchar(max) OUTPUT
AS
BEGIN
    /*
        generates an HTML-code for the tables of completed tasks
    */
    SET NOCOUNT ON;
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

    declare @tbl table (
                        Job_GUID                uniqueidentifier
                        ,Job_Name               nvarchar(255)
                        ,LastFinishRunState     nvarchar(255)
                        ,LastDateTime           datetime
                        ,LastRunDurationString  nvarchar(255)
                        ,LastOutcomeMessage     nvarchar(max)
                        ,[Server]               nvarchar(255)
                        ,ID                     int identity(1,1)
                       );

    declare
    @Job_GUID               uniqueidentifier
    ,@Job_Name              nvarchar(255)
    ,@LastFinishRunState    nvarchar(255)
    ,@LastDateTime          datetime
    ,@LastRunDurationString nvarchar(255)
    ,@LastOutcomeMessage    nvarchar(max)
    ,@Server                nvarchar(255)
    ,@ID                    int;

    insert into @tbl(
                        Job_GUID
                        ,Job_Name
                        ,LastFinishRunState
                        ,LastDateTime
                        ,LastRunDurationString
                        ,LastOutcomeMessage
                        ,[Server]
                    )
            select      Job_GUID
                        ,Job_Name
                        ,LastFinishRunState
                        ,LastDateTime
                        ,LastRunDurationString
                        ,LastOutcomeMessage
                        ,[Server]
            from    srv.ShortInfoRunJobs
            --order by LastRunDurationInt desc;

    if(exists(select top(1) 1 from @tbl))
    begin
        set @body='When analyzing these tasks execution, I have found out the tasks that either have failed with an error,
        or, it has taken more than 30 seconds for their execution :<br><br>'+'<TABLE BORDER=5>';

        set @body=@body+'<TR>';

        set @body=@body+'<TD>';
        set @body=@body+'№ p/p';
        set @body=@body+'</TD>';

        set @body=@body+'<TD>';
        set @body=@body+'GUID';
        set @body=@body+'</TD>';

        set @body=@body+'<TD>';
        set @body=@body+'TASK';
        set @body=@body+'</TD>';

        set @body=@body+'<TD>';
        set @body=@body+'STATUS';
        set @body=@body+'</TD>';

        set @body=@body+'<TD>';
        set @body=@body+'DATE AND TIME';
        set @body=@body+'</TD>';

        set @body=@body+'<TD>';
        set @body=@body+'DURATION';
        set @body=@body+'</TD>';

        set @body=@body+'<TD>';
        set @body=@body+'MESSAGE';
        set @body=@body+'</TD>';

        set @body=@body+'<TD>';
        set @body=@body+'SERVER';
        set @body=@body+'</TD>';

        set @body=@body+'</TR>';

        while((select top 1 1 from @tbl)>0)
        begin
            set @body=@body+'<TR>';

            select top 1
            @ID                     =   [ID]
            ,@Job_GUID              =   Job_GUID
            ,@Job_Name              =   Job_Name                
            ,@LastFinishRunState    =   LastFinishRunState      
            ,@LastDateTime          =   LastDateTime            
            ,@LastRunDurationString =   LastRunDurationString   
            ,@LastOutcomeMessage    =   LastOutcomeMessage      
            ,@Server                =   [Server]                
            from @tbl
            order by LastRunDurationInt desc;

            set @body=@body+'<TD>';
            set @body=@body+cast(@ID as nvarchar(max));
            set @body=@body+'</TD>';

            set @body=@body+'<TD>';
            set @body=@body+cast(@Job_GUID as nvarchar(255));
            set @body=@body+'</TD>';

            set @body=@body+'<TD>';
            set @body=@body+coalesce(@Job_Name,'');
            set @body=@body+'</TD>';

            set @body=@body+'<TD>';
            set @body=@body+coalesce(@LastFinishRunState,'');
            set @body=@body+'</TD>';

            set @body=@body+'<TD>';
            set @body=@body+rep.GetDateFormat(@LastDateTime, default)+' '+rep.GetTimeFormat(@LastDateTime, default);--cast(@InsertDate as nvarchar(max));
            set @body=@body+'</TD>';

            set @body=@body+'<TD>';
            set @body=@body+coalesce(@LastRunDurationString,'');
            set @body=@body+'</TD>';

            set @body=@body+'<TD>';
            set @body=@body+coalesce(@LastOutcomeMessage, '');
            set @body=@body+'</TD>';

            set @body=@body+'<TD>';
            set @body=@body+coalesce(@Server, '');
            set @body=@body+'</TD>';

            delete from @tbl
            where ID=@ID;

            set @body=@body+'</TR>';
        end

        set @body=@body+'</TABLE>';
    end
    else
    begin
        set @body='The tasks, that have failed with an error or that have been executed for more than 30 seconds, have not been found';
    end

    set @body=@body+'<br><br>For the detailed information, please refer to the table DATABASE_NAME.srv.ShortInfoRunJobs';
END

GO

This stored procedure generates an HTML-report about completed tasks that have been executed for 30 seconds or that failed to be completed.

Result

In this article, I have explored on a particular example the implementation of a daily automatic data collection about completed tasks in the SQL Server Agent. This information helps determine tasks that have been executed for a long time or completed with an error. It allows an administrator to take measures to avoid such mistakes in future. For example, it is possible to make the task run faster or set the maximum time for the specified task.

This solution also helps monitor issues related to backups. Still, we will discuss it later, as it is not enough to notify about important tasks once a day. It is necessary to email about them immediately and regularly until the error is fixed.

If you need to select data from several servers, then it is possible to combine results and send them `via one email.

References:

» sysjobs
» sysjobservers

Further Reading:

Automatic Data Collection of Database Schema Changes in MS SQL Server

Automatic Data Collection: Database Files and Logical Drives in MS SQL Server

Configuring Database Mail Notifications in MS SQL Server

 

Evgeniy Gribkov