Dela via

Skapa ett SQL Server-agentjobb för att arkivera databasmeddelanden och händelseloggar

gäller för:SQL ServerAzure SQL Managed Instance

Kopior av Databas-e-postmeddelanden och deras bifogade filer behålls i msdb tabeller tillsammans med händelseloggen Database Mail. Med jämna mellanrum kanske du vill minska storleken på tabeller och arkivera meddelanden och händelser som inte längre behövs.

Följande procedurer skapar ett SQL Server Agent-jobb för att automatisera processen.

Förutsättningar

Om du vill köra T-SQL-kommandon på SQL Server-instansen använder du SQL Server Management Studio (SSMS),MSSQL-tillägget för Visual Studio Code, sqlcmd eller ditt favoritverktyg för T-SQL-frågor.

Rekommendationer

Överväg att felkontrollera och övervaka det här jobbet för att skicka ett e-postmeddelande till operatorerna om det här arkivjobbet misslyckas.

Du kan också flytta arkiverade Database Mail-data till en anpassad arkivdatabas utanför msdbeller exportera dem från SQL Server.

Behörigheter

Du måste vara medlem i sysadmin fast serverroll för att kunna köra de lagrade procedurer som beskrivs i det här avsnittet.

Skapa ett arkivdatabas-e-postjobb

Den första proceduren skapar ett jobb med namnet Archive Database Mail med följande steg.

  1. Kopiera alla meddelanden från Database Mail-tabellerna till en ny tabell med namnet efter föregående månad i formatet DBMailArchive__<year_month>.

  2. Kopiera de bifogade filer som är relaterade till de meddelanden som kopieras i det första steget, från databasposttabellerna till en ny tabell med namnet efter föregående månad i formatet DBMailArchive_Attachments_<year_month>.

  3. Kopiera händelserna från händelseloggen Database Mail som är relaterade till de meddelanden som kopieras i det första steget, från Databasposttabellerna till en ny tabell med namnet efter föregående månad i formatet DBMailArchive_Log_<year_month>.

  4. Ta bort posterna för de överförda e-postobjekten från databasposttabellerna.

  5. Ta bort de händelser som är relaterade till de överförda e-postobjekten från händelseloggen Database Mail.

  6. Schemalägg jobbet så att det körs regelbundet.

Skapa ett SQL Server Agent-jobb

Följande steg använder SQL Server Management Studio (SSMS). Ladda ned den senaste versionen av SSMS på aka.ms/ssms.

  1. Anslut till SQL Server-instansen.

  2. I Object Explorer expanderar du SQL Server Agent, högerklickar på Jobb och väljer sedan Nytt jobb.

  3. I dialogrutan Nytt jobb skriver du Arkivdatabasposti rutan Namn .

  4. I rutan Ägare kontrollerar du att ägaren är medlem i sysadmin fasta serverrollen.

  5. I rutan Kategori väljer du Databasunderhåll.

  6. I rutan Beskrivning skriver du Arkivera databas-e-postmeddelanden och väljer sedan Steg.

Skapa ett jobbsteg för att arkivera databas-e-postmeddelanden

  1. På sidan Steg väljer du Nytt.

  2. I rutan Stegnamn skriver du Kopiera databasens e-postobjekt.

  3. I rutan Typ väljer du Transact-SQL skript (T-SQL).

  4. I rutan Databas väljer du msdb.

  5. I rutan Kommando skriver du följande T-SQL-instruktion för att skapa en tabell med namnet efter föregående månad, som innehåller rader som är äldre än början av den aktuella månaden.

    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. Spara steget genom att välja OK .

Skapa ett jobbsteg för att arkivera bifogade filer i Database Mail

  1. På sidan Steg väljer du Nytt.

  2. I rutan Stegnamn skriver du Kopiera databasens e-postbilagor.

  3. I rutan Typ väljer du Transact-SQL skript (T-SQL).

  4. I rutan Databas väljer du msdb.

  5. I rutan Kommando skriver du följande instruktion för att skapa en tabell med bifogade filer med namnet efter föregående månad, som innehåller de bifogade filer som motsvarar de meddelanden som överfördes i föregående steg:

    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. Spara steget genom att välja OK .

Skapa ett jobbsteg för att arkivera Database Mail-loggen

  1. På sidan Steg väljer du Nytt.

  2. I rutan Stegnamn skriver du Kopiera databaspostloggen.

  3. I rutan Typ väljer du Transact-SQL skript (T-SQL).

  4. I rutan Databas väljer du msdb.

  5. I rutan Kommando skriver du följande instruktion för att skapa en loggtabell med namnet efter föregående månad, som innehåller loggposterna som motsvarar de meddelanden som överfördes i föregående steg:

    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. Spara steget genom att välja OK .

Skapa ett jobbsteg för att ta bort de arkiverade raderna från Database Mail

  1. På sidan Steg väljer du Nytt.

  2. I rutan Stegnamn skriver du Ta bort rader från Database Mail-.

  3. I rutan Typ väljer du Transact-SQL skript (T-SQL).

  4. I rutan Databas väljer du msdb.

  5. I rutan Kommando skriver du följande instruktion för att ta bort rader som är äldre än den aktuella månaden från Database Mail-tabellerna:

    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. Spara steget genom att välja OK .

Skapa ett jobbsteg för att ta bort arkiverade objekt från Database Mail-händelseloggen

  1. På sidan Steg väljer du Nytt.

  2. I rutan Stegnamn skriver du Ta bort rader från Database Mail-händelseloggen.

  3. I rutan Typ väljer du Transact-SQL skript (T-SQL).

  4. I rutan Kommando skriver du följande instruktion för att ta bort rader som är äldre än den aktuella månaden från händelseloggen för 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. Spara steget genom att välja OK .

Schemalägg jobbet så att det körs regelbundet

  1. I dialogrutan Nytt jobb väljer du Scheman.

  2. På sidan Scheman väljer du Ny.

  3. I rutan Namn skriver du Archive Database Mail.

  4. I rutan Schematyp väljer du Återkommande.

  5. I området Frekvens väljer du alternativen för att köra jobbet regelbundet, till exempel en gång i månaden.

  6. I området Daglig frekvens väljer du Inträffar en gång vid <tid>.

  7. Kontrollera att de andra alternativen har konfigurerats som du vill och välj sedan OK för att spara schemat.

  8. Välj OK för att spara jobbet.

Relaterat innehåll

  • Last updated on