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

Создание задания агента SQL Server для архивации сообщений Database Mail и журналов событий

  • Статья

Область применения: SQL Server Управляемый экземпляр SQL Azure

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

Перед началом

Предварительные условия

Новые таблицы для хранения архивных данных могут быть расположены в специальной архивной базе данных. Кроме того, строки можно экспортировать в текстовый файл.

Рекомендации

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

Разрешения

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

Общие сведения о процессе

  • Первая процедура, которая создает задание с именем «Archive Database Mail», состоит из следующих действий.

    1. Скопируйте все сообщения из таблиц Database Mail в новую таблицу с именем предыдущего месяца в формате DBMailArchive_<year_month.>

    2. Скопируйте вложения, связанные с сообщениями, скопированными на первом шаге, из таблиц Database Mail в новую таблицу с именем предыдущего месяца в формате DBMailArchive_Attachments_<year_month.>

    3. Скопируйте события из журнала событий Database Mail, которые связаны с сообщениями, скопированным на первом шаге, из таблиц Database Mail в новую таблицу с именем предыдущего месяца в формате DBMailArchive_Log_<year_month.>

    4. Удалить записи о переданных элементах почты из таблиц Database Mail.

    5. Удалите события, связанные с перенесёнными почтовыми элементами, из журнала событий компонента Database Mail.

  • Запланируйте выполнение задания на регулярной основе.

Чтобы создать задание для агента 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. В поле Имя шага введите Удалить строки из 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. В текстовом поле Имя шага введите Удалить строки из журнала событий Database Mail.

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

  4. Чтобы удалить из журнала событий компонента 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 ;

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

Обзор

Запланировать периодическое выполнение задания

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

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

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

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

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

  6. В области ежедневной частоты выберите Происходит один раз в <время>.

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

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

Обзор