Condividi tramite

  • Articolo

Procedura: Creazione di un processo di SQL Server Agent per l'archiviazione di messaggi e log eventi di Posta elettronica database

Oltre al log eventi di Posta elettronica database, nelle tabelle del database msdb viene mantenuta una copia dei messaggi di Posta elettronica database e dei relativi allegati. È consigliabile ridurre periodicamente le dimensioni delle tabelle e rimuovere i messaggi e gli eventi non più necessari. Nelle procedure seguenti viene illustrato come creare un processo di SQL Server Agent per eseguire queste operazioni in modo automatico.

  1. La prima procedura consente di creare un processo denominato archiviazione di Posta elettronica database in quattro passaggi.

  2. Il primo passaggio consiste nel copiare tutti i messaggi delle tabelle di Posta elettronica database in una nuova tabella con un nome basato sul mese precedente nel formato DBMailArchive_<year_month>.

  3. Il secondo passaggio consiste nel copiare gli allegati correlati ai messaggi copiati nel primo passaggio dalle tabelle di Posta elettronica database in una nuova tabella con un nome basato sul mese precedente nel formato DBMailArchive_Attachments_<year_month>.

  4. Il terzo passaggio consiste nel copiare gli eventi del log eventi di Posta elettronica database correlati ai messaggi copiati nel primo passaggio dalle tabelle di Posta elettronica database in una nuova tabella con un nome basato sul mese precedente nel formato DBMailArchive_Log_<year_month>.

  5. Il quarto passaggio consiste nell'eliminare dalle tabelle di Posta elettronica database i record degli elementi di posta trasferiti.

  6. Il quinto passaggio consiste nell'eliminare dal log eventi di Posta elettronica database gli eventi correlati agli elementi di posta trasferiti.

  7. La procedura finale consente di pianificare il processo in modo che venga eseguito all'inizio di ogni mese.

Per questo esempio le tabelle di archiviazione vengono create nel database msdb. Per ridurre le dimensioni del database msdb, è possibile inserire le nuove tabelle in un database di archiviazione speciale oppure esportare in un file di testo o semplicemente eliminare le righe. In questo esempio le righe vengono spostate in nuove tabelle facenti parte del database msdb. In un ambiente di produzione è consigliabile aggiungere un ulteriore controllo degli errori e inviare un messaggio di posta elettronica agli operatori in caso di esito negativo del processo.

Per creare un processo di SQL Server Agent

  1. In Esplora oggetti espandere SQL Server Agent, fare clic con il pulsante destro del mouse su Processi e quindi scegliere Nuovo processo.

  2. Nella finestra di dialogo Nuovo processo digitare Archiviazione di Posta elettronica database nella casella Nome.

  3. Nella casella Proprietario confermare che il proprietario è un membro del ruolo predefinito del server sysadmin.

  4. Nella casella Categoria fare clic su Manutenzione database.

  5. Nella casella Descrizione digitare Archiviazione di messaggi di Posta elettronica database e quindi fare clic su Passaggi.

Per creare un passaggio per l'archiviazione dei messaggi di Posta elettronica database

  1. Nella pagina Passaggi fare clic su Nuovo.

  2. Nella casella Nome passaggio digitare Copia elementi di Posta elettronica database.

  3. Nella casella Tipo selezionare Script Transact-SQL (T-SQL).

  4. Nella casella Database selezionare msdb.

  5. Nella casella Comando digitare l'istruzione seguente per creare una tabella con un nome basato sul mese precedente e contenente le righe con una data anteriore all'inizio del mese corrente:

    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. Fare clic su OK per salvare il passaggio.

Per creare un passaggio per l'archiviazione degli allegati di Posta elettronica database

  1. Nella pagina Passaggi fare clic su Nuovo.

  2. Nella casella Nome passaggio digitare Copia allegati di Posta elettronica database.

  3. Nella casella Tipo selezionare Script Transact-SQL (T-SQL).

  4. Nella casella Database selezionare msdb.

  5. Nella casella Comando digitare l'istruzione seguente per creare una tabella degli allegati con un nome basato sul mese precedente e contenente gli allegati che corrispondono ai messaggi trasferiti nel passaggio precedente:

    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. Fare clic su OK per salvare il passaggio.

Per creare un passaggio per l'archiviazione del log di Posta elettronica database

  1. Nella pagina Passaggi fare clic su Nuovo.

  2. Nella casella Nome passaggio digitare Copy Database Mail Log.

  3. Nella casella Tipo selezionare Script Transact-SQL (T-SQL).

  4. Nella casella Database selezionare msdb.

  5. Nella casella Comando digitare l'istruzione seguente per creare una tabella del log con un nome basato sul mese precedente e contenente le voci del log che corrispondono ai messaggi trasferiti nel primo passaggio:

    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. Fare clic su OK per salvare il passaggio.

Per creare un passaggio per la rimozione da Posta elettronica database delle righe archiviate

  1. Nella pagina Passaggi fare clic su Nuovo.

  2. Nella casella Nome passaggio digitare Rimuovi righe da Posta elettronica database.

  3. Nella casella Tipo selezionare Script Transact-SQL (T-SQL).

  4. Nella casella Database selezionare msdb.

  5. Nella casella Comando digitare l'istruzione seguente per rimuovere dalle tabelle di Posta elettronica database le righe con una data anteriore al mese corrente:

    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. Fare clic su OK per salvare il passaggio.

Per creare un passaggio per la rimozione dal log eventi di Posta elettronica database degli elementi archiviati

  1. Nella pagina Passaggi fare clic su Nuovo.

  2. Nella casella Nome passaggio digitare Rimuovi righe dal log eventi di Posta elettronica database.

  3. Nella casella Tipo selezionare Script Transact-SQL (T-SQL).

  4. Nella casella Database selezionare msdb.

  5. Nella casella Comando digitare l'istruzione seguente per rimuovere dal log eventi di Posta elettronica database le righe con una data anteriore al mese corrente:

    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. Fare clic su OK per salvare il passaggio.

Per pianificare il processo per l'esecuzione all'inizio di ogni mese

  1. Nella finestra di dialogo Nuovo processo fare clic su Pianificazioni.

  2. Nella pagina Pianificazioni fare clic su Nuova.

  3. Nella casella Nome digitare Archive Database Mail.

  4. Nella casella Tipo pianificazione selezionare Periodica.

  5. Nell'area Frequenza selezionare le opzioni che consentono di eseguire il processo il primo giorno di ogni mese.

  6. Nell'area Frequenza giornaliera selezionare Una sola volta alle 15.00.00.

  7. Verificare che le altre opzioni siano configurate come desiderato e quindi fare clic su OK per salvare la pianificazione.

  8. Fare clic su OK per salvare il processo.

Protezione

È necessario essere membri del ruolo predefinito del server sysadmin per eseguire le stored procedure descritte in questo argomento.