Pull to refresh

Автосбор данных о выполненных заданиях в MS SQL Server

Reading time 6 min
Views 8.5K

Предисловие


Администратору баз данных важно знать, какие задачи выполнялись и каким образом это происходило (по длительности, успешно или не успешно и т. д.). Чтобы этого не делать вручную на каждом сервере, лучше данный процесс автоматизировать.


В данной статье приведу реализацию автоматического ежедневного сбора информации о выполненных заданиях Агента в MS SQL Server.



Решение


Алгоритм:


1) создать представление для отбора заданий:


Код
USE [ИМЯ_БАЗЫ_ДАННЫХ]
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 'Ошибка'
        when 1 then 'Успешно'
        when 3 then 'Отменено'
        else case when sj.[last_run_date] is not null and len(sj.[last_run_date])=8 then 'Неопределенное состояние'
                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

Здесь используются два системных представления sysjobservers и sysjobs_view
2) создать таблицу для хранения отобранной информации:


Код
USE [ИМЯ_БАЗЫ_ДАННЫХ]
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) создать в Агенте задачу и ежедневно собирать информацию о тех задачах, которые либо долго выполнялись (больше 30 сек.), либо завершились неудачно за последние 2 дня:


Код
USE [ИМЯ_БАЗЫ_ДАННЫХ];
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

Здесь же или в п.2 можно настроить фильтр, чтобы убрать ненужные задания. Например, связанные с репликацией, т. к. они работают долго
4) сформировать HTML-отчет для дальнейшей отправки на почту администраторам о результатах:


Код
USE [ИМЯ_БАЗЫ_ДАННЫХ]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [srv].[GetHTMLTableShortInfoRunJobs]
    @body nvarchar(max) OUTPUT
AS
BEGIN
    /*
        формирует HTML-код для таблицы выполненных заданий
    */
    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='В ходе анализа последних выполнений заданий, были выявлены следующие задания, которые либо с ошибочным завершением, либо выполнились по времени более 30 секунд:<br><br>'+'<TABLE BORDER=5>';

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

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

        set @body=@body+'<TD>';
        set @body=@body+'ГУИД';
        set @body=@body+'</TD>';

        set @body=@body+'<TD>';
        set @body=@body+'ЗАДАНИЕ';
        set @body=@body+'</TD>';

        set @body=@body+'<TD>';
        set @body=@body+'СТАТУС';
        set @body=@body+'</TD>';

        set @body=@body+'<TD>';
        set @body=@body+'ДАТА И ВРЕМЯ';
        set @body=@body+'</TD>';

        set @body=@body+'<TD>';
        set @body=@body+'ДЛИТЕЛЬНОСТЬ';
        set @body=@body+'</TD>';

        set @body=@body+'<TD>';
        set @body=@body+'СООБЩЕНИЕ';
        set @body=@body+'</TD>';

        set @body=@body+'<TD>';
        set @body=@body+'СЕРВЕР';
        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='В ходе анализа последних выполнений заданий, задания с ошибочным завершением, а также те, что выполнились по времени более 30 секунд, не выявлены';
    end

    set @body=@body+'<br><br>Для более детальной информации обратитесь к таблице ИМЯ_БАЗЫ_ДАННЫХ.srv.ShortInfoRunJobs';
END

GO

Данная хранимая процедура формирует HTML-отчет о выполненных заданиях, которые выполнялись дольше 30 секунд или которые завершились с ошибкой (согласно п.3).


Результат


В статье выше был рассмотрен пример реализации системы ежедневного автоматического сбора информации о выполненных заданиях Агента. С помощью данной информации можно определить задания, которые выполнялись долго по времени или завершились с ошибкой. Это позволяет администратору своевременно принять меры для предотвращения ошибок в дальнейшем.
Например, можно улучшить задание, чтобы оно выполнялось быстрее, или выставить для данного задания максимальное время выше, чем у остальных.
Данное решение также очень помогает для отслеживания проблем с созданием резервных копий (но об этом позже, т. к. один раз в день уведомлять о критически важных ошибках недостаточно, необходимо уведомлять сразу и постоянно повторять уведомление через определенный промежуток времени, пока ошибка не будет исправлена).
Если нужно собирать информацию с нескольких серверов, то можно объединить результат и отправить одним сообщением.


Источники:


» sysjobs
» sysjobservers

Tags:
Hubs:
+10
Comments 20
Comments Comments 20

Articles