• Artikel

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

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 löschen, die nicht mehr benötigt werden. Die folgenden Prozeduren erstellen einen Auftrag des SQL Server-Agents, um diesen Prozess zu automatisieren.

  1. Die erste Prozedur erstellt den Auftrag Datenbank-E-Mail archivieren in sechs Schritten.

  2. Im ersten Schritt werden alle Nachrichten aus den Datenbank-E-Mail-Tabellen in eine neue Tabelle kopiert, die nach dem vorhergehenden Monat im Format DBMailArchive_<Jahr_Monat> benannt wird.

  3. Im zweiten Schritt werden die zu den im ersten Schritt kopierten Nachrichten gehörenden Anlagen aus den Datenbank-E-Mail-Tabellen in eine neue Tabelle kopiert, die nach dem vorhergehenden Monat im Format DBMailArchive_Attachments_<Jahr_Monat> benannt wird.

  4. Im dritten Schritt werden die zu den im ersten Schritt kopierten Nachrichten gehörenden Ereignisse aus dem Datenbank-E-Mail-Ereignisprotokoll aus den Datenbank-E-Mail-Tabellen in eine neue Tabelle kopiert, die nach dem vorhergehenden Monat im Format DBMailArchive_Log_<Jahr_Monat> benannt wird.

  5. Im vierten Schritt werden die Datensätze der übertragenen E-Mail-Elemente aus den Datenbank-E-Mail-Tabellen gelöscht.

  6. Im fünften Schritt werden die zu den übertragenen E-Mail-Elementen gehörenden Ereignisse aus dem Datenbank-E-Mail-Ereignisprotokoll gelöscht.

  7. Im letzten Schritt wird die Auftragsausführung für den Beginn jeden Monats geplant.

In diesem Beispiel werden die Archivtabellen in der msdb-Datenbank erstellt. Um die Größe der msdb-Datenbank zu reduzieren, können neue Tabellen in einer speziellen Archivdatenbank gespeichert werden, oder die Zeilen können in eine Textdatei exportiert oder einfach gelöscht werden. In diesem Beispiel werden nur die Zeilen in neue Tabellen in der msdb-Datenbank verschoben. 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.

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

  1. Erweitern Sie im Objekt-Explorer den Knoten 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 archivieren ein.

  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 archivieren ein, und klicken Sie dann auf Schritte.

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 kopieren ein.

  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.

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 kopieren ein.

  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.

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 kopieren ein.

  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.

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 entfernen ein.

  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.

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 entfernen ein.

  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 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 ;

  6. Klicken Sie auf OK, um den Schritt zu speichern.

So planen Sie die Auftragsausführung für den Beginn eines jeden Monats

  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 archivieren ein.

  4. Klicken Sie im Feld Zeitplantyp auf Wiederholt.

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

  6. Klicken Sie im Bereich Häufigkeit pro Tag auf Wird einmal um 3:00:00 Uhr ausgeführt.

  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.

Sicherheit

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