Compartilhar via

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

Aplica-se a:SQL ServerInstância Gerenciada de SQL do Azure

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, 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.

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 essa tarefa para enviar uma mensagem de e-mail aos operadores se essa tarefa de arquivamento falhar.

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

Permissões

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

Criar uma tarefa de mail do Banco de Dados Arquivo

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 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 correio transferidos das tabelas do Database Mail.

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

  6. Agende o trabalho a ser executado periodicamente.

Criar um trabalho do SQL Server Agent

As etapas a seguir usam o SSMS (SQL Server Management Studio). 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 o 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 que o proprietário é um membro da função de servidor fixa sysadmin .

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

  6. Na caixa Descrição, digite Arquivar mensagens do Database Mail e, em seguida, 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 Copy Database Mail Items.

  3. Na caixa Tipo , selecione Transact-SQL script (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 Copy Database Mail Attachments.

  3. Na caixa Tipo , selecione Transact-SQL script (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. 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 Copy Database Mail Log.

  3. Na caixa Tipo , selecione Transact-SQL script (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. 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 Remove rows from Database Mail.

  3. Na caixa Tipo , selecione Transact-SQL script (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. 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 , selecione Transact-SQL script (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. Selecione OK para salvar a etapa.

Agendar 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 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 em <hora>.

  7. Verifique se as outras opções estão configuradas como desejar e selecione OK para salvar a agenda.

  8. Selecione OK para salvar a tarefa.

Conteúdo relacionado

  • Last updated on