Gérer la taille du fichier journal des transactions

  • Article
  • 7 minutes de lecture

S’applique à :SQL Server

Cette rubrique contient des informations sur la façon de surveiller la taille d’un journal des transactions SQL Server, de réduire le journal des transactions, d’ajouter ou d’agrandir un fichier journal de transactions, d’optimiser le taux de croissance du journal des transactions de tempdb, et de contrôler la croissance d’un fichier journal de transactions.

Contrôler l’utilisation de l’espace pour le journal

Surveillez l’utilisation de l’espace pour le journal à l’aide de sys.dm_db_log_space_usage. Cette vue de gestion dynamique retourne des informations sur la quantité d’espace journal utilisée et indique à quel moment le journal des transactions a besoin d’être tronqué.

Pour plus d’informations sur la taille actuelle d’un fichier journal, sa taille maximale et l’option de croissance automatique du fichier, vous pouvez également utiliser les colonnes size, max_size et growth de ce fichier journal dans sys.database_files.

Important

Évitez de surcharger le disque du journal. Assurez-vous que le stockage des journaux peut supporter les exigences d’IOPS et de faible latence inhérentes à votre charge transactionnelle.

Réduire la taille du fichier journal

Pour réduire la taille physique d'un fichier journal physique, vous devez réduire le fichier journal. Cela est utile quand vous savez qu’un fichier journal de transactions contient de l’espace inutilisé. Vous pouvez réduire un fichier journal uniquement quand la base de données est en ligne, et qu’au moins un fichier journal virtuel est libre. Dans certains cas, la réduction du journal peut n'être possible qu'après la troncation de journal suivante.

Notes

Certains facteurs (par exemple, une transaction longue) chargés de maintenir les fichiers journaux virtuels actifs pendant une période de temps prolongée peuvent limiter, voire empêcher, la réduction du journal. Pour plus d’informations, consultez Facteurs pouvant retarder la troncation du journal.

La réduction d’un fichier journal supprime un ou plusieurs fichiers journaux virtuels qui ne contiennent aucune partie du journal logique (autrement dit, des fichiers journaux virtuels inactifs). Quand vous réduisez un fichier journal de transactions, les fichiers journaux virtuels inactifs sont supprimés à la fin du fichier journal pour réduire le journal et le ramener à une taille proche de la taille cible.

Important

Avant de réduire le journal des transactions, gardez à l’esprit les facteurs pouvant retarder la troncation du journal. Si l’espace de stockage est à nouveau nécessaire après une réduction de journal, le journal des transactions croît de nouveau, introduisant une surcharge au niveau des performances pendant les opérations d’accroissement du journal. Pour plus d’informations, consultez les Recommandations dans cette rubrique.

Réduire un fichier journal (sans réduire les fichiers de base de données)

Surveiller les événements de réduction du fichier journal

Contrôler l’espace pour le journal

Ajouter ou agrandir un fichier journal

Vous pouvez gagner de l’espace en agrandissant le fichier journal existant (si l’espace disque le permet) ou en ajoutant un fichier journal à la base de données, généralement sur un autre disque. Un seul fichier journal de transactions est suffisant, sauf si l’espace pour le journal est insuffisant, et que l’espace disque est également insuffisant sur le volume qui contient le fichier journal.

  • Pour ajouter un fichier journal à la base de données, utilisez la clause ADD LOG FILE de l’instruction ALTER DATABASE. L'ajout d'un fichier journal permet au journal de croître.
  • Pour agrandir le fichier journal, utilisez la clause MODIFY FILE de l’instruction ALTER DATABASE, en spécifiant la syntaxe SIZE et MAXSIZE. Pour plus d’informations, consultez Options de fichiers et de groupes de fichiers ALTER DATABASE (Transact-SQL).

Pour plus d’informations, consultez les Recommandations dans cette rubrique.

Optimiser la taille du journal des transactions tempdb

Le redémarrage d’une instance de serveur permet de redimensionner le journal des transactions de la base de données tempdb conformément à sa taille d’origine avant la croissance automatique. Ceci peut réduire les performances du journal des transactions tempdb .

Vous pouvez éviter cette surcharge en augmentant la taille du journal des transactions tempdb après avoir démarré ou redémarré l'instance de serveur. Pour plus d'informations, consultez tempdb Database.

Contrôler la croissance d’un fichier journal de transactions

Utilisez l’instruction ALTER DATABASE - Options de fichiers et de groupes de fichiers (Transact-SQL) pour gérer la croissance d’un fichier journal de transactions. Notez les points suivants :

  • Pour changer la taille actuelle du fichier selon les unités Ko, Mo, Go et To, utilisez l’option SIZE.
  • Pour changer l’incrément de croissance, utilisez l’option FILEGROWTH. Une valeur 0 indique que la croissance automatique est désactivée et qu'aucun espace supplémentaire n'est autorisé.
  • Pour contrôler la taille maximale d’un fichier journal en Ko, Mo, Go et To ou affecter la valeur UNLIMITED à la croissance, utilisez l’option MAXSIZE.

Pour plus d’informations, consultez les Recommandations dans cette rubrique.

Recommandations

Voici une série de recommandations générales à suivre pendant l’utilisation de fichiers journaux de transactions :

  • L’incrément de croissance automatique du journal des transactions, tel que défini par l’option FILEGROWTH, doit être suffisamment grand pour anticiper les besoins des transactions de la charge de travail. L'incrément de croissance d'un fichier journal doit être suffisamment important pour éviter une expansion fréquente. Une bonne approche pour dimensionner correctement un journal des transactions consiste à contrôler la quantité de journal occupée pendant :

    • Le temps nécessaire pour exécuter une sauvegarde complète, étant donné que les sauvegardes de fichier journal ne peuvent pas se produire tant qu’elle n’est pas terminée
    • Le temps nécessaire pour les opérations de maintenance des index les plus volumineux
    • Le temps nécessaire pour exécuter le lot le plus volumineux dans une base de données

  • Quand vous définissez la croissance automatique pour les fichiers journaux et de données à l’aide de l’option FILEGROWTH, il peut être préférable de le faire en taille plutôt qu’en pourcentage, pour permettre un meilleur contrôle du taux de croissance, car le pourcentage exprime une quantité en constante augmentation.

    • Dans les versions antérieures à SQL Server 2022 (16.x), les journaux des transactions ne peuvent pas tirer parti de l’initialisation instantanée de fichiers ; les temps de croissance de journal étendus sont donc particulièrement critiques.
    • À compter de SQL Server 2022 (16.x) (toutes les éditions) et dans Azure SQL Database, l’initialisation instantanée des fichiers peut bénéficier aux événements de croissance du journal des transactions jusqu’à 64 Mo. L’incrément de taille de croissance automatique par défaut pour les nouvelles bases de données est de 64 Mo. Les événements de croissance automatique du fichier journal des transactions supérieurs à 64 Mo ne peuvent pas tirer parti de l’initialisation instantanée des fichiers.
    • En guise de bonne pratique, ne définissez pas l’option FILEGROWTH sur une valeur supérieure à 1 024 Mo pour les journaux des transactions. Les valeurs par défaut pour l’option FILEGROWTH sont les suivantes :
    Version Valeurs par défaut
    À compter de SQL Server 2016 (13.x) 64 Mo de données. 64 Mo de fichiers journaux.
    À compter de SQL Server 2005 (9.x) 1 Mo de données. 10 % de fichiers journaux.
    Avant SQL Server 2005 (9.x) 10 % de données. 10 % de fichiers journaux.

  • Un incrément de croissance réduit peut générer un nombre excessif de petits fichiers journaux virtuels et réduire le niveau de performance. Pour déterminer la distribution optimale des fichiers journaux virtuels pour la taille actuelle du journal des transactions de toutes les bases de données dans une instance donnée, ainsi que les incréments de croissance pour atteindre la taille nécessaire, consultez ce script.

  • Un incrément de croissance élevé peut générer un nombre insuffisants de fichiers journaux virtuels volumineux et affecter également le niveau de performance. Pour déterminer la distribution optimale des fichiers journaux virtuels pour la taille actuelle du journal des transactions de toutes les bases de données dans une instance donnée, ainsi que les incréments de croissance pour atteindre la taille nécessaire, consultez ce script.

  • Même si la croissance automatique est activée, vous pouvez recevoir un message indiquant que le journal des transactions est complet, s’il ne peut pas croître suffisamment rapidement pour répondre aux besoins de votre requête. Pour plus d’informations sur le changement de l’incrément de croissance, consultez Options de fichiers et de groupes de fichiers (Transact-SQL) ALTER DATABASE.

  • Avoir plusieurs fichiers journaux dans une base de données n’améliore pas du tout le niveau de performance, car les fichiers journaux de transactions n’utilisent pas le remplissage proportionnel comme les fichiers de données dans un même groupe de fichiers.

  • Les fichiers journaux peuvent être définis de manière à se réduire automatiquement. Toutefois, cette configuration étant déconseillée, la propriété de base de données auto_shrink est définie sur FALSE par défaut. Si auto_shrink est définie sur la valeur TRUE, la troncation automatique réduit la taille d’un fichier uniquement quand plus de 25 pour cent de son espace est inutilisé.

Voir aussi

BACKUP (Transact-SQL)
Résoudre les problèmes liés à un journal des transactions saturé (erreur SQL Server 9002)
Sauvegardes de fichier journal dans le guide d’architecture et gestion du journal des transactions SQL Server
Sauvegardes du journal des transactions (SQL Server)
Options de fichier et de groupe de fichiers ALTER DATABASE (Transact-SQL)