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

  • Tutorial

Предисловие


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


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

Поделиться публикацией

Комментарии 20

    +1
    сформировать HTML-отчет для дальнейшей отправки на почту администраторам о результатах:

    Формирование текста HTML отчета в цикле это сильно! А почему не использовали конкатенацию через for xml path('')?
      0
      Это одна из возможностей
      Можно конечно, как и Вы предлагаете.
      Приведите пример
        +1
        Примерно накидал код на основе вашей процедуры. Как бонус обернул текст в табличку.

        Пример конкатенации
        declare @body nvarchar(max) 
        
        set @body = (	select     
        			    cast(Job_GUID as nvarchar(36)) + '</td><td>' 
        			    + isnull(Job_Name, '') + '</td>' 
        		        from    srv.ShortInfoRunJobs
        			for xml path('tr'))
        
        set @body = '<html>
        				<head>
        					<style type="text/css">
        						table {
        							border-collapse: collapse;
        							border: 2px solid black;
        							font: 10pt helvetica;
        						}
        						th {
        							padding: 10px;
        							text-align: center;
        						}
        						td {
        							padding: 10px;
        							text-align: center;
        						}
        					</style>
        				</head>
        				<body>
        					<table border="1" cellspacing="0">'
        				  + '<tr><th>ID</th><th>Job name</th></tr>'
        				  + replace( replace( @body, '<', '<' ), '>', '>' )
        				  + '</table>
        				</body>
        			 </html>';
        

          0
          Ваше решение намного улучшило мое предложенное)
          Спасибо)
      0
      .
        +1
        Из опыта, используем Zabbix для централизованного мониторинга. Количество заданий, выполнившихся с ошибкой мониторим через счетчик \SQLAgent:Jobs(_Total)\Failed jobs.
          0
          Про Zabbix спасибо-попробуем использовать)
          На счет заданий-здесь важно еще и сколько по времени выполняется задание
          +1
          Zabbix-ом можно мониторить и время выполнения задания, например написав соответсвтующий запрос и выполнять его через zabbix по odbc. Через zabbix можно вообще много чего реализовать, было бы желание))
            0
            Vitoska, порекомендуйте ссылки на то, что может Заббикс, как его устанавливать и настраивать.
            Здесь ознакомился
            +1
            https://www.zabbix.com/documentation/3.0/ru/manual — официальная документация, более чем достаточно для начала. Развернуть можно несколькими способами, если не хотите заморачиваться, то можно скачать уже готовый образ системы и развернуть его, вот ссылка http://www.zabbix.com/ru/download. Относительно SQL Server мы мониторим счетчики perfmon и по odbc.
              0
              Спасибо)
                +1
                Пожалуйста, будут вопросы пишите.
              +1
              Можно без создания прямо в агенте сделать задание
              IF (SELECT COUNT(*)
              FROM sysjobs AS s 
              INNER JOIN sysjobsteps AS s2 (nolock) ON s2.job_id = s.job_id
              INNER JOIN dbo.sysjobhistory sh (nolock) ON sh.instance_id=(SELECT MAX(sjh.instance_id) FROM dbo.sysjobhistory sjh (nolock) WHERE sjh.job_id=s.job_id AND sjh.step_id=s2.step_id) 
              			  AND sh.run_date>=(SELECT MAX(sjh.run_date) FROM dbo.sysjobhistory sjh (nolock) WHERE sjh.job_id=s.job_id AND sjh.step_id=0)
              WHERE s.[enabled]=1 AND sh.run_status=0)>0
              BEGIN
              DECLARE @tableHTML AS NVARCHAR(max)
              
              SET @tableHTML =
                  N'<H1>Ошибки в джобах на sql сервере '+@@SERVERNAME+'</H1>' +
                  N'<table border="1">' +
                  N'<tr><th>Джоб</th><th>ID шага</th>' +
                  N'<th>Имя шага</th><th>команда</th><th>база</th>' +
                  N'<th>последний запуск</th><th>ошибка</th></tr>' +
                  CAST ( ( SELECT td = s.name,'', td = s2.step_id,'', td = s2.step_name, '',td = cast(s2.command AS VARCHAR(100)),'', td = s2.database_name,'',
                     td = s2.last_run_date,'', td = cast(sh.[message] AS VARCHAR(100))
              FROM sysjobs AS s (nolock)
              INNER JOIN sysjobsteps AS s2 (nolock) ON s2.job_id = s.job_id
              INNER JOIN dbo.sysjobhistory sh (nolock) ON sh.instance_id=(SELECT MAX(sjh.instance_id) FROM dbo.sysjobhistory sjh (nolock) WHERE sjh.job_id=s.job_id AND sjh.step_id=s2.step_id) 
              			  AND sh.run_date>=(SELECT MAX(sjh.run_date) FROM dbo.sysjobhistory sjh (nolock) WHERE sjh.job_id=s.job_id AND sjh.step_id=0)
              WHERE s.[enabled]=1 AND sh.run_status=0
              --ORDER BY s.name, s2.step_id
              FOR XML PATH('tr'), TYPE 
                  ) AS NVARCHAR(MAX) ) +
                  N'</table>' ;
              
              
              DECLARE @subject AS NVARCHAR(max)
              SET @subject='Ошибка в работе джобов на сервере '+@@SERVERNAME
              
              EXEC msdb.dbo.sp_send_dbmail
              @profile_name = 'MAIL'
              ,@recipients = ''
              ,@subject = @subject
              ,@importance ='Normal'
              ,@body =  @tableHTML
              ,@body_format = 'HTML' 
              
              END
              

              подставив нужных получателей и почтовый профиль
                0
                Верно, однако лучше оборачивать все в хранимые процедуры. Так проще поддерживать и найти зависимости, чем код читать прямо в заданиях Агента
                  +1
                  Там убогий EditBox под это дело и весь этот код совершенно невозможно будет прочитать, тем более исправить.
                    0
                    У нас правда некоторые до сих пор побыстрому пишут код прямо в заданиях
                    Приходится потом оборачивать, иначе глаз сломаешь, особенно когда код не на один экран или запрос очень нетривиальный
                      +1
                      У нас так никто не делает. Все пишут что надо в обычном окне запросов, и CtrlC-CtrlV в это окошко. А потом начинается: Ты взял бэкапы? — Да, вот они — А джобы где???
                        0
                        Знакомая ситуация)
                  0
                  Огромное спасибо programmer76 за увиденную ошибку в публикации. Поправил.
                  Ошибка заключалась в том, что при вставке данных нет смысла сортировать, а сортировать нужно в момент отбора-шаг 4 алгоритма.
                    0
                    Приношу извинения, fakir89 спасибо) Просто с телефона неудобно писать и мелко все

                  Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.

                  Самое читаемое