Delen via

Een SQL Server Agent-taak maken om database-e-mailberichten en gebeurtenislogboeken te archiveren

  • Artikel

van toepassing op:SQL ServerAzure SQL Managed Instance

Kopieën van Database Mail-berichten en de bijbehorende bijlagen worden bewaard in msdb tabellen, samen met het gebeurtenislogboek van Database Mail. Mogelijk wilt u de grootte van de tabellen verkleinen en berichten en gebeurtenissen archiveren die niet meer nodig zijn. Met de volgende procedures maakt u een SQL Server Agent-taak om het proces te automatiseren.

Voordat u begint

Voorwaarden

De nieuwe tabellen voor het opslaan van de archiefgegevens bevinden zich mogelijk in een speciale archiefdatabase. De rijen kunnen ook worden geëxporteerd naar een tekstbestand.

Aanbevelingen

In uw productieomgeving wilt u mogelijk extra foutcontrole toevoegen en een e-mailbericht verzenden naar operators als de taak mislukt.

Machtigingen

U moet lid zijn van de sysadmin vaste serverfunctie om de opgeslagen procedures uit te voeren die in dit onderwerp worden beschreven.

Overzicht van het proces

  • Met de eerste procedure maakt u een taak met de naam Archive Database Mail met de volgende stappen.

    1. Kopieer alle berichten uit de Database Mail-tabellen naar een nieuwe tabel met de naam van de vorige maand in de indeling DBMailArchive_<year_month>.

    2. Kopieer de bijlagen met betrekking tot de berichten die in de eerste stap zijn gekopieerd, van de Database Mail-tabellen naar een nieuwe tabel met de naam van de vorige maand in de indeling DBMailArchive_Attachments_<year_month>.

    3. Kopieer de gebeurtenissen uit het gebeurtenislogboek database-mail die zijn gerelateerd aan de berichten die in de eerste stap zijn gekopieerd, van de Database Mail-tabellen naar een nieuwe tabel die is genoemd na de vorige maand in de indeling DBMailArchive_Log_<year_month>.

    4. Verwijder de records van de overgedragen e-mailitems uit de database-mailtabellen.

    5. Verwijder de gebeurtenissen met betrekking tot de overgedragen e-mailitems uit het gebeurtenislogboek Database Mail.

  • Plan de taak periodiek uit te voeren.

Een SQL Server Agent-taak maken

  1. Vouw in Objectverkenner SQL Server Agent uit, klik met de rechtermuisknop op Takenen klik vervolgens op Nieuwe Taak.

  2. Typ in het dialoogvenster Nieuwe baan in het vak NaamArchiveer Database Mail.

  3. Controleer in het vak Eigenaar of de eigenaar lid is van de sysadmin vaste serverfunctie.

  4. Klik in het vak Categorie op de Databaseonderhoud.

  5. Typ in het vak BeschrijvingDatabase-e-mailberichten archiverenen klik vervolgens op Stappen.

Overzicht

Een stap maken om de database-e-mailberichten te archiveren

  1. Klik op de Stappen pagina op Nieuwe.

  2. Typ in het vak Stapnaam de tekst Database-e-mailitems kopiëren.

  3. Selecteer in het veld TypeTransact-SQL script (T-SQL).

  4. In het vak Database selecteer msdb.

  5. Typ in het vak Opdracht de volgende instructie om een tabel te maken met de naam van de vorige maand, met rijen die ouder zijn dan het begin van de huidige maand:

    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. Klik op OK- om de stap op te slaan.

Overzicht

Een stap maken om de Database Mail-bijlagen te archiveren

  1. Op de pagina stappen, klik op Nieuw.

  2. Typ in het vak StapnaamDatabase-e-mailbijlagen kopiëren.

  3. Selecteer in het vak TypeTransact-SQL script (T-SQL).

  4. Selecteer in het vak Databasemsdb.

  5. Typ in het vak Opdracht de volgende instructie om een tabel met bijlagen te maken met de naam van de vorige maand, met daarin de bijlagen die overeenkomen met de berichten die in de vorige stap zijn overgebracht:

    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. Klik op OK- om de stap op te slaan.

Overzicht

Een stap maken om het Database Mail-logboek te archiveren

  1. Op de pagina Stappen, klik op Nieuwe.

  2. Typ in het vak StapnaamDatabase-e-maillogboek kopiëren.

  3. Selecteer in het vak Type het Transact-SQL script (T-SQL).

  4. In het vak Database, selecteer msdb.

  5. Typ in het vak Opdracht de volgende instructie om een logboektabel te maken met de naam van de vorige maand, met daarin de logboekvermeldingen die overeenkomen met de berichten die in de vorige stap zijn overgebracht:

    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. Klik op OK- om de stap op te slaan.

Overzicht

Een stap maken voor het verwijderen van de gearchiveerde rijen uit Database Mail

  1. Klik op de pagina stappen op Nieuwe.

  2. Typ in het vak StapnaamRijen verwijderen uit Database Mail.

  3. Selecteer in het vak TypeTransact-SQL script (T-SQL).

  4. Selecteer in het vak Databasemsdb.

  5. Typ in het vak Opdracht de volgende instructie om rijen te verwijderen die ouder zijn dan de huidige maand uit de database-mailtabellen:

    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. Klik op OK- om de stap op te slaan.

Overzicht

Een stap maken om de gearchiveerde items te verwijderen uit het gebeurtenislogboek van Database Mail

  1. Klik op de pagina Stappen op Nieuw.

  2. Typ in het vak StapnaamRijen verwijderen uit het gebeurtenislogboek van Database Mail.

  3. Selecteer in het vak TypeTransact-SQL script (T-SQL).

  4. Typ in het vak Opdracht de volgende instructie om rijen te verwijderen die ouder zijn dan de huidige maand uit het gebeurtenislogboek van 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. Klik op OK- om de stap op te slaan.

Overzicht

Om de taak periodiek in te plannen

  1. Klik in het dialoogvenster Nieuwe taak op Planningen.

  2. Klik op de pagina Planningen op Nieuwe.

  3. Typ in het Naam veld Archive Database Mail.

  4. Selecteer in het vak Planningstype de optie Terugkerende.

  5. Selecteer in het gebied Frequentie de opties om de taak periodiek uit te voeren, bijvoorbeeld één keer per maand.

  6. Kies in het veld Dagelijkse frequentieEenmalig op <tijd>.

  7. Controleer of de andere opties naar wens zijn geconfigureerd en klik vervolgens op OK om de planning op te slaan.

  8. Klik op OK- om de taak op te slaan.

Overzicht