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

2025-05-20

van toepassing op:SQL Server Azure 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.

Voorwaarden

Als u T-SQL-opdrachten wilt uitvoeren op uw SQL Server-exemplaar, gebruikt u SQL Server Management Studio (SSMS), de MSSQL-extensie voor Visual Studio Code, sqlcmd of uw favoriete hulpprogramma voor T-SQL-query's.

Aanbevelingen

Overweeg foutcontrole en controleer deze taak om een e-mailbericht te verzenden naar operators als deze archieftaak mislukt.

Desgewenst kunt u gearchiveerde Database Mail-gegevens verplaatsen naar een aangepaste archiefdatabase buiten msdbof exporteren vanuit SQL Server.

Machtigingen

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

Een archiefdatabase-e-mailtaak maken

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

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>.
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>.
Kopieer de gebeurtenissen uit het gebeurtenislogboek van Database Mail die zijn gerelateerd aan 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_Log_<year_month>.
Verwijder de records van de overgedragen e-mailitems uit de database-mailtabellen.
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

In de volgende stappen wordt SQL Server Management Studio (SSMS) gebruikt. Download de nieuwste versie van SSMS op aka.ms/ssms.

Maak verbinding met het SQL Server-exemplaar.
Vouw in Objectverkenner SQL Server Agent uit, klik met de rechtermuisknop op Taken en selecteer Nieuwe taak.
Typ in het dialoogvenster Nieuwe baan in het vak NaamArchiveer Database Mail.
Controleer in het vak Eigenaar of de eigenaar lid is van de sysadmin vaste serverfunctie.
Selecteer in het vak Categorie het databaseonderhoud.
Typ in het vak Beschrijvinge-mailberichten van archiefdatabase en selecteer vervolgens Stappen.

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

Selecteer Nieuw op de pagina Stappen.
Typ in het vak Stapnaam de tekst Database-e-mailitems kopiëren.
Selecteer in het veld TypeTransact-SQL script (T-SQL).
Selecteer in het vak Database de optie msdb.

Typ in het opdrachtvak de volgende T-SQL-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 ;

Selecteer OK om de stap op te slaan.

Een taakstap maken om de Database Mail-bijlagen te archiveren

Selecteer Nieuw op de pagina Stappen.
Typ in het vak StapnaamDatabase-e-mailbijlagen kopiëren.
Selecteer in het veld TypeTransact-SQL script (T-SQL).
Selecteer in het vak Database de optie msdb.

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 ;

Selecteer OK om de stap op te slaan.

Een taakstap maken om het Database Mail-logboek te archiveren

Selecteer Nieuw op de pagina Stappen.
Typ in het vak StapnaamDatabase-e-maillogboek kopiëren.
Selecteer in het veld TypeTransact-SQL script (T-SQL).
Selecteer in het vak Database de optie msdb.

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 ;

Selecteer OK om de stap op te slaan.

Een taakstap maken om de gearchiveerde rijen uit Database Mail te verwijderen

Selecteer Nieuw op de pagina Stappen.
Typ in het vak StapnaamRijen verwijderen uit Database Mail.
Selecteer in het veld TypeTransact-SQL script (T-SQL).
Selecteer in het vak Database de optie msdb.

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 ;

Selecteer OK om de stap op te slaan.

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

Selecteer Nieuw op de pagina Stappen.
Typ in het vak StapnaamVerwijder rijen uit het gebeurtenislogboek van Database Mail.
Selecteer in het veld TypeTransact-SQL script (T-SQL).

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 ;

Selecteer OK om de stap op te slaan.

Plan de taak om periodiek uit te voeren

Selecteer Planningen in het dialoogvenster Nieuwe taak.
Selecteer Nieuw op de pagina Planningen.
Typ in het Naam veld Archive Database Mail.
Selecteer in het vak Planningstype de optie Terugkerende.
Selecteer in het gebied Frequentie de opties om de taak periodiek uit te voeren, bijvoorbeeld één keer per maand.
Kies in het veld Dagelijkse frequentieEenmalig op <tijd>.
Controleer of de andere opties zijn geconfigureerd zoals u wilt en selecteer VERVOLGENS OK om de planning op te slaan.
Kies OK om de taak op te slaan.

algemene stappen voor het oplossen van problemen met database-e-mail

Feedback

Is deze pagina nuttig?