Criar um trabalho do SQL Server Agent para arquivar mensagens do Database Mail e logs de eventos

  • Artigo

Cópias de mensagens do Database Mail e seus anexos são retidos em tabelas msdb junto com o log de eventos do Database Mail. Periodicamente, convém reduzir o tamanho das tabelas e arquivar mensagens e eventos que não sejam mais necessários. Os procedimentos a seguir criam um trabalho do SQL Server Agent para automatizar o processo.

  • Antes de começar:  , Pré-requisitos, Recomendações, Permissões

  • Para arquivar mensagens e logs do Database Mail usando : SQL Server Agent

Antes de começar

Pré-requisitos

As novas tabelas para armazenar os dados de arquivo morto devem estar localizadas em um banco de dados de arquivo morto especial. Alternativamente as linhas podem ser exportadas para um arquivo de texto.

Início

Recomendações

No seu ambiente de produção, convém adicionar outras verificações de erros e enviar uma mensagem de email aos operadores, caso o trabalho falhe.

Início

Permissões

É necessário ser membro da função de servidor fixa sysadmin para poder executar os procedimentos armazenados descritos neste tópico.

Início

Visão geral do processo

  • O primeiro procedimento cria um trabalho denominado Archive Database Mail com as etapas a seguir.

    1. Copie todas as mensagens das tabelas do Database Mail em uma nova tabela nomeada com o mês anterior, no formato DBMailArchive_<year_month>.

    2. Copie os anexos das mensagens copiadas na primeira etapa, das tabelas do Database Mail em uma nova tabela nomeada com o mês anterior, no formato DBMailArchive_Attachments_<year_month>.

    3. Copie os eventos do log de eventos do Database Mail referentes às mensagens copiadas na primeira etapa, das tabelas do Database Mail em uma nova tabela nomeada com o mês anterior, no formato DBMailArchive_Log_<year_month>.

    4. Exclua os registros dos itens de correio transferidos das tabelas do Database Mail.

    5. Exclua os eventos referentes aos itens de correio transferidos do log de eventos do Database Mail.

  • Agende o trabalho a ser executado periodicamente.

Início

Para criar um trabalho do SQL Server Agent

  1. No Pesquisador de Objetos, expanda o SQL Server Agent, clique com o botão direito do mouse em Trabalhos e, em seguida, em Novo Trabalho.

  2. Na caixa de diálogo Novo Trabalho, na caixa Nome, digite Archive Database Mail.

  3. Na caixa Proprietário, confirme que o proprietário é um membro da função de servidor fixa sysadmin.

  4. Na caixa Categoria, clique em Manutenção de Banco de Dados.

  5. Na caixa Descrição, digite Archive Database Mail messages e clique em Etapas.

Visão geral

Para criar uma etapa para arquivar as mensagens do Database Mail

  1. Na página Etapas, clique em Nova.

  2. Na caixa Nome da etapa, digite Copiar Itens do Database Mail.

  3. Na caixa Tipo, selecione Script Transact-SQL (T-SQL).

  4. Na caixa Banco de dados, selecione msdb.

  5. Na caixa Comando, digite a seguinte instrução para criar uma tabela nomeada com o mês anterior, contendo linhas anteriores à data de início do mês atual:

    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. Clique em OK para salvar a etapa.

Visão geral

Para criar uma etapa para arquivar os anexos do Database Mail

  1. Na página Etapas, clique em Nova.

  2. Na caixa Nome da etapa, digite Copiar Anexos do Database Mail.

  3. Na caixa Tipo, selecione Script Transact-SQL (T-SQL).

  4. Na caixa Banco de dados, selecione msdb.

  5. Na caixa Comando, digite a seguinte instrução para criar uma tabela de anexos nomeada com o mês anterior, contendo os anexos que correspondem às mensagens transferidas na etapa anterior:

    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. Clique em OK para salvar a etapa.

Visão geral

Para criar uma etapa para arquivar o log do Database Mail

  1. Na página Etapas, clique em Nova.

  2. Na caixa Nome da etapa, digite Copiar Log do Database Mail.

  3. Na caixa Tipo, selecione Script Transact-SQL (T-SQL).

  4. Na caixa Banco de dados, selecione msdb.

  5. Na caixa Comando, digite a seguinte instrução para criar uma tabela de log nomeada com o mês anterior, contendo as entradas do log que correspondem às mensagens transferidas na etapa anterior:

    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. Clique em OK para salvar a etapa.

Visão geral

Para criar uma etapa para remover as linhas arquivadas do Database Mail

  1. Na página Etapas, clique em Nova.

  2. Na caixa Nome da etapa, digite Remover linhas do Database Mail.

  3. Na caixa Tipo, selecione Script Transact-SQL (T-SQL).

  4. Na caixa Banco de dados, selecione msdb.

  5. Na caixa Comando, digite a seguinte instrução para remover as linhas anteriores ao mês atual das tabelas do 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. Clique em OK para salvar a etapa.

Visão geral

Para criar uma etapa para remover os itens arquivados do log de eventos do Database Mail

  1. Na página Etapas, clique em Nova.

  2. Na caixa Nome da etapa, digite Remover linhas do log de eventos do Database Mail.

  3. Na caixa Tipo, selecione Script Transact-SQL (T-SQL).

  4. Na caixa Comando, digite a seguinte instrução para remover as linhas anteriores ao mês atual do log de eventos do 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. Clique em OK para salvar a etapa.

Visão geral

Para agendar o trabalho a ser executado periodicamente

  1. Na caixa de diálogo Novo Trabalho, clique em Agendas.

  2. Na página Agendas, clique em Nova.

  3. Na caixa Nome, digite Arquivar Database Mail.

  4. Na caixa Tipo de agenda, selecione Recorrente.

  5. Na área Frequência, selecione as opções para executar o trabalho periodicamente; por exemplo, uma vez por mês.

  6. Na área Frequência diária, selecione Ocorre uma vez às <hora>.

  7. Verifique se as outras opções estão configuradas a seu gosto e clique em OK para salvar a agenda.

  8. Clique em OK para salvar o trabalho.

Visão geral