• Artigo

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

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

  1. O primeiro procedimento cria um trabalho denominado Archive Database Mail, com quatro etapas.

  2. A primeira etapa copia todas as mensagens das tabelas do Database Mail para uma nova tabela nomeada com o mês anterior, no formato DBMailArchive_<ano_mês>.

  3. A segunda etapa copia os anexos das mensagens copiadas na primeira etapa, das tabelas do Database Mail para uma nova tabela nomeada com o mês anterior, no formato DBMailArchive_Attachments_<ano_mês>.

  4. A terceira etapa copia os eventos do log de eventos do Database Mail referentes às mensagens copiadas na primeira etapa, das tabelas do Database Mail para uma nova tabela nomeada com o mês anterior, no formato DBMailArchive_Log<ano_mês>.

  5. A quarta etapa exclui os registros dos itens de correio transferidos das tabelas do Database Mail.

  6. A quinta etapa exclui os eventos referentes aos itens de correio transferidos do log de eventos do Database Mail.

  7. O procedimento final agenda o trabalho para execução no início de cada mês.

Neste exemplo, as tabelas de arquivo morto são criadas no banco de dados msdb. Para reduzir o tamanho do banco de dados msdb, as novas tabelas poderiam se localizar em um banco de dados de arquivo morto especial ou as linhas poderiam ser exportadas para um arquivo de texto ou serem, simplesmente, excluídas. Este exemplo só move as linhas para as novas tabelas do banco de dados msdb. Em uso de produção, convém adicionar outras verificações de erros e enviar uma mensagem de email aos operadores, caso o trabalho falhe.

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

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.

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.

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.

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.

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 Banco de dados, selecione msdb.

  5. 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 ;

  6. Clique em OK para salvar a etapa.

Para agendar o trabalho para execução no início de cada mês

  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 Freqüência, selecione as opções para executar o trabalho no primeiro dia de cada mês.

  6. Na área Freqüência diária, selecione Ocorre uma vez às 3:00:00 AM.

  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.

Segurança

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