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

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

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

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

Представленные ниже процедуры используются для создания задания агента SQL Server для автоматизации указанного процесса.

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

Чтобы выполнить команды T-SQL в экземпляре SQL Server, используйте SQL Server Management Studio (SSMS),расширение MSSQL для Visual Studio Code, sqlcmd или любимое средство запросов T-SQL.

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

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

При необходимости можно переместить архивированные данные Database Mail в настраиваемую архивную базу данных за пределами msdbили экспортировать их из 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.

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

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

В приведенных ниже инструкциях используется SQL Server Management Studio (SSMS). Скачайте последнюю версию SSMS на aka.ms/ssms.

  1. Подключитесь к экземпляру SQL Server.

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

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

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

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

  6. В поле "Описание" введите архив сообщений Database Mail и нажмите кнопку "Шаги".

Создание шага задания для архивации сообщений Database Mail

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

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

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

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

  5. В командном поле введите следующую инструкцию T-SQL, чтобы создать таблицу с именем предыдущего месяца, содержащую строки старше начала текущего месяца.

    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. Убедитесь, что другие параметры настроены по желанию, а затем нажмите кнопку "ОК ", чтобы сохранить расписание.

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

Связанный контент