Partager via

Gérer la taille du fichier journal des transactions

S'applique à :SQL Server

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

Cet article s'applique à SQL Server. Bien que le processus soit similaire, pour la gestion de l’espace de fichiers dans Azure SQL, consultez :

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 règle générale, l’espace utilisé augmente sur les insertions et diminue sur les suppressions. Dans certains cas, l’espace utilisé ne change pas sur les insertions ou les suppressions, selon la quantité et le 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 le stockage des 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 diagramme suivant illustre les relations entre les différents types d’espace de stockage d’une base de données.

Diagramme illustrant les relations entre les différents types d’espace de stockage d’une 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 sizemax_sizecolonnes 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 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 complet, probablement en raison de transactions ouvertes, examinez ce qui empêche 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 de journaux qui augmentent en raison d’opérations commerciales 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. Nous vous déconseillons de réduire les fichiers de données si une charge de travail d’application régulière entraîne une croissance des fichiers vers 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 la maintenance de l’index après la réduction.

Avant de réduire le journal des transactions, gardez à l’esprit les facteurs qui peuvent 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 Recommandations.

Vous pouvez réduire un fichier journal uniquement lorsque la base de données est en ligne et qu’au moins un fichier journal virtuel (VLF) est gratuit. Dans certains cas, la réduction du journal peut être possible uniquement après la troncation du journal suivante.

Certains facteurs, tels qu’une transaction de longue durée, peuvent conserver les FVL actives pendant une période prolongée, peuvent restreindre la réduction des journaux ou même empêcher le journal de réduire du tout. 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 plus d’informations sur les opérations de réduction, consultez les ressources suivantes :

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 d’index après une opération de 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. Cette fragmentation réduit leur efficacité pour l’optimisation des performances pour certaines charges de travail, telles que les requêtes qui utilisent 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 l’opération de réduction.

Pour plus d’informations, 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. Cette action 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 les options de fichier et de groupe de fichiers ALTER DATABASE (Transact-SQL).

Pour plus d’informations, consultez Recommandations.

Optimiser la taille du journal des transactions tempdb

Le redémarrage d’une instance de serveur redimensionne le journal des transactions de la tempdb base de données à sa taille de prédéfinie d’origine. Ce redimensionnement peut réduire les performances du tempdb journal des transactions.

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 la base de données Tempdb.

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

Utilisez l’instruction d’options de fichier et de groupe de fichiers ALTER DATABASE (Transact-SQL) pour gérer la croissance d’un fichier journal des 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 UNLIMITEDsur .

Pour plus d’informations, consultez Recommandations.

Recommandations

Voici quelques recommandations générales à prendre en compte 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. Un bon conseil pour dimensionner correctement un journal des transactions consiste à surveiller la quantité de journaux occupés pendant :

    • Temps nécessaire pour exécuter une sauvegarde complète, car les sauvegardes de journal 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
    • Temps nécessaire pour exécuter le plus grand lot 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é croissante.

    • Dans les versions antérieures à SQL Server 2022 (16.x), les journaux des transactions ne peuvent pas utiliser l’initialisation instantanée des fichiers, 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) Données : 64 Mo. Fichiers journaux : 64 Mo.
      À compter de SQL Server 2005 (9.x) Données : 1 Mo. Fichiers journaux : 10%.
      Avant SQL Server 2005 (9.x) Données : 10%. Fichiers journaux : 10%.

  • Un petit incrément de croissance automatique 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 :

    • Elle peut entraîner la pause de la base de données pendant que le nouvel espace est alloué, ce qui peut entraîner des délais d’attente de requête.
    • Il peut générer trop peu et trop grand VLF et peut également affecter 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.

  • 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 la modification de l’incrément de croissance, consultez les options de fichier et de groupe 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, nous déconseillons cette configuration et la AUTO_SHRINK propriété de base de données a la valeur FALSE par défaut. Si AUTO_SHRINK la valeur true est définie, la réduction automatique réduit la taille d’un fichier uniquement lorsque plus de 25 % de son espace n’est pas utilisé.

Contenu connexe

  • Last updated on