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. Bien que similaire, pour plus d’informations sur la gestion de la taille des fichiers journaux de transactions dans Azure SQL Managed Instance, consultez Gérer l’espace de fichiers pour les 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

Comprendre les quantités d’espace de stockage suivantes est importante 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é Espace utilisé pour stocker les données de 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 sur les insertions ou les suppressions en fonction de la quantité et du modèle de données impliqués dans l’opération et toute fragmentation. 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é Espace de fichier mis en forme disponible pour le stockage des données de 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 futures sont plus rapides, car l’espace n’a pas besoin d’être reformaté.
Espace de données alloué mais non utilisé Différence entre la quantité allouée et l’espace de données utilisé. Cette quantité représente l’espace libre maximal qui réduit les fichiers de données de base de données peut récupérer.
Taille maximale des données Quantité maximale d’espace pour stocker les données de 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.

Diagramme montrant la taille de différents concepts d’espace de base de données dans la table de quantité de base de données.

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 plus d’informations sur la taille actuelle du fichier journal, sa taille maximale et l’option de croissance automatique pour le fichier, vous pouvez également utiliser les colonnes et growth les max_sizesizecolonnes 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

Réduisez le fichier journal pour réduire sa taille physique en retournant de l’espace libre au système d’exploitation. Une réduction ne fait une différence que 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. Réduire les commandes impacte les performances de la base de données lors de l’exécution ; elles doivent être exécutées pendant des 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 de la taille des fichiers jusqu’à la même taille allouée.

Tenez compte de l’impact potentiel sur les performances négatives de la réduction des fichiers de base de données ; consultez maintenance d’index après 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 de nouveau nécessaire après une réduction du journal, le journal des transactions augmente à nouveau, ce qui entraîne une surcharge de performances pendant les opérations de croissance des journaux. Pour plus d’informations, consultez les 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 être possible uniquement après la troncation du 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

Les index peuvent devenir fragmentés une fois qu’une opération de réduction est terminée sur les fichiers de données. Cela réduit leur efficacité pour l’optimisation des performances pour certaines charges de travail, telles que les requêtes utilisant des analyses volumineuses. 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. Gardez à l’esprit que les reconstructions d’index nécessitent un espace libre dans la base de données et peuvent donc augmenter l’espace alloué, contrecarrant l’effet de la 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 fichier journal des transactions suffit, sauf si l’espace 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. Cela permet au journal de croître.

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 tempdb journal des transactions après le démarrage ou le redémarrage de 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 :

Utilisez l’option SIZE pour modifier la taille de fichier actuelle en unités Ko, Mo, Go et To.

  • Pour changer l’incrément de croissance, utilisez l’option FILEGROWTH. La valeur 0 indique que la croissance automatique est désactivée et qu’aucun espace supplémentaire n’est autorisé. Utilisez l’option MAXSIZE pour contrôler la taille maximale d’un fichier journal en unités Ko, Mo, Go et To ou pour définir la croissance sur UNLIMITED.

Pour en savoir plus, reportez-vous aux Recommandations.

Recommandations

Voici quelques recommandations générales lorsque vous utilisez des fichiers journaux des transactions :

  • L’incrément automatique de croissance (croissance automatique) du journal des transactions, tel qu’il est défini par l’option FILEGROWTH , doit être suffisamment grand pour rester à l’avance sur les besoins des transactions de 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 :

    • Temps nécessaire pour exécuter une sauvegarde complète, car les sauvegardes de journaux ne peuvent pas se produire tant qu’elles ne sont pas terminées.
    • 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

  • Lorsque vous définissez la croissance automatique pour les fichiers de données et de journaux à l’aide de l’option FILEGROWTH , il peut être préférable de la définir en taille au lieu du pourcentage pour permettre un meilleur contrôle du ratio de croissance, car un pourcentage est une quantité toujours croissante.

    • Dans les versions antérieures à SQL Server 2022 (16.x), les journaux des transactions ne peuvent pas utiliser l’initialisation de fichiers instantanés, de sorte que les temps de croissance des journaux étendus sont 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 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 de transactions d'une taille supérieure à 64 Mo ne peuvent pas bénéficier de l'initialisation instantanée de fichier.

    • En guise de bonne pratique, ne définissez pas la FILEGROWTH valeur d’option supérieure à 1 024 Mo pour les journaux des transactions. Les valeurs par défaut de 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 petit incrément de croissance peut générer trop de petites VLF et réduire les performances. Pour déterminer la distribution VLF optimale pour la taille actuelle du journal des transactions de toutes les bases de données d’une instance donnée et les incréments de croissance requis pour atteindre la taille requise, consultez ce script pour analyser et corriger les fichiers VLF fournis par l’équipe SQL Tiger.

  • Un incrément de croissance automatique élevé 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 également le niveau de performance. Pour déterminer la distribution VLF optimale pour la taille actuelle du journal des transactions de toutes les bases de données d’une instance donnée et les incréments de croissance requis pour atteindre la taille requise, consultez ce script pour analyser et corriger les fichiers VLF fournis par l’équipe SQL Tiger.

  • Même si la croissance automatique est activée, vous pouvez recevoir un message indiquant que le journal des transactions est plein 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 ALTER DATABASE (Transact-SQL).

  • L’utilisation de plusieurs fichiers journaux dans une base de données n’améliore pas les performances, car les fichiers journaux des transactions n’utilisent pas de 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, cela n’est pas recommandé et la propriété de base de données auto_shrink a la valeur 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é. - Le fichier est réduit à la taille à laquelle seulement 25 % du fichier n’est pas utilisé ou à la taille d’origine du fichier, selon la taille la plus grande. - Pour plus d’informations sur la modification du paramètre de la propriété auto_shrink, consultez Afficher ou modifier les propriétés d’une base de données et des options ALTER DATABASE SET (Transact-SQL).

Contenu connexe