Erstellen eines Auftrags des SQL Server-Agents zum Archivieren von Datenbank-E-Mail-Nachrichten und Ereignisprotokollen

  • Artikel

Gilt für:SQL ServerAzure SQL Managed Instance

Kopien von Datenbank-E-Mail-Nachrichten und deren Anlagen werden zusammen mit dem Datenbank-E-Mail-Ereignisprotokoll in msdb -Tabellen gespeichert. Sie sollten die Größe der Tabellen regelmäßig reduzieren und Nachrichten und Ereignisse archivieren, die nicht mehr benötigt werden. Die folgenden Prozeduren erstellen einen Auftrag des SQL Server-Agents, um diesen Prozess zu automatisieren.

Vorbereitungen

Voraussetzungen

Die neuen Tabellen zum Speichern der Archivdaten können sich in einer speziellen Archivdatenbank befinden. Alternativ können die Zeilen in eine Textdatei exportiert werden.

Empfehlungen

Für die Verwendung in einer Produktionsumgebung sollten Sie eine zusätzliche Fehlerprüfung einfügen und eine E-Mail-Nachricht an Operatoren senden, wenn beim Auftrag ein Fehler auftritt.

Berechtigungen

Sie müssen Mitglied der festen Serverrolle sysadmin sein, um die in diesem Thema beschriebenen gespeicherten Prozeduren auszuführen.

Übersicht über den Prozess

  • Die erste Prozedur erstellt den Auftrag "Datenbank-E-Mail archivieren" in den folgenden Schritten:

    1. Kopieren Sie alle Nachrichten aus den Datenbank-E-Mail-Tabellen in eine neue Tabelle, die nach dem vorherigen Monat im Format DBMailArchive_<year_month> benannt ist.

    2. Kopieren Sie die Anlagen im Zusammenhang mit den Nachrichten, die im ersten Schritt kopiert wurden, aus den Datenbank-E-Mail-Tabellen in eine neue Tabelle, die nach dem vorherigen Monat im Format DBMailArchive_Attachments_<year_month benannt> wurde.

    3. Kopieren Sie die Ereignisse aus dem Datenbank-E-Mail-Ereignisprotokoll, die mit den im ersten Schritt kopierten Nachrichten verknüpft sind, aus den Datenbank-Mail-Tabellen in eine neue Tabelle, die nach dem vorherigen Monat im Format DBMailArchive_Log_<year_month> benannt ist.

    4. Löschen Sie die Datensätze der übertragenen E-Mail-Elemente aus den Datenbank-E-Mail-Tabellen.

    5. Löschen Sie die zu den übertragenen E-Mail-Elementen gehörenden Ereignisse aus dem Datenbank-E-Mail-Ereignisprotokoll.

  • Planen Sie die regelmäßige Ausführung des Auftrags.

So erstellen Sie einen Auftrag für den SQL Server-Agent

  1. Erweitern Sie im Objekt-Explorer den SQL Server-Agent, klicken Sie mit der rechten Maustaste auf "Aufträge", und klicken Sie dann auf "Neuer Auftrag".

  2. Geben Sie im Dialogfeld Neuer Auftrag im Feld Name den Namen Datenbank-E-Mail archivierenein.

  3. Bestätigen Sie im Feld Besitzer , dass der Besitzer Mitglied der festen Serverrolle sysadmin ist.

  4. Klicken Sie im Feld Kategorie auf Datenbankwartung.

  5. Geben Sie im Feld Beschreibung als Beschreibung Datenbank-E-Mail-Nachrichten archivierenein, und klicken Sie dann auf Schritte.

Übersicht

So erstellen Sie einen Schritt zum Archivieren der Datenbank-E-Mail-Nachrichten

  1. Klicken Sie auf der Seite Schritte auf Neu.

  2. Geben Sie im Feld Schrittname den Namen Datenbank-E-Mail-Elemente kopierenein.

  3. Klicken Sie im Feld Typ auf Transact-SQL-Skript (T-SQL).

  4. Klicken Sie im Feld Datenbank auf msdb.

  5. Geben Sie im Feld Befehl die folgende Anweisung zum Erstellen einer Tabelle ein, die nach dem vorhergehenden Monat benannt wird und Zeilen enthält, die aus der Zeit vor Beginn des aktuellen Monats stammen:

    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. Klicken Sie auf OK , um den Schritt zu speichern.

Übersicht

So erstellen Sie einen Schritt zum Archivieren der Datenbank-E-Mail-Anlagen

  1. Klicken Sie auf der Seite Schritte auf Neu.

  2. Geben Sie im Feld Schrittname den Namen Datenbank-E-Mail-Anlagen kopierenein.

  3. Klicken Sie im Feld Typ auf Transact-SQL-Skript (T-SQL).

  4. Klicken Sie im Feld Datenbank auf msdb.

  5. Geben Sie im Feld Befehl die folgende Anweisung zum Erstellen einer Tabelle mit Anlagen ein, die nach dem vorhergehenden Monat benannt wird und die Anlagen zu den im vorhergehenden Schritt übertragenen Nachrichten enthält:

    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. Klicken Sie auf OK , um den Schritt zu speichern.

Übersicht

So erstellen Sie einen Schritt zum Archivieren des Datenbank-E-Mail-Protokolls

  1. Klicken Sie auf der Seite Schritte auf Neu.

  2. Geben Sie im Feld Schrittname den Namen Datenbank-E-Mail-Protokoll kopierenein.

  3. Klicken Sie im Feld Typ auf Transact-SQL-Skript (T-SQL).

  4. Klicken Sie im Feld Datenbank auf msdb.

  5. Geben Sie im Feld Befehl die folgende Anweisung zum Erstellen einer Protokolltabelle ein, die nach dem vorhergehenden Monat benannt wird und die Protokolleinträge zu den in einem vorhergehenden Schritt übertragenen Nachrichten enthält:

    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. Klicken Sie auf OK , um den Schritt zu speichern.

Übersicht

So erstellen Sie einen Schritt zum Entfernen der archivierten Zeilen aus der Datenbank-E-Mail

  1. Klicken Sie auf der Seite Schritte auf Neu.

  2. Geben Sie im Feld Schrittname den Namen Zeilen aus Datenbank-E-Mail entfernenein.

  3. Klicken Sie im Feld Typ auf Transact-SQL-Skript (T-SQL).

  4. Klicken Sie im Feld Datenbank auf msdb.

  5. Geben Sie im Feld Befehl die folgende Anweisung ein, um Zeilen aus den Datenbank-E-Mail-Tabellen zu entfernen, die älter als der aktuelle Monat sind:

    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. Klicken Sie auf OK , um den Schritt zu speichern.

Übersicht

So erstellen Sie einen Schritt zum Entfernen der archivierten Elemente aus dem Datenbank-E-Mail-Ereignisprotokoll

  1. Klicken Sie auf der Seite Schritte auf Neu.

  2. Geben Sie im Feld Schrittname den Namen Zeilen aus dem Datenbank-E-Mail-Protokoll entfernenein.

  3. Klicken Sie im Feld Typ auf Transact-SQL-Skript (T-SQL).

  4. Geben Sie im Feld Befehl die folgende Anweisung ein, um Zeilen aus dem Datenbank-E-Mail-Ereignisprotokoll zu entfernen, die älter als der aktuelle Monat sind:

    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. Klicken Sie auf OK , um den Schritt zu speichern.

Übersicht

So planen Sie die regelmäßige Ausführung des Auftrags

  1. Klicken Sie im Dialogfeld Neuer Auftrag auf Zeitpläne.

  2. Klicken Sie auf der Seite Zeitpläne auf Neu.

  3. Geben Sie im Feld Name den Namen Datenbank-E-Mail archivierenein.

  4. Klicken Sie im Feld Zeitplantyp auf Wiederholt.

  5. Wählen Sie im Bereich Häufigkeit die Optionen zum regelmäßigen Ausführen des Auftrags, z. B. am ersten Tag eines jeden Monats, aus.

  6. Wählen Sie im Bereich "Tägliche Häufigkeit " die Option "Tritt einmal zur <Zeit>" aus.

  7. Überprüfen Sie, ob die anderen Optionen wie gewünscht konfiguriert sind, und klicken Sie dann auf OK , um den Zeitplan zu speichern.

  8. Klicken Sie auf OK , um den Auftrag zu speichern.

Übersicht