Créer un travail SQL Server Agent pour archiver les messages et les journaux d’événements de la messagerie de base de données

S’applique à :SQL Server Azure SQL Managed Instance

Les copies des messages de messagerie de base de données et de leurs pièces jointes sont conservées dans msdb des tables, ainsi que dans le journal des événements de la messagerie de base de données. Il peut être utile d'archiver périodiquement les messages et les événements dont vous n'avez plus besoin afin de réduire la taille des tables.

Les procédures suivantes permettent de créer un travail de l'Agent SQL Server pour automatiser le processus.

Prérequis

Pour exécuter des commandes T-SQL sur votre instance SQL Server, utilisez SQL Server Management Studio (SSMS),l’extension MSSQL pour Visual Studio Code, sqlcmd ou votre outil de requête T-SQL favori.

Recommandations

Envisagez la vérification des erreurs et surveillez ce travail pour envoyer un message électronique aux opérateurs en cas d’échec de ce travail d’archivage.

Si vous le souhaitez, vous pouvez déplacer des données de messagerie de base de données archivées archivées vers une base de données d’archivage personnalisée en dehors de msdb, ou les exporter à partir de SQL Server.

autorisations

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

Créer une tâche de messagerie pour base de données d’archivage

La première procédure crée un travail intitulé Archiver la messagerie de base de données avec les étapes suivantes.

Copiez tous les messages des tables de messagerie de base de données vers une nouvelle table nommée après le mois précédent, au format DBMailArchive__<year_month>.
Copiez les pièces jointes associées aux messages copiés à la première étape, à partir des tables de messagerie de base de données vers une nouvelle table nommée après le mois précédent au format DBMailArchive_Attachments_<year_month>.
Copiez les événements du journal des événements de la messagerie de base de données qui sont liés aux messages copiés à la première étape, des tables de messagerie de base de données à une nouvelle table nommée après le mois précédent au format DBMailArchive_Log_<year_month>.
Supprimez des tables de la messagerie de base de données les enregistrements des éléments de messagerie transférés.
Supprimez 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.
Planifiez une exécution périodique du travail.

Créer un travail de SQL Server Agent

Les étapes suivantes utilisent SQL Server Management Studio (SSMS). Téléchargez la dernière version de SSMS à aka.ms/ssms.

Connectez-vous à l’instance SQL Server.
Dans l’Explorateur d’objets, développez SQL Server Agent, cliquez avec le bouton droit sur Travaux, puis sélectionnez Nouveau travail.
Dans la boîte de dialogue Nouveau travail , dans la zone Nom , tapez Archiver la messagerie de base de données.
Dans la zone Propriétaire , confirmez que le propriétaire est membre du rôle serveur fixe sysadmin .
Dans la zone Catégorie , sélectionnez la maintenance de la base de données.
Dans la zone Description , tapez Archiver les messages de messagerie de base de données, puis sélectionnez Étapes.

Créer une étape de travail pour archiver les messages de messagerie de base de données

Dans la page Étapes , sélectionnez Nouveau.
Dans la zone Nom de l'étape , tapez Copier les éléments de la messagerie de base de données.
Dans la zone Type , sélectionnez Script Transact-SQL (T-SQL).
Dans la zone Base de données , sélectionnez msdb.

Dans la zone Commande , tapez l’instruction T-SQL suivante pour créer une table nommée après le mois précédent, contenant des lignes antérieures au début du mois en cours.

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 ;

Sélectionnez OK pour enregistrer l’étape.

Créer une étape de travail pour archiver les pièces jointes de la messagerie de base de données

Dans la page Étapes , sélectionnez Nouveau.
Dans la zone Nom de l'étape , tapez Copier les pièces jointes de la messagerie de base de données.
Dans la zone Type , sélectionnez Script Transact-SQL (T-SQL).
Dans la zone Base de données , sélectionnez msdb.

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 ;

Sélectionnez OK pour enregistrer l’étape.

Créer une étape de travail pour archiver le journal de messagerie de base de données

Dans la page Étapes , sélectionnez Nouveau.
Dans la zone Nom de l'étape , tapez Copier le journal de la messagerie de base de données.
Dans la zone Type , sélectionnez Script Transact-SQL (T-SQL).
Dans la zone Base de données , sélectionnez msdb.

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 ;

Sélectionnez OK pour enregistrer l’étape.

Créer une étape de travail pour supprimer les lignes archivées de la messagerie de base de données

Dans la page Étapes , sélectionnez Nouveau.
Dans la zone Nom de l'étape , tapez Supprimer les lignes de la messagerie de base de données.
Dans la zone Type , sélectionnez Script Transact-SQL (T-SQL).
Dans la zone Base de données , sélectionnez msdb.

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 ;

Sélectionnez OK pour enregistrer l’étape.

Créer une étape de travail pour supprimer les éléments archivés du journal des événements de la messagerie de base de données

Dans la page Étapes , sélectionnez Nouveau.
Dans la zone Nom de l’étape , tapez Supprimer les lignes du journal des événements de la messagerie de base de données.
Dans la zone Type , sélectionnez Script Transact-SQL (T-SQL).

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 ;

Sélectionnez OK pour enregistrer l’étape.

Programmer l’exécution périodique de la tâche

Dans la boîte de dialogue Nouveau travail , sélectionnez Planifications.
Dans la page Planifications , sélectionnez Nouveau.
Dans la zone Nom , tapez Archiver la messagerie de base de données.
Dans la zone Type de planification , sélectionnez Périodique.
Dans la zone Fréquence , sélectionnez les options appropriées pour exécuter périodiquement le travail, par exemple une fois par mois.
Dans la zone Fréquence quotidienne, sélectionnez Une fois à <heure>.
Vérifiez que les autres options sont configurées comme vous le souhaitez, puis sélectionnez OK pour enregistrer la planification.
Sélectionnez OK pour enregistrer la tâche.

Étapes générales de résolution des problèmes liés à la messagerie de base de données

Rétroaction

Cette page vous a-t-elle été utile ?

Last updated on 2025-05-20