Поделиться через

  • Статья

Как создать задание агента SQL Server по архивации сообщений компонента Database Mail и журналов событий базы данных

Копии сообщений компонента Database Mail и их вложения хранятся в таблицах msdb, расположенных в журнале событий компонента Database Mail. Может возникнуть потребность периодического уменьшения объема таблиц и удаления устаревших сообщений и событий. Представленные ниже процедуры используются для создания задания агента SQL Server для автоматизации указанного процесса.

  1. Первая процедура предназначена для создания задания с именем «Архивация компонента Database Mail», состоящего из четырех шагов.

  2. При выполнении первого шага происходит копирование всех сообщений таблиц компонента Database Mail в новую таблицу с именем DBMailArchive_<год_месяц>.

  3. При выполнении второго шага происходит копирование всех вложений, прикрепленных к скопированным сообщениям таблиц компонента Database Mail, в новую таблицу с именем DBMailArchive_Attachments_<год_месяц>.

  4. При выполнении третьего шага происходит копирование всех событий, связанных со скопированными на первом этапе сообщениями таблиц компонента Database Mail, в новую таблицу с именем DBMailArchive_Log_<год_месяц>.

  5. В ходе выполнения четвертого шага происходит удаление всех скопированных элементов из таблиц компонента Database Mail.

  6. В ходе выполнения пятого шага происходит удаление всех связанных со скопированными элементами событий, хранящихся в журнале событий компонента Database Mail.

  7. Последняя процедура используется для планирования задания на ежемесячный запуск.

В данном примере архивные таблицы создаются в базе данных msdb. Чтобы уменьшить объем базы данных msdb, необходимо хранить новые таблицы в специальной архивной базе данных. Также можно экспортировать строки данных в текстовый файл или удалить их. В данном примере строки данных помещаются в новые таблицы базы данных msdb. В случае сбоя задания в процессе работы, возможно, понадобится провести дополнительную проверку и отослать уведомления операторам.

Создание задания агента SQL Server

  1. В обозревателе объектов разверните узел агента SQL Server, правой кнопкой мыши щелкните элемент Задания и выберите команду Создать задание.

  2. В диалоговом окне Создание задания в поле Имя введите Archive Database Mail.

  3. В окне Владелец подтвердите принадлежность владельца к предопределенной роли сервера sysadmin.

  4. В окне Категория выберите Обслуживание базы данных.

  5. В поле Описание введите Archive Database Mail messages, а затем выберите вкладку Шаги.

Создание шага по архивации сообщений компонента Database Mail

  1. На странице Шаги нажмите кнопку Создать.

  2. В текстовое поле Имя шага введите Copy Database Mail Items.

  3. В поле Тип выберите Сценарий Transact-SQL (T-SQL).

  4. В поле База данных выберите msdb.

  5. Чтобы создать таблицу с именем прошлого месяца, в которой будут храниться все строки данных за предыдущие месяцы, в окне Команда введите представленную ниже инструкцию:

    DECLARE @LastMonth nvarchar(12);
DECLARE @CopyDate nvarchar(20) ;
DECLARE @CreateTable nvarchar(250) ;
SET @LastMonth = (SELECT CAST(DATEPART(yyyy,GETDATE()) AS CHAR(4)) + '_' + CAST(DATEPART(mm,GETDATE())-1 AS varchar(2))) ;
SET @CopyDate = (SELECT CAST(CONVERT(char(8), CURRENT_TIMESTAMP- DATEPART(dd,GETDATE()-1), 112) AS datetime))
SET @CreateTable = 'SELECT * INTO msdb.dbo.[DBMailArchive_' + @LastMonth + '] FROM sysmail_allitems WHERE send_request_date < ''' + @CopyDate +'''';
EXEC sp_executesql @CreateTable ;

  6. Нажмите кнопку ОК, чтобы сохранить шаг.

Создание шага по архивации вложений компонента Database Mail

  1. На странице Шаги нажмите кнопку Создать.

  2. В текстовое поле Имя шага введите Copy Database Mail Attachments.

  3. В поле Тип выберите Сценарий Transact-SQL (T-SQL).

  4. В поле База данных выберите msdb.

  5. Чтобы создать таблицу с именем прошлого месяца, в которой будут храниться все вложения, связанные с сообщениями, скопированными на предыдущем шаге, в окне Команда введите представленную ниже инструкцию:

    DECLARE @LastMonth nvarchar(12);
DECLARE @CopyDate nvarchar(20) ;
DECLARE @CreateTable nvarchar(250) ;
SET @LastMonth = (SELECT CAST(DATEPART(yyyy,GETDATE()) AS CHAR(4)) + '_' + CAST(DATEPART(mm,GETDATE())-1 AS varchar(2))) ;
SET @CopyDate = (SELECT CAST(CONVERT(char(8), CURRENT_TIMESTAMP- DATEPART(dd,GETDATE()-1), 112) AS datetime))
SET @CreateTable = 'SELECT * INTO msdb.dbo.[DBMailArchive_Attachments_' + @LastMonth + '] FROM sysmail_attachments 
 WHERE mailitem_id in (SELECT DISTINCT mailitem_id FROM [DBMailArchive_' + @LastMonth + '] )';
EXEC sp_executesql @CreateTable ;

  6. Нажмите кнопку ОК, чтобы сохранить шаг.

Создание шага по архивации журнала компонента Database Mail

  1. На странице Шаги нажмите кнопку Создать.

  2. В текстовом поле Имя шага введите Copy Database Mail Log.

  3. В поле Тип выберите Сценарий Transact-SQL (T-SQL).

  4. В поле База данных выберите msdb.

  5. Чтобы создать таблицу с именем прошлого месяца, в которой будут храниться все записи журнала, связанные с сообщениями, скопированными на предыдущих шагах, в окне Команда введите представленную ниже инструкцию:

    DECLARE @LastMonth nvarchar(12);
DECLARE @CopyDate nvarchar(20) ;
DECLARE @CreateTable nvarchar(250) ;
SET @LastMonth = (SELECT CAST(DATEPART(yyyy,GETDATE()) AS CHAR(4)) + '_' + CAST(DATEPART(mm,GETDATE())-1 AS varchar(2))) ;
SET @CopyDate = (SELECT CAST(CONVERT(char(8), CURRENT_TIMESTAMP- DATEPART(dd,GETDATE()-1), 112) AS datetime))
SET @CreateTable = 'SELECT * INTO msdb.dbo.[DBMailArchive_Log_' + @LastMonth + '] FROM sysmail_Event_Log 
 WHERE mailitem_id in (SELECT DISTINCT mailitem_id FROM [DBMailArchive_' + @LastMonth + '] )';
EXEC sp_executesql @CreateTable ;

  6. Нажмите кнопку ОК, чтобы сохранить шаг.

Создание шага по удалению архивных строк из компонента Database Mail

  1. На странице Шаги нажмите кнопку Создать.

  2. В текстовом поле Имя шага введите Remove rows from Database Mail.

  3. В поле Тип выберите Сценарий Transact-SQL (T-SQL).

  4. В поле База данных выберите msdb.

  5. Чтобы удалить из таблиц компонента Database Mail строки, созданные ранее начала текущего месяца, в окне Команда введите представленную ниже инструкцию:

    DECLARE @CopyDate nvarchar(20) ;
SET @CopyDate = (SELECT CAST(CONVERT(char(8), CURRENT_TIMESTAMP- DATEPART(dd,GETDATE()-1), 112) AS datetime)) ;
EXECUTE msdb.dbo.sysmail_delete_mailitems_sp @sent_before = @CopyDate ;

  6. Нажмите кнопку ОК, чтобы сохранить шаг.

Создание шага по удалению архивных элементов из журнала событий компонента Database Mail

  1. На странице Шаги нажмите кнопку Создать.

  2. В текстовом поле Имя шага введите Remove rows from Database Mail event log.

  3. В поле Тип выберите Сценарий Transact-SQL (T-SQL).

  4. В поле База данных выберите msdb.

  5. Чтобы удалить из журнала событий компонента Database Mail строки, созданные ранее начала текущего месяца, в окне Команда введите представленную ниже инструкцию:

    DECLARE @CopyDate nvarchar(20) ;
SET @CopyDate = (SELECT CAST(CONVERT(char(8), CURRENT_TIMESTAMP- DATEPART(dd,GETDATE()-1), 112) AS datetime)) ;
EXECUTE msdb.dbo.sysmail_delete_log_sp @logged_before = @CopyDate ;

  6. Нажмите кнопку ОК, чтобы сохранить шаг.

Планирование запуска задания на начало каждого месяца

  1. В диалоговом окне Создание задания выберите Расписания.

  2. На странице Расписания нажмите кнопку Создать.

  3. В текстовое поле Имя введите Archive Database Mail.

  4. В окне Тип расписания выберите Циклический.

  5. В области Частота задайте выполнение задания первого числа каждого месяца.

  6. В области Сколько раз в день выберите Проводится один раз в день в 15:00.

  7. Убедитесь, что другие параметры настроены правильно, и сохраните расписание, нажав кнопку OK.

  8. Нажмите кнопку ОК, чтобы сохранить задание.

Безопасность

Чтобы выполнить хранимые процедуры, описанные в данном разделе, пользователь должен быть членом предопределенной роли сервера sysadmin.