Часто при администрировании баз данных возникает необходимость настроить оповещения о сбоях в задачах резервного копирования или обслуживания. Стандартная настройка планов обслуживания (Maintenance Plans) в Management Studio имеет существенный недостаток: если включить протоколирование через кнопку настроек отчета, на почту будет приходить весь лог целиком вне зависимости от того, успешно завершилась задача или нет. Кроме того, стандартным уведомлениям не хватает информативности — в них может отсутствовать имя проблемной базы данных или развернутый стек ошибки.
Давайте разберем по шагам, как решить эту задачу и настроить отправку отчета на почту с подробным логом событий только при неудачном завершении задания.
Самый простой подход — отказаться от отправки отчетов внутри самого плана обслуживания и перенести эту логику на уровень SQL Server Agent.
Таким образом, мы избавимся от лишних писем при успешном выполнении. Однако, стандартный текст сообщения от Агента может оказаться недостаточно полным (фиксированный текст и тема).
Рассмотрим альтернативный вариант решения средствами самого плана обслуживания:
Уведомление оператора или Выполнение инструкции T-SQL, которая будет собирать и отправлять лог.Чтобы отправить подробный текст ошибки, его нужно откуда-то взять. В свойствах каждого конкретного шага задания (Job Steps) есть скрытые, но очень полезные настройки.
Зайдем в свойства шага задания на вкладку Дополнительно (Advanced). Проанализируем доступные опции:
msdb.dbo.sysjobsteplogs. Из этой таблицы достать развернутый лог гораздо удобнее с помощью скрипта.Чтобы обойти ограничения стандартных шаблонов и получить красивое письмо с именем базы данных и текстом ошибки, создадим свой собственный шаг на T-SQL, который выполняется при условии сбоя.
Нам потребуется сделать выборку из системных таблиц базы msdb:
sysjobs — для получения имени задания.sysjobsteps — для получения информации о шагах (здесь можно извлечь контекст базы данных).sysjobhistory — для получения истории. Главная колонка здесь — message. Именно она содержит подробный лог ошибки.Собранные данные мы объединяем в удобную HTML-таблицу и передаем системной хранимой процедуре sp_send_dbmail. Посмотрим на пример использования токенов, чтобы скрипт был универсальным.
Важно: в свойствах агента SQL Server (вкладка Alert System) включите галочку Replace tokens for all job responses to alerts. Это позволит использовать встроенные макросы.
Пример логики для передачи уникального ID упавшего задания в скрипт:
DECLARE @JobID UNIQUEIDENTIFIER = $(ESCAPE_SQUOTE(JOBID));
-- Далее пишем SELECT запрос к таблицам:
-- msdb.dbo.sysjobs
-- msdb.dbo.sysjobhistory
-- Фильтруем по @JobID и отправляем через sp_send_dbmail
Этот скрипт сам поймет, какое именно задание его вызвало, и вытащит ошибку именно по нему.
Если в вашей инфраструктуре развернут SQL Server Reporting Services (SSRS), вы можете вынести логику оповещений за пределы планов обслуживания. Создайте отчет SSRS, который периодически опрашивает таблицу упавших заданий. Настройте на этот отчет подписку по электронной почте (E-Mail Subscription). Вы будете получать отформатированное письмо с таблицей (имя сервера, имя БД, ошибка) только при появлении новых сбоев.
Выясним частую причину, почему письма с логами могут не приходить даже при правильной настройке заданий. Убедитесь, что ваш профиль электронной почты имеет правильные права доступа.
Перейдите в настройки Database Mail и проверьте, что ваш почтовый профиль помечен как Публичный профиль (Public Profile) и для него установлено значение по умолчанию (Default Profile = Yes). Встроенные уведомления Агента используют именно публичный профиль по умолчанию. Если профиль приватный, в логах Database Mail будут фиксироваться скрытые ошибки отправки.