Procédure : créer un travail d'Agent SQL Server pour archiver les messages et les journaux d'événements de la messagerie de base de données

Des copies des messages de la messagerie de base de données et de leurs pièces jointes sont conservées dans les tables msdb avec le journal d'événements de la messagerie de base de données. Il peut être utile de supprimer périodiquement les messages et les événements dont vous n'avez plus besoin afin de réduire la taille de ces tables. Les procédures suivantes permettent de créer un travail de l'Agent SQL Server pour automatiser le processus.

1. La première procédure crée un travail intitulé Archiver la messagerie de base de données qui contient cinq étapes.

2. La première étape copie tous les messages contenus dans les tables de la messagerie de base de données vers une nouvelle table nommée d'après le mois précédent au format DBMailArchive_<year_month>.

3. La deuxième étape copie les pièces jointes associées aux messages copiés à la première étape à partir des tables de la messagerie de base de données vers une nouvelle table nommée d'après le mois précédent au format DBMailArchive_Attachments_<year_month>.

4. La troisième étape copie les événements du journal des événements de la messagerie de base de données qui sont associés aux messages copiés à la première étape, à partir des tables de la messagerie de base de données vers une nouvelle table nommée d'après le mois précédent au format DBMailArchive_Log_<year_month>.

5. La quatrième étape supprime des tables de la messagerie de base de données les enregistrements des éléments de messagerie transférés.

6. La cinquième étape supprime du journal des événements de la messagerie de base de données les événements associés aux éléments de messagerie transférés.

7. La procédure finale planifie l'exécution du travail au début de chaque mois.

Pour cet exemple, les tables d'archive sont créées dans la base de données msdb. Afin de réduire la taille de la base de données msdb, vous pouvez stocker les nouvelles tables dans une base de données d'archive spéciale, exporter les lignes vers un fichier texte ou tout simplement les supprimer. Cet exemple déplace uniquement les lignes vers de nouvelles tables dans la base de données msdb. À des fins de production, vous pouvez ajouter des fonctionnalités supplémentaires de vérification des erreurs et faire envoyer un message électronique aux opérateurs en cas d'échec du travail.

Pour créer un travail d’Agent SQL Server

1. Dans l'Explorateur d'objets, développez l'Agent SQL Server, cliquez avec le bouton droit sur Travaux, puis cliquez sur Nouveau travail.

2. Dans la boîte de dialogue Nouveau travail, dans la zone Nom, tapez Archiver la messagerie de base de données.

3. Dans la zone Propriétaire, confirmez que le propriétaire est membre du rôle serveur fixe sysadmin.

4. Dans la zone Catégorie, cliquez sur Maintenance de la base de données.

5. Dans la zone Description, tapez Archiver les messages de la messagerie de base de données, puis cliquez sur Étapes.

Pour créer une étape permettant d’archiver les messages de la messagerie de base de données

1. Dans la page Étapes, cliquez sur Nouveau.

2. Dans la zone Nom de l'étape, tapez Copier les éléments de la messagerie de base de données.

3. Dans la zone Type, sélectionnez Script Transact-SQL (T-SQL).

4. Dans la zone Base de données, sélectionnez msdb.

5. Dans la zone Commande, tapez l'instruction suivante pour créer une table nommée d'après le mois précédent, contenant les lignes antérieures au début du mois actuel :

   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. Cliquez sur OK pour enregistrer l'étape.

Pour créer une étape permettant d’archiver les pièces jointes de la messagerie de base de données

1. Dans la page Étapes, cliquez sur Nouveau.

2. Dans la zone Nom de l'étape, tapez Copier les pièces jointes de la messagerie de base de données.

3. Dans la zone Type, sélectionnez Script Transact-SQL (T-SQL).

4. Dans la zone Base de données, sélectionnez msdb.

5. Dans la zone Commande, tapez l'instruction suivante pour créer une table de pièces jointes nommée d'après le mois précédent, contenant les pièces jointes qui correspondent aux messages transférés à l'étape précédente :

   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. Cliquez sur OK pour enregistrer l'étape.

Pour créer une étape permettant d’archiver le journal de la messagerie de base de données

1. Dans la page Étapes, cliquez sur Nouveau.

2. Dans la zone Nom de l'étape, tapez Copier le journal de la messagerie de base de données.

3. Dans la zone Type, sélectionnez Script Transact-SQL (T-SQL).

4. Dans la zone Base de données, sélectionnez msdb.

5. Dans la zone Commande, tapez l'instruction suivante pour créer une table de journal nommée d'après le mois précédent, contenant les entrées de journal qui correspondent aux messages transférés à l'étape antérieure :

   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. Cliquez sur OK pour enregistrer l'étape.

Pour créer une étape permettant de supprimer les lignes archivées du journal de la messagerie de base de données

1. Dans la page Étapes, cliquez sur Nouveau.

2. Dans la zone Nom de l'étape, tapez Supprimer les lignes de la messagerie de base de données.

3. Dans la zone Type, sélectionnez Script Transact-SQL (T-SQL).

4. Dans la zone Base de données, sélectionnez msdb.

5. Dans la zone Commande, tapez l'instruction suivante pour supprimer des tables de la messagerie de base de données les lignes antérieures au mois actuel :

   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. Cliquez sur OK pour enregistrer l'étape.

Pour créer une étape permettant de supprimer les éléments archivés du journal des événements de la messagerie de base de données

1. Dans la page Étapes, cliquez sur Nouveau.

2. Dans la zone Nom de l'étape, tapez Supprimer les lignes du journal des événements de la messagerie de base de données.

3. Dans la zone Type, sélectionnez Script Transact-SQL (T-SQL).

4. Dans la zone Base de données, sélectionnez msdb.

5. Dans la zone Commande, tapez l'instruction suivante pour supprimer du journal des événements de la messagerie de base de données les lignes antérieures au mois actuel :

   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. Cliquez sur OK pour enregistrer l'étape.

Pour planifier l'exécution du travail au début de chaque mois

1. Dans la boîte de dialogue Nouveau travail, cliquez sur Planifications.

2. Dans la page Planifications, cliquez sur Nouvelle.

3. Dans la zone Nom, tapez Archiver la messagerie de base de données.

4. Dans la zone Type de planification, sélectionnez Périodique.

5. Dans la zone Fréquence, sélectionnez les options appropriées pour exécuter le travail le premier jour de chaque mois.

6. Dans la zone Fréquence quotidienne, sélectionnez Une fois à 03:00:00.

7. Vérifiez que les autres options sont configurées à votre convenance, puis cliquez sur OK pour enregistrer la planification.

8. Cliquez sur OK pour enregistrer le travail.

Sécurité

Vous devez être membre du rôle serveur fixe sysadmin pour pouvoir exécuter les procédures stockées décrites dans cette rubrique.

Voir aussi

Référence

Vues de la messagerie de base de données (Transact-SQL)

Procédures stockées de messagerie de base de données et SQL Mail (Transact-SQL)

Creating SQL Server Agent Jobs

Concepts

Messagerie de base de données