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

  • Статья

Применимо к:Управляемому экземпляру SQL Server 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. В текстовом поле Имя шага введите 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. Чтобы удалить из журнала событий компонента 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. Нажмите кнопку ОК , чтобы сохранить задание.

Обзор