SQL Server-ügynökfeladat létrehozása adatbázis-üzenetek és eseménynaplók archiválásához

A következőkre vonatkozik:SQL ServerAzure SQL-kezelte példány

Az Adatbázispostal kapcsolatos üzenetek és mellékleteik másolatai a táblákban, valamint az Adatbázispostal kapcsolatos eseménynaplóban msdb is megmaradnak. Időnként érdemes lehet csökkenteni a már nem szükséges táblák és archív üzenetek és események méretét.

Az alábbi eljárások létrehoznak egy SQL Server Agent-feladatot a folyamat automatizálásához.

Előfeltételek

T-SQL-parancsok SQL Server-példányon való futtatásához használja az SQL Server Management Studiót (SSMS), a Visual Studio Code MSSQL-bővítményét, az sqlcmd-et vagy a kedvenc T-SQL-lekérdezési eszközét.

Ajánlások

Fontolja meg a feladat hibaellenőrzését és monitorozását, hogy e-mailt küldjön az operátoroknak, ha ez az archív feladat meghiúsul.

Igény szerint áthelyezheti az archivált Database Mail-adatokat egy egyéni archív adatbázisba kívülre msdb, vagy exportálhatja őket az SQL Serverről.

Engedélyek

A jelen témakörben ismertetett tárolt eljárások végrehajtásához a sysadmin rögzített kiszolgálói szerepkör tagjának kell lennie.

Archiválási adatbázis levelezési feladatának létrehozása

Az első eljárás létrehoz egy Archive Database Mail nevű feladatot az alábbi lépésekkel.

1. Másolja az adatbázis levelezési tábláinak összes üzenetét egy, az előző hónapról elnevezett új táblába, a formátumba DBMailArchive__<year_month>.

2. Másolja az első lépésben másolt üzenetekhez kapcsolódó mellékleteket az Adatbázisposta táblákból egy új, az előző hónap után elnevezett táblába.DBMailArchive_Attachments_<year_month>

3. Másolja a Database Mail eseménynaplójából az első lépésben másolt üzenetekhez kapcsolódó eseményeket az Adatbázisposta táblákból egy új, az előző hónap után elnevezett táblába.DBMailArchive_Log_<year_month>

4. Törölje az átvitt e-mailek rekordjait az Adatbázisposta táblákból.

5. Törölje az átvitt levelekhez kapcsolódó eseményeket az Adatbázisposta eseménynaplójából.

6. Ütemezze a feladatot időszakos futtatásra.

SQL Server Agent-feladat létrehozása

Az alábbi lépések az SQL Server Management Studiót (SSMS) használják. Töltse le az SSMS legújabb verzióját a aka.ms/ssms.

1. Csatlakozzon az SQL Server-példányhoz.

2. Az Object Explorerben bontsa ki az SQL Server-ügynököt, kattintson a jobb gombbal a Feladatok elemre, majd válassza az Új feladat lehetőséget.

3. Az Új állás párbeszédpanel Név mezőjébe írja be Archiválási adatbázis levelezés.

4. A Tulajdonos mezőben ellenőrizze, hogy a tulajdonos tagja-e a sysadmin rögzített kiszolgálói szerepkörnek.

5. A Kategória mezőben válassza ki az adatbázis-karbantartást.

6. A Leírás mezőbe írja be az Archiválás adatbázis e-mailjeinek szövegét, majd válassza a Lépések lehetőséget.

Feladatlépés létrehozása az Adatbázis-levelek üzeneteinek archiválásához

1. A Lépések lapon válassza az Új lehetőséget.

2. A Lépésnév mezőbe írja be: Másolja az adatbázis levelezési elemeit.

3. A Típus mezőben válassza Transact-SQL szkript (T-SQL).

4. Az Adatbázis mezőben válassza a lehetőséget msdb.

5. A Parancs mezőbe írja be a következő T-SQL-utasítást az előző hónapról elnevezett táblázat létrehozásához, amely az aktuális hónap kezdeténél régebbi sorokat tartalmaz.

   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. A lépés mentéséhez kattintson az OK gombra .

Feladatlépés létrehozása az Adatbázisposta mellékleteinek archiválásához

1. A Lépések lapon válassza az Új lehetőséget.

2. A Lépésnév mezőbe írja be Adatbázis e-mail mellékleteinek másolása.

3. A Típus mezőben válassza Transact-SQL szkript (T-SQL).

4. Az Adatbázis mezőben válassza a lehetőséget msdb.

5. A Parancs mezőbe írja be a következő utasítást az előző hónapról elnevezett melléklettáblázat létrehozásához, amely tartalmazza az előző lépésben átvitt üzeneteknek megfelelő mellékleteket:

   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. A lépés mentéséhez kattintson az OK gombra .

Feladatlépés létrehozása az Adatbázisposta napló archiválásához

1. A Lépések lapon válassza az Új lehetőséget.

2. A Lépésnév mezőbe írja be Adatbázis levelezési napló másolása.

3. A Típus mezőben válassza Transact-SQL szkript (T-SQL).

4. Az Adatbázis mezőben válassza a lehetőséget msdb.

5. A Parancs mezőbe írja be a következő utasítást egy, az előző hónapról elnevezett naplótáblához, amely a korábbi lépésben átvitt üzeneteknek megfelelő naplóbejegyzéseket tartalmazza:

   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. A lépés mentéséhez kattintson az OK gombra .

Feladatlépés létrehozása az archivált sorok adatbázis-levelezésből való eltávolításához

1. A Lépések lapon válassza az Új lehetőséget.

2. A Lépésnév mezőbe írja be: Sorok eltávolítása a Database Mailből.

3. A Típus mezőben válassza Transact-SQL szkript (T-SQL).

4. Az Adatbázis mezőben válassza a lehetőséget msdb.

5. A Parancs mezőbe írja be a következő utasítást az aktuális hónapnál régebbi sorok eltávolításához az Adatbázispostal kapcsolatos táblákból:

   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. A lépés mentéséhez kattintson az OK gombra .

Feladatlépés létrehozása az archivált elemek adatbázis-levelezési eseménynaplóból való eltávolításához

1. A Lépések lapon válassza az Új lehetőséget.

2. A Lépés neve mezőbe írja be a Sorok eltávolítása a Database Mail eseménynaplójából.

3. A Típus mezőben válassza Transact-SQL szkript (T-SQL).

4. A Parancs mezőbe írja be a következő utasítást az aktuális hónapnál régebbi sorok eltávolításához a Database Mail eseménynaplójából:

   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. A lépés mentéséhez kattintson az OK gombra .

A feladat rendszeres futtatásának ütemezése

1. Az Új feladat párbeszédpanelen válassza az Ütemezések lehetőséget.

2. Az Ütemezések lapon válassza az Új lehetőséget.

3. A Név mezőbe írja be Archív adatbázis levelezés.

4. Az ütemezés típusa mezőben válassza ki az ismétlődőlehetőséget.

5. A Gyakoriság területen válassza ki a feladat rendszeres futtatásához szükséges beállításokat, például havonta egyszer.

6. A Napi gyakoriság területen válassza a Egyszer előfordul <időpontban>.

7. Ellenőrizze, hogy a többi beállítás konfigurálva van-e, majd az ütemezés mentéséhez kattintson az OK gombra .

8. A feladat mentéséhez kattintson az OK gombra .

Kapcsolódó tartalom

• Adatbázis e-mailjeinek általános hibaelhárítási lépései