Предисловие
Администратору баз данных важно знать какие задачи выполнялись. И как выполнялись (по длительности, успешно или не успешно и т. д.). И чтобы этого не делать в ручную на каждом сервере, лучше этот процесс автоматизировать.
В данной статье приведу реализацию автоматического ежедневного сбора информации о выполненных заданиях Агента в 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; 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
ссылка на оригинал статьи https://habrahabr.ru/post/314628/
Добавить комментарий