Автозбір даних про виконаних завданнях в MS SQL Server

Передмова
Адміністратора баз даних важливо знати, які завдання виконувалися. І як виконувалися (по тривалості, успішно чи не успішно і т. д.). І щоб цього не робити в ручну на кожному сервері, краще цей процес автоматизувати.
У даній статті наведу реалізацію автоматичного щоденного збору інформації про виконаних завданнях Агента в MS SQL Server.

Рішення
Алгоритм:
1) створити подання для відбору завдань:
Код
USE [ІМ'Я_БАЗИ_ДАНИХ]
GO

SET ON ANSI_NULLS
GO

SET ON QUOTED_IDENTIFIER
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 ON ANSI_NULLS
GO

SET ON QUOTED_IDENTIFIER
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 ON ANSI_NULLS
GO

SET ON QUOTED_IDENTIFIER
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)
,@Datetime LastDateTime
,@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
Джерело: Хабрахабр

0 коментарів

Тільки зареєстровані та авторизовані користувачі можуть залишати коментарі.