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

Предисловие


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


В данной статье приведу реализацию автоматического ежедневного сбора информации о выполненных заданиях Агента в 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 секунд:

'+''; set @body=@body+''; set @body=@body+''; set @body=@body+''; set @body=@body+''; set @body=@body+''; set @body=@body+''; set @body=@body+''; set @body=@body+''; set @body=@body+''; set @body=@body+''; while((select top 1 1 from @tbl)>0) begin set @body=@body+''; 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; set @body=@body+''; set @body=@body+''; set @body=@body+''; set @body=@body+''; set @body=@body+''; set @body=@body+''; set @body=@body+''; set @body=@body+''; delete from @tbl where ID=@ID; set @body=@body+''; end set @body=@body+'
'; set @body=@body+'№ п/п'; set @body=@body+''; set @body=@body+'ГУИД'; set @body=@body+''; set @body=@body+'ЗАДАНИЕ'; set @body=@body+''; set @body=@body+'СТАТУС'; set @body=@body+''; set @body=@body+'ДАТА И ВРЕМЯ'; set @body=@body+''; set @body=@body+'ДЛИТЕЛЬНОСТЬ'; set @body=@body+''; set @body=@body+'СООБЩЕНИЕ'; set @body=@body+''; set @body=@body+'СЕРВЕР'; set @body=@body+'
'; set @body=@body+cast(@ID as nvarchar(max)); set @body=@body+''; set @body=@body+cast(@Job_GUID as nvarchar(255)); set @body=@body+''; set @body=@body+coalesce(@Job_Name,''); set @body=@body+''; set @body=@body+coalesce(@LastFinishRunState,''); set @body=@body+''; set @body=@body+rep.GetDateFormat(@LastDateTime, default)+' '+rep.GetTimeFormat(@LastDateTime, default);--cast(@InsertDate as nvarchar(max)); set @body=@body+''; set @body=@body+coalesce(@LastRunDurationString,''); set @body=@body+''; set @body=@body+coalesce(@LastOutcomeMessage, ''); set @body=@body+''; set @body=@body+coalesce(@Server, ''); set @body=@body+'
'; end else begin set @body='В ходе анализа последних выполнений заданий, задания с ошибочным завершением, а также те, что выполнились по времени более 30 секунд, не выявлены'; end set @body=@body+'

Для более детальной информации обратитесь к таблице ИМЯ_БАЗЫ_ДАННЫХ.srv.ShortInfoRunJobs'; END GO

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


Результат


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


Источники:


» sysjobs
» sysjobservers

Комментарии (0)

© Habrahabr.ru