Gérer la taille du fichier journal des transactions

  • Article

S’applique à :SQL Server

Cet article explique comment surveiller la taille d'un journal des transactions SQL Server, le réduire, y ajouter ou agrandir un fichier journal de transactions, optimiser le taux de croissance du journal des transactions de tempdb, et contrôler sa croissance.

Cet article s'applique à SQL Server. Pour en savoir plus sur la gestion de la taille des fichiers journaux de transactions dans Azure SQL Managed Instance, reportez-vous à Gérer l'espace de stockage des bases de données dans Azure SQL Managed Instance. En revanche, pour en savoir plus sur Azure SQL Database, reportez-vous à Gérer l'espace de stockage des bases de données dans Azure SQL Database.

Appréhender les types d'espace de stockage d'une base de données

Il est essentiel d’appréhender les quantités d’espace de stockage suivantes pour gérer l’espace de fichier d’une base de données.

Quantité pour une base de données Définition Commentaires
Espace de données utilisé Quantité d’espace utilisée pour stocker les données de la base de données. En général, l’espace utilisé augmente (diminue) lors des insertions (suppressions). Dans certains cas, l’espace utilisé ne change pas lors des insertions ou suppressions selon la quantité et le modèle des données impliquées dans l’opération et dans toute fragmentation éventuelle. Par exemple, la suppression d’une ligne dans chaque page de données ne diminue pas forcément l’espace utilisé.
Espace de données alloué La quantité d’espace de fichiers formatés mise à disposition pour stocker les données de la base de données. La quantité d’espace allouée augmente automatiquement, mais ne diminue jamais après les suppressions. Ce comportement garantit que les insertions ultérieures seront plus rapides, car l’espace n’aura pas besoin d’être reformaté.
Espace de données alloué mais non utilisé La différence entre la quantité d’espace de données allouée et la quantité d’espace de données utilisée. Cette quantité représente la quantité maximale d’espace libre qui peut être récupérée par la réduction des fichiers de données de la base de données.
Taille maximale des données La quantité maximale d’espace qui peut être utilisée pour le stockage des données de la base de données. La quantité d’espace de données allouée ne peut pas croître au-delà de la taille maximale des données.

Le schéma suivant illustre la relation entre les différents types d’espace de stockage d’une base de données.

Diagram that demonstrates the size of difference database space concepts in the database quantity table.

Interroger une base de données unique pour des informations relatives à l'espace de stockage

Utilisez la requête suivante pour retourner le volume d'espace de fichiers de base de données alloués et le volume d'espace alloué non utilisé. Le résultat de la requête est exprimé en Mo.

-- Connect to a user database
SELECT file_id, type_desc,
       CAST(FILEPROPERTY(name, 'SpaceUsed') AS decimal(19,4)) * 8 / 1024. AS space_used_mb,
       CAST(size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS decimal(19,4)) AS space_unused_mb,
       CAST(size AS decimal(19,4)) * 8 / 1024. AS space_allocated_mb,
       CAST(max_size AS decimal(19,4)) * 8 / 1024. AS max_size_mb
FROM sys.database_files;

Surveiller 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 en savoir plus 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 d'un fichier journal

Pour réduire la taille physique d'un fichier journal physique en renvoyant l'espace libre du fichier au système d'exploitation, réduisez la taille du fichier journal. Une réduction ne fait qu'une différence lorsqu'un fichier journal de transactions contient un espace inutilisé.

Si le fichier journal est plein, probablement en raison de transactions ouvertes, examinez les causes du blocage de la troncation du journal des transactions.

Attention

Les opérations de réduction ne doivent pas être considérées comme une opération de maintenance régulière. Les fichiers de données et les fichiers journaux qui augmentent en raison d’opérations d’entreprise périodiques régulières ne nécessitent pas d’opérations de réduction. Les commandes de réduction ont un impact sur les performances de la base de données pendant l’exécution et, si possible, doivent être exécutées pendant les périodes de faible utilisation. Il n’est pas recommandé de réduire les fichiers de données si la charge de travail normale de l’application entraîne une nouvelle augmentation pour atteindre la même taille allouée.

Soyez conscient du possible impact négatif sur les performances que peut avoir la réduction des fichiers de base de données. Reportez-vous à Maintenance de l'index après la réduction.

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 en savoir plus, reportez-vous aux Recommandations.

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.

Certains facteurs (par exemple, une transaction longue) peuvent maintenir les fichiers journaux virtuels actifs pendant une période de temps prolongée, et 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 de la fin du fichier journal pour réduire le journal et le ramener à une taille proche de la taille cible.

Pour en savoir plus sur les opérations de réduction, suivez les liens suivants :

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

Maintenance des index après réduction

Une fois que l’opération de réduction est effectuée sur les fichiers de données, les index peuvent devenir fragmentés. Cela réduit l’efficacité de l’optimisation des performances pour certaines charges de travail, comme les requêtes qui utilisent de grandes analyses. Si la dégradation des performances se produit une fois l’opération de réduction terminée, envisagez la maintenance des index pour reconstruire les index. N’oubliez pas que les reconstructions d’index demandent de l’espace libre dans la base de données, ce qui peut entraîner une augmentation de l’espace alloué et donc un affaiblissement de l’effet de réduction.

Pour plus d’informations sur la maintenance des index, consultez Optimiser la maintenance des index pour améliorer les performances des requêtes et réduire la consommation des ressources.

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 en savoir plus, reportez-vous aux Recommandations.

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. Cette action 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 ce qui suit :

  • 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 ILLIMITÉE à la croissance, utilisez l'option MAXSIZE.

Pour en savoir plus, reportez-vous aux Recommandations.

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 utiliser 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 est applicable aux événements de croissance des journaux des transactions jusqu’à 64 Mo. L’incrément par défaut de la taille de croissance automatique pour les nouvelles bases de données est de 64 Mo. Les événements de croissance automatique des fichiers journaux des transactions qui sont supérieurs à 64 Mo ne peuvent pas bénéficier 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 automatique 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, reportez-vous à ce script d'analyse et de correction des fichiers journaux virtuels, fourni par la SQL Tiger Team.

  • Un grand incrément de croissance automatique peut causer deux problèmes :

    • Un incrément de croissance automatique élevé peut générer une pause dans la base de données pendant que le nouvel espace est alloué, ce qui peut entraîner des délais d'expiration lors des requêtes.
    • Un incrément de croissance automatique élevé peut générer un nombre insuffisant de fichiers journaux virtuels volumineux et affecter 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, reportez-vous à ce script d'analyse et de correction des fichiers journaux virtuels, fourni par la SQL Tiger Team.

  • 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 en savoir plus sur le changement de l'incrément de croissance, reportez-vous aux 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é.

Étapes suivantes