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

Статья
12/15/2008

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

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

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

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

В обозревателе объектов разверните узел агента SQL Server, правой кнопкой мыши щелкните элемент Задания и выберите команду Создать задание.
В диалоговом окне Создание задания в поле Имя введите Archive Database Mail.
В окне Владелец подтвердите принадлежность владельца к фиксированной серверной роли sysadmin.
В окне Категория выберите Обслуживание базы данных.
В поле Описание введите Archive Database Mail messages, а затем выберите вкладку Шаги.

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

На странице Шаги нажмите кнопку Создать.
В текстовое поле Имя шага введите Copy Database Mail Items.
В поле Тип выберите Сценарий Transact-SQL (T-SQL).
В поле База данных выберите msdb.

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

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 ;

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

Создайте шаг, на котором будут архивироваться вложения сообщений компонента Database Mail

На странице Шаги нажмите кнопку Создать.
В текстовое поле Имя шага введите Copy Database Mail Attachments.
В поле Тип выберите Сценарий Transact-SQL (T-SQL).
В поле База данных выберите msdb.

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

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 ;

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

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

На странице Шаги нажмите кнопку Создать.
В текстовом поле Имя шага введите Copy Database Mail Log.
В поле Тип выберите Сценарий Transact-SQL (T-SQL).
В поле База данных выберите msdb.

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

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 ;

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

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

На странице Шаги нажмите кнопку Создать.
В текстовом поле Имя шага введите Remove rows from Database Mail.
В поле Тип выберите Сценарий Transact-SQL (T-SQL).
В поле База данных выберите msdb.
Чтобы удалить из таблиц компонента 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 ;
```
Нажмите кнопку ОК, чтобы сохранить шаг.

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

На странице Шаги нажмите кнопку Создать.
В текстовом поле Имя шага введите Remove rows from Database Mail event log.
В поле Тип выберите Сценарий Transact-SQL (T-SQL).
В поле База данных выберите msdb.
Чтобы удалить из журнала событий компонента 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 ;
```
Нажмите кнопку ОК, чтобы сохранить шаг.

Спланируйте задание таким образом, чтобы оно выполнялось ежемесячно

В диалоговом окне Создание задания выберите Расписания.
На странице Расписания нажмите кнопку Создать.
В текстовое поле Имя введите Archive Database Mail.
В окне Тип расписания выберите Циклический.
В области Частота задайте выполнение задания первого числа каждого месяца.
В области Сколько раз в день выберите Проводится один раз в день в 15:00.
Убедитесь, что другие параметры настроены правильно, и сохраните расписание, нажав кнопку OK.
Нажмите кнопку ОК, чтобы сохранить задание.

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

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

См. также

Share via