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.

• Innan du börjar: , Krav, rekommendationer, behörigheter

• Att arkivera databas-e-postmeddelanden och loggar med :SQL Server Agent

Innan du börjar

Förutsättningar

De nya tabellerna som ska lagra arkivdata kan finnas i en särskild arkivdatabas. Alternativt kan raderna exporteras till en textfil.

Rekommendationer

I produktionsmiljön kanske du vill lägga till ytterligare felkontroll och skicka ett e-postmeddelande till operatorerna om jobbet misslyckas.

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.

Översikt över processen

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

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

Så här skapar du ett SQL Server Agent-jobb

1. I Object Explorer expanderar du SQL Server Agent, högerklickar på Jobboch klickar sedan på Nytt jobb.

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

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

4. I rutan Kategori klickar du på Databasunderhåll.

5. I rutan Beskrivning skriver du arkivera databasens e-postmeddelandenoch klickar sedan på Steg.

Översikt

Så här skapar du ett steg för att arkivera databas-e-postmeddelanden

1. På sidan Steg klickar du på Ny.

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 Database väljer du msdb.

5. I rutan Kommando skriver du följande 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. Klicka på OK för att spara steget.

Översikt

Så här skapar du ett steg för att arkivera bifogade filer i Database Mail

1. På sidan Steg klickar du på Ny.

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 Database 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. Klicka på OK för att spara steget.

Översikt

Så här skapar du ett steg för att arkivera Database Mail-loggen

1. På sidan Steg klickar du på Ny.

2. I rutan Stegnamn skriver du Kopiera databaspostloggen.

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

4. I rutan Database 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. Klicka på OK för att spara steget.

Översikt

Så här skapar du ett steg för att ta bort de arkiverade raderna från Database Mail

1. På sidan Steg klickar du på Ny.

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 Database 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. Klicka på OK för att spara steget.

Översikt

Så här skapar du ett steg för att ta bort arkiverade objekt från Database Mail-händelseloggen

1. På sidan Steg klickar du på Ny.

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. Klicka på OK för att spara steget.

Översikt

Så här schemalägger du jobbet så att det körs regelbundet

1. I dialogrutan Nytt jobb klickar du på Scheman.

2. På sidan Schemalägger klickar du på 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 klicka sedan på OK för att spara schemat.

8. Klicka på OK för att spara jobbet.

Översikt