Creazione di un processo di SQL Server Agent per l'archiviazione di messaggi e log eventi di Posta elettronica databaseCreate a SQL Server Agent Job to Archive Database Mail Messages and Event Logs

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.Copies of Database Mail messages and their attachments are retained in msdb tables along with the Database Mail event log. È consigliabile ridurre periodicamente le dimensioni delle tabelle e archiviare i messaggi e gli eventi non più necessari.Periodically you might want to reduce the size of the tables and archive messages and events that are no longer needed. Nelle procedure seguenti viene illustrato come creare un processo di SQL Server Agent per eseguire queste operazioni in modo automatico.The following procedures create a SQL Server Agent job to automate the process.

Prima di iniziare Before You Begin

Prerequisiti Prerequisites

Le nuove tabelle per archiviare i dati dell'archivio possono trovarsi in un database di archiviazione speciale.The new tables to store the archive data might be located in a special archive database. In alternativa le righe possono essere esportate in un file di testo.Alternatively the rows could be exported to a text file.

Raccomandazioni Recommendations

Nell'ambiente di produzione è consigliabile aggiungere un ulteriore controllo degli errori e inviare un messaggio di posta elettronica agli operatori se l'esecuzione del processo non viene completata.In your production environment, you might want to add additional error checking and send an e-mail message to operators if the job fails.

Autorizzazioni Permissions

È necessario essere membri del ruolo predefinito del server sysadmin per eseguire le stored procedure descritte in questo argomento.You must be a member of the sysadmin fixed server role to execute the stored procedures described in this topic.

Panoramica del processo Overview of the Process

  • La prima procedura consente di creare un processo denominato Archive Database Mail effettuando i passaggi riportati di seguito.The first procedure creates a job named Archive Database Mail with the following steps.

    1. Copiare tutti i messaggi dalle tabelle di Posta elettronica database in una nuova tabella con il nome basato sul mese precedente nel formato DBMailArchive_<anno_mese>.Copy all messages from the Database Mail tables to a new table named after the previous month in the format DBMailArchive_<year_month>.

    2. Copiare gli allegati correlati ai messaggi copiati nel primo passaggio dalle tabelle di Posta elettronica database in una nuova tabella con il nome basato sul mese precedente nel formato DBMailArchive_Attachments_<anno_mese>.Copy the attachments related to the messages copied in the first step, from the Database Mail tables to a new table named after the previous month in the format DBMailArchive_Attachments_<year_month>.

    3. Copiare gli eventi del registro 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_<anno_mese>.Copy the events from the Database Mail event log that are related to the messages copied in the first step, from the Database Mail tables to a new table named after the previous month in the format DBMailArchive_Log_<year_month>.

    4. Eliminare dalle tabelle di Posta elettronica database i record degli elementi di posta trasferiti.Delete the records of the transferred mail items from the Database Mail tables.

    5. Eliminare dal log eventi di Posta elettronica database gli eventi correlati agli elementi di posta trasferiti.Delete the events related to the transferred mail items from the Database Mail event log.

  • Pianificare l'esecuzione periodica del processo.Schedule the job to run periodically.

Per creare un processo di SQL Server AgentTo create a SQL Server Agent job

  1. In Esplora oggetti, espandere SQL ServerSQL Server Agent, fare clic con il pulsante destro del mouse su Processi, quindi scegliere Nuovo processo.In Object Explorer, expand SQL ServerSQL Server Agent, right-click Jobs, and then click New Job.

  2. Nella finestra di dialogo Nuovo processo digitare Archive Database Mail nella casella Nome.In the New Job dialog box, in the Name box, type Archive Database Mail.

  3. Nella casella Proprietario confermare che il proprietario è un membro del ruolo predefinito del server sysadmin .In the Owner box, confirm that the owner is a member of the sysadmin fixed server role.

  4. Nella casella Categoria fare clic su Manutenzione database.In the Category box, click the Database Maintenance.

  5. Nella casella Descrizione digitare Archiviazione messaggi di Posta elettronica database, quindi fare clic su Passaggi.In the Description box, type Archive Database Mail messages, and then click Steps.

    PanoramicaOverview

Per creare un passaggio per l'archiviazione dei messaggi di Posta elettronica databaseTo create a step to archive the Database Mail messages

  1. Nella pagina Passaggi fare clic su Nuovo.On the Steps page, click New.

  2. Nella casella Nome passaggio digitare Copy Database Mail Items.In the Step name box, type Copy Database Mail Items.

  3. Nella casella Tipo selezionare Script Transact-SQL (T-SQL).In the Type box, select Transact-SQL script (T-SQL).

  4. Nella casella Database selezionare msdb.In the Database box, select msdb.

  5. Nella casella Comando , digitare l'istruzione seguente per creare una tabella con il nome basato sul mese precedente e contenente le righe con una data anteriore all'inizio del mese corrente:In the Command box, type the following statement to create a table named after the previous month, containing rows older than the start of the current month:

    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.Click OK to save the step.

    PanoramicaOverview

Per creare un passaggio per l'archiviazione degli allegati di Posta elettronica databaseTo create a step to archive the Database Mail attachments

  1. Nella pagina Passaggi fare clic su Nuovo.On the Steps page, click New.

  2. Nella casella Nome passaggio digitare Copy Database Mail Attachments.In the Step name box, type Copy Database Mail Attachments.

  3. Nella casella Tipo selezionare Script Transact-SQL (T-SQL).In the Type box, select Transact-SQL script (T-SQL).

  4. Nella casella Database selezionare msdb.In the Database box, select msdb.

  5. Nella casella Comando , digitare la seguente istruzione per creare una tabella di allegati con il nome basato sul mese precedente e contenente gli allegati corrispondenti ai messaggi trasferiti nel passaggio precedente:In the Command box, type the following statement to create an attachments table named after the previous month, containing the attachments that correspond to the messages transferred in the previous step:

    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.Click OK to save the step.

    PanoramicaOverview

Per creare un passaggio per l'archiviazione del log di Posta elettronica databaseTo create a step to archive the Database Mail log

  1. Nella pagina Passaggi fare clic su Nuovo.On the Steps page, click New.

  2. Nella casella Nome passaggio digitare Copy Database Mail Log.In the Step name box, type Copy Database Mail Log.

  3. Nella casella Tipo selezionare Script Transact-SQL (T-SQL).In the Type box, select Transact-SQL script (T-SQL).

  4. Nella casella Database selezionare msdb.In the Database box, select 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:In the Command box, type the following statement to create a log table named after the previous month, containing the log entries that correspond to the messages transferred in the earlier step:

    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.Click OK to save the step.

    PanoramicaOverview

Per creare un passaggio per la rimozione da Posta elettronica database delle righe archiviateTo create a step to remove the archived rows from Database Mail

  1. Nella pagina Passaggi fare clic su Nuovo.On the Steps page, click New.

  2. Nella casella Nome passaggio digitare Remove rows from Database Mail.In the Step name box, type Remove rows from Database Mail.

  3. Nella casella Tipo selezionare Script Transact-SQL (T-SQL).In the Type box, select Transact-SQL script (T-SQL).

  4. Nella casella Database selezionare msdb.In the Database box, select 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:In the Command box, type the following statement to remove rows older than the current month from the Database Mail tables:

    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.Click OK to save the step.

    PanoramicaOverview

Per creare un passaggio per la rimozione dal log eventi di Posta elettronica database degli elementi archiviatiTo create a step to remove the archived items from Database Mail event log

  1. Nella pagina Passaggi fare clic su Nuovo.On the Steps page, click New.

  2. Nella casella Nome passaggio digitare Remove rows from Database Mail event log.In the Step Name box type Remove rows from Database Mail event log.

  3. Nella casella Tipo selezionare Script Transact-SQL (T-SQL).In the Type box, select Transact-SQL script (T-SQL).

  4. 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:In the Command box, type the following statement to remove rows older than the current month from the Database Mail event log:

    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. Fare clic su OK per salvare il passaggio.Click OK to save the step.

    PanoramicaOverview

Per pianificare l'esecuzione periodica del processoTo schedule the job to run periodically

  1. Nella finestra di dialogo Nuovo processo fare clic su Pianificazioni.In the New Job dialog box, click Schedules.

  2. Nella pagina Pianificazioni fare clic su Nuova.On the Schedules page, click New.

  3. Nella casella Nome digitare Archive Database Mail.In the Name box, type Archive Database Mail.

  4. Nella casella Tipo pianificazione selezionare Periodica.In the Schedule type box, select Recurring.

  5. Nell'area Frequenza selezionare le opzioni che consentono di eseguire il processo periodicamente, ad esempio una volta al mese.In the Frequency area, select the options to run the job periodically, for example once every month.

  6. Nell'area Frequenza giornaliera selezionare Una sola volta alle <ora>.In the Daily frequency area, select Occurs once at <time>.

  7. Verificare che le altre opzioni siano configurate come desiderato, quindi fare clic su OK per salvare la pianificazione.Verify that the other options are configured as you wish, and then click OK to save the schedule.

  8. Fare clic su OK per salvare il processo.Click OK to save the job.

    PanoramicaOverview