Автосбор данных о выполненных заданиях в 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]
GO3) создать в Агенте задачу и ежедневно собирать информацию о тех задачах, которые либо долго выполнялись (больше 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+' ';
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+' ';
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+cast(@ID as nvarchar(max));
set @body=@body+' ';
set @body=@body+'';
set @body=@body+cast(@Job_GUID as nvarchar(255));
set @body=@body+' ';
set @body=@body+'';
set @body=@body+coalesce(@Job_Name,'');
set @body=@body+' ';
set @body=@body+'';
set @body=@body+coalesce(@LastFinishRunState,'');
set @body=@body+' ';
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+'';
set @body=@body+coalesce(@LastRunDurationString,'');
set @body=@body+' ';
set @body=@body+'';
set @body=@body+coalesce(@LastOutcomeMessage, '');
set @body=@body+' ';
set @body=@body+'';
set @body=@body+coalesce(@Server, '');
set @body=@body+' ';
delete from @tbl
where ID=@ID;
set @body=@body+' ';
end
set @body=@body+'
';
end
else
begin
set @body='В ходе анализа последних выполнений заданий, задания с ошибочным завершением, а также те, что выполнились по времени более 30 секунд, не выявлены';
end
set @body=@body+'
Для более детальной информации обратитесь к таблице ИМЯ_БАЗЫ_ДАННЫХ.srv.ShortInfoRunJobs';
END
GOДанная хранимая процедура формирует HTML-отчет о выполненных заданиях, которые выполнялись дольше 30 секунд или которые завершились с ошибкой (согласно п.3).
Результат
В данной статье был рассмотрен пример реализации системы ежедневного автоматического сбора информации о выполненных заданиях Агента. Данная информация позволяет узнать какие задания выполнились с ошибкой или долго по времени. Это позволяет администратору своевременно принять меры для предотвращения ошибок в дальнейшем. Также можно улучшить задание, чтобы оно выполнялось быстрее или принять решение, что для этого задания максимальное время выставить выше, чем у остальных. Также данное решение очень помогает для отслеживания проблем с созданием резервных копий (но об этом позже, т. к. один раз в день уведомлять о критически важных ошибках недостаточно, необходимо сразу и все время повторять через какое-то время, пока ошибка не уйдет в следствии работы администратора или каким-либо иным способом).
Если нужно собирать информацию с нескольких серверов, то можно объединить результат и отправить одним сообщением.
Источники:
» sysjobs
» sysjobservers
