Freigeben über

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

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 aufbewahrt. 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.

Voraussetzungen

Verwenden Sie ZUM Ausführen von T-SQL-Befehlen auf Ihrer SQL Server-Instanz SQL Server Management Studio (SSMS), die MSSQL-Erweiterung für Visual Studio Code, sqlcmd oder Ihr bevorzugtes T-SQL-Abfragetool.

Empfehlungen

Erwägen Sie die Fehlerüberprüfung, und überwachen Sie diesen Auftrag, um eine E-Mail-Nachricht an Operatoren zu senden, wenn dieser Archivauftrag fehlschlägt.

Optional können Sie archivierte Datenbank-E-Mail-Daten in eine benutzerdefinierte Archivdatenbank außerhalb von msdboder aus SQL Server exportieren.

Berechtigungen

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

Erstellen eines Archivdatenbank-E-Mail-Auftrags

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 im Format DBMailArchive__<year_month>nach dem vorherigen Monat 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 ist.

  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-E-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.

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

Erstellen eines Auftrags für SQL Server-Agent

In den folgenden Schritten wird hierzu SQL Server Management Studio (SSMS) verwendet: Laden Sie die neueste Version von SSMS unter aka.ms/ssms herunter.

  1. Stellen Sie eine Verbindung mit der SQL Server-Instanz her.

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

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

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

  5. Wählen Sie die Kategorie im Feld Datenbankwartung aus.

  6. Geben Sie im Feld "Beschreibung " "Archivdatenbank-E-Mail-Nachrichten" ein, und wählen Sie dann "Schritte" aus.

Jobschritt erstellen, um Datenbank-E-Mail-Nachrichten zu archivieren

  1. Wählen Sie auf der Seite "Schritte " die Option "Neu" aus.

  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. Wählen Sie im Feld "Datenbank " die Option msdbaus.

  5. Geben Sie im Feld "Command " die folgende T-SQL-Anweisung ein, um eine Tabelle zu erstellen, die nach dem vorherigen Monat benannt ist und Zeilen enthält, die älter als der Anfang des aktuellen Monats sind.

    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. Wählen Sie "OK" aus, um den Schritt zu speichern.

Erstellen eines Schrittes im Auftrag zum Archivieren der Datenbank-Mail-Anhänge

  1. Wählen Sie auf der Seite "Schritte " die Option "Neu" aus.

  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. Wählen Sie im Feld "Datenbank " die Option msdbaus.

  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. Wählen Sie "OK" aus, um den Schritt zu speichern.

Erstellen Sie einen Auftragsschritt zum Archivieren des Datenbank-E-Mail-Protokolls

  1. Wählen Sie auf der Seite "Schritte " die Option "Neu" aus.

  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. Wählen Sie im Feld "Datenbank " die Option msdbaus.

  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. Wählen Sie "OK" aus, um den Schritt zu speichern.

Erstellen eines Auftragsschritts zum Entfernen der archivierten Zeilen aus Datenbank-E-Mail

  1. Wählen Sie auf der Seite "Schritte " die Option "Neu" aus.

  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. Wählen Sie im Feld "Datenbank " die Option msdbaus.

  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. Wählen Sie "OK" aus, um den Schritt zu speichern.

Erstellen eines Auftragsschritts zum Entfernen der archivierten Elemente aus dem Datenbank-E-Mail-Ereignisprotokoll

  1. Wählen Sie auf der Seite "Schritte " die Option "Neu" aus.

  2. Geben Sie im Feld Schrittname den Text Zeilen aus dem Datenbank-E-Mail-Ereignisprotokoll entfernen ein.

  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. Wählen Sie "OK" aus, um den Schritt zu speichern.

Planen der Ausführung des Auftrags in regelmäßigen Abständen

  1. Wählen Sie im Dialogfeld "Neuer Auftrag " die Option "Zeitpläne" aus.

  2. Wählen Sie auf der Seite "Zeitpläne " die Option "Neu" aus.

  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. Klicken Sie im Bereich Häufigkeit pro Tag auf Wird einmal um <Uhrzeit> ausgeführt.

  7. Vergewissern Sie sich, dass die anderen Optionen wie gewünscht konfiguriert sind, und wählen Sie dann "OK " aus, um den Zeitplan zu speichern.

  8. Wählen Sie OK aus, um den Auftrag zu speichern.

Verwandte Inhalte