Partilhar via

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

Aplica-se a:SQL ServerAzure SQL Managed Instance

Cópias de mensagens do Database Mail e seus anexos são mantidas em msdb tabelas junto com o log de eventos do Database Mail. Periodicamente, você pode querer reduzir o tamanho das tabelas e arquivar mensagens e eventos que não são mais necessários.

Os procedimentos a seguir criam um trabalho do SQL Server Agent para automatizar o processo.

Pré-requisitos

Para executar comandos T-SQL em sua instância do SQL Server, use o SQL Server Management Studio (SSMS), a extensão MSSQL para Visual Studio Code, sqlcmd ou sua ferramenta de consulta T-SQL favorita.

Recomendações

Considere a verificação de erros e monitore esse trabalho para enviar uma mensagem de email aos operadores se esse trabalho de arquivamento falhar.

Opcionalmente, você pode mover dados arquivados do Database Mail para um banco de dados de arquivamento personalizado fora do msdb, ou exportá-los do SQL Server.

Permissões

Você deve ser membro da função fixa de servidor sysadmin para executar os procedimentos armazenados descritos neste tópico.

Criar uma Tarefa de Archive Database Mail

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

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

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

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

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

  5. Exclua os eventos relacionados aos itens de email transferidos do log de eventos do Database Mail.

  6. Agende a tarefa para ser executada periodicamente.

Criar uma tarefa do SQL Server Agent

As etapas a seguir usam o SQL Server Management Studio (SSMS). Baixe a versão mais recente do SSMS em aka.ms/ssms.

  1. Conecte-se à instância do SQL Server.

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

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

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

  5. Na caixa Categoria , selecione Manutenção do Banco de Dados.

  6. Na caixa Descrição , digite Arquivar mensagens do Database Mail e selecione Etapas.

Criar uma etapa de trabalho para arquivar as mensagens do Database Mail

  1. Na página Etapas , selecione Novo.

  2. Na caixa nome da etapa , digite Copiar itens do Database Mail.

  3. Na caixa Tipo, escolha o script Transact-SQL (T-SQL).

  4. Na caixa Banco de dados , selecione msdb.

  5. Na caixa Comando , digite a seguinte instrução T-SQL para criar uma tabela com o nome do mês anterior, contendo linhas anteriores ao 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. Selecione OK para salvar a etapa.

Criar uma etapa de trabalho para arquivar os anexos do Database Mail

  1. Na página Etapas , selecione Novo.

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

  3. Na caixa Tipo, escolha o script Transact-SQL (T-SQL).

  4. Na caixa Banco de dados , selecione msdb.

  5. Na caixa de comandos, digite a seguinte instrução para criar uma tabela de anexos com o nome do 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. Selecione OK para salvar a etapa.

Criar uma etapa de trabalho para arquivar o log do Database Mail

  1. Na página Etapas , selecione Novo.

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

  3. Na caixa Tipo, escolha o script Transact-SQL (T-SQL).

  4. Na caixa Banco de dados , selecione msdb.

  5. Na caixa de comandos, digite a seguinte instrução para criar uma tabela de log com o nome do mês anterior, contendo as entradas de 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. Selecione OK para salvar a etapa.

Criar uma etapa de trabalho para remover as linhas arquivadas do Database Mail

  1. Na página Etapas , selecione Novo.

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

  3. Na caixa Tipo, escolha o script Transact-SQL (T-SQL).

  4. Na caixa Banco de dados , selecione msdb.

  5. Na caixa de comando, digite a seguinte instrução para remover 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. Selecione OK para salvar a etapa.

Criar uma etapa de trabalho para remover os itens arquivados do log de eventos do Database Mail

  1. Na página Etapas , selecione Novo.

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

  3. Na caixa Tipo, escolha o script Transact-SQL (T-SQL).

  4. Na caixa de comandos, digite a seguinte instrução para remover 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. Selecione OK para salvar a etapa.

Programar o trabalho para ser executado periodicamente

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

  2. Na página Agendas , selecione Novo.

  3. Na caixa Nome, digite Archive Database Mail.

  4. Na caixa Tipo de agenda, selecione Recorrente.

  5. Na área de 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 <>.

  7. Verifique se as outras opções estão configuradas como você deseja e, em seguida, selecione OK para salvar a agenda.

  8. Selecione OK para salvar o trabalho.

Conteúdo relacionado

  • Last updated on