Condividi tramite

Creare un processo di SQL Server Agent per l'archiviazione dei messaggi di Posta elettronica del database e dei log degli eventi.

Si applica a:SQL ServerIstanza gestita di SQL di Azure

Le copie dei messaggi di Posta elettronica database e dei relativi allegati vengono mantenute in msdb tabelle insieme al registro eventi di Posta elettronica database. È consigliabile ridurre periodicamente le dimensioni delle tabelle e archiviare 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.

Prerequisiti

Per eseguire comandi T-SQL nell'istanza di SQL Server, usare SQL Server Management Studio (SSMS),l'estensione MSSQL per Visual Studio Code, sqlcmd o lo strumento di query T-SQL preferito.

Consigli

Prendere in considerazione il controllo degli errori e monitorare questo processo per inviare un messaggio di posta elettronica agli operatori in caso di errore del processo di archiviazione.

Facoltativamente, è possibile spostare i dati di Posta elettronica database archiviati in un database archivio personalizzato all'esterno di msdbo esportarli da SQL Server.

Autorizzazioni

Per eseguire le stored procedure descritte in questo argomento, è necessario essere membri del ruolo predefinito del server sysadmin.

Creare un processo Database Mail di archivio.

La prima procedura consente di creare un processo denominato Archive Database Mail effettuando i passaggi riportati di seguito.

  1. Copiare tutti i messaggi dalle tabelle di Posta elettronica database in una nuova tabella denominata dopo il mese precedente, nel formato DBMailArchive__<year_month>.

  2. Copiare gli allegati correlati ai messaggi copiati nel primo passaggio, dalle tabelle di Posta elettronica database a una nuova tabella denominata dopo il mese precedente nel formato DBMailArchive_Attachments_<year_month>.

  3. Copiare gli eventi dal registro eventi di Posta elettronica database correlati ai messaggi copiati nel primo passaggio, dalle tabelle di Posta elettronica database a una nuova tabella denominata dopo il mese precedente nel formato DBMailArchive_Log_<year_month>.

  4. Eliminare dalle tabelle di Posta elettronica database i record degli elementi di posta trasferiti.

  5. Eliminare dal log eventi di Posta elettronica database gli eventi correlati agli elementi di posta trasferiti.

  6. Pianifica l'esecuzione periodica del lavoro.

Creare un processo di SQL Server Agent

La procedura seguente usa SQL Server Management Studio (SSMS). Scaricare la versione più recente di SSMS all'indirizzo aka.ms/ssms.

  1. Eseguire la connessione all'istanza di SQL Server.

  2. In Esplora oggetti, espandere SQL Server Agent, fare clic con il pulsante destro del mouse su Jobs e quindi scegliere Nuovo processo di lavoro.

  3. Nella finestra di dialogo Nuovo processo digitare Archive Database Mail nella casella Nome.

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

  5. Nella casella Categoria selezionare Manutenzione database.

  6. Nella casella Descrizione digitare Archivia messaggi database di posta elettronica e quindi selezionare Fasi.

Creare un passaggio del processo per archiviare i messaggi della Posta Elettronica di Database

  1. Nella pagina Passaggi selezionare Nuovo.

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

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

  4. Nella casella Database selezionare msdb.

  5. Nella casella Comando digitare l'istruzione T-SQL seguente per creare una tabella denominata dopo il mese precedente, contenente righe precedenti 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. Selezionare OK per salvare il passaggio.

Creare una fase di lavoro per archiviare gli allegati di Database Mail

  1. Nella pagina Passaggi selezionare Nuovo.

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

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

  4. Nella casella Database selezionare 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:

    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. Selezionare OK per salvare il passaggio.

Creare una fase del processo per archiviare il log di Database Mail

  1. Nella pagina Passaggi selezionare 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. Selezionare OK per salvare il passaggio.

Creare una fase di lavoro per rimuovere le righe archiviate da Database Mail

  1. Nella pagina Passaggi selezionare Nuovo.

  2. Nella casella Nome passaggio, digitare Rimuovi righe da Database Mail.

  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. Selezionare OK per salvare il passaggio.

Creare una fase del task per eliminare gli elementi archiviati dal log degli eventi di Database Mail

  1. Nella pagina Passaggi selezionare Nuovo.

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

  3. Nella casella Tipo selezionare Script Transact-SQL (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:

    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. Selezionare OK per salvare il passaggio.

Pianificare l'esecuzione periodica del lavoro

  1. Nella finestra di dialogo Nuovo processo selezionare Pianificazioni.

  2. Nella pagina Pianificazioni selezionare Nuovo.

  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 periodicamente, ad esempio una volta al mese.

  6. Nell'area Frequenza giornaliera, selezionare Si verifica una volta all'<ora>.

  7. Verificare che le altre opzioni siano configurate come desiderate e quindi selezionare OK per salvare la pianificazione.

  8. Selezionare OK per salvare il lavoro.

Contenuti correlati

  • Last updated on