[SQL Server] Gestion d’un journal de transactions

Dans le billet [SQL Server] Un peu de théorie sur les journaux de transactions, il a été question de la compréhension de l’utilité d’un journal de transactions et de la manière dont SQL Server l’exploite. Nous allons, ici, aborder différentes façons de mieux le gérer.

Choix du mode de récupération

La clé d’une bonne gestion du journal de transactions LDF est de connaître le degré d’importance des besoins en termes de disponibilité et de récupération d’une base de données. Le choix final ne doit pas se faire à cause d’un problème de performance ou d’espace.

S’il n’y a aucun besoin en termes de restauration point-in-time, et qu’il est acceptable, suite à un crash, de restaurer la base de données à un état datant de la dernière sauvegarde complète ou différentielle, alors le mode de récupération SIMPLE est le bon choix.

En réalité, il n’est pas commun d’avoir une base de données de production où une perte de données de plusieurs heures est acceptable en cas de crash. Et c’est donc pourquoi, en règle générale, le mode de récupération SIMPLE est plutôt adapté aux bases de données de développement ou de tests.

S’il y a un besoin en récupération de la base de données à n’importe quel moment de la journée ou de la soirée (point-in-time), le mode de récupération FULL est recommandé.

Une bonne approche consiste à combiner les modes de récupération FULL et BULK-LOGGED de sorte que la base de données en FULL soit repassée en BULK-LOGGED à certains moments spécifiques de la journée ou de la soirée afin d’économiser de l’espace au niveau du journal de transactions LDF en cas d’exécution d’opérations « lourdes » (cas d’une opération de reconstruction d’indexes, par exemple).

Pour spécifier le mode de récupération d’une base de données LaBD en FULL, quoi de plus simple que de lancer la requête T-SQL suivante :

ALTER DATABASE LaBD SET RECOVERY FULL
GO

Pour les modes de récupération SIMPLE et BULK-LOGGED, il suffira simplement de changer, au sein de la requête, le mot-clé « FULL » en respectivement « SIMPLE » et « BULK_LOGGED ».

Au sujet de la chaîne des transactions (LSN)

Les sauvegardes transactionnelles d’une base de données forment une chaîne qui commence avec la dernière sauvegarde complète faite sur la base de données (ou la première sauvegarde complète faite après le passage de la base de données en FULL ou en BULK-LOGGED). Afin d’être en mesure d’effectuer une restauration point-in-time, la chaîne des sauvegardes transactionnelles ne doit pas être « coupée » de la sauvegarde complète (ou différentielle) à laquelle elle dépend.

Si la chaîne des transactions est brisée, soit à cause d’une troncature (voir plus loin), soit à cause d’un fichier de sauvegarde transactionnelle (TRN) manquant, soit à cause d’un repassage de la base de données en mode de récupération SIMPLE (le passage en BULK-LOGGED à partir de FULL et vice-versa ne brise pas la chaîne des sauvegardes transactionnelles), alors la base de données ne peut plus utiliser les sauvegardes transactionnelles.

Pour la restauration des sauvegardes transactionnelles, le moteur SQL utilise les LSN (Log Sequence Number, pour rappel) afin d’effectuer une restauration cohérente dans le temps.

Gestion de la taille d’un journal des transactions

          Contrôle du filegrowth et allocation de VLFs

Le filegrowth est une valeur de croissance d’un fichier de données ou de transactions d’une base de données. Il n’y a pas de règle empirique permettant de choisir le bon filegrowth pour un fichier de journal de transactions LDF, hormis le fait qu’il ne doit être ni trop gros (risque de saturation rapide de l’espace disque ou du LDF lui-même), ni trop petit (risques de contention, ou même de fragmentation du fichier LDF dû à la trop grande multiplication des segments de données).

Dans le cas d’un fichier de transactions LDF, il est souvent de bon ton de fournir une valeur d’auto-incrément (auto-growth ou auto-filegrowth)
équivalente à la moitié de celle du fichier de données MDF, et en MB et non en % (afin de mieux contrôler la croissance du fichier).

Concernant la taille initiale d’un fichier de journal de logs, tout dépend de la taille et de l’activité transactionnelle qu’est susceptible d’avoir la base de données. Afin de mieux optimiser l’utilisation des VLFs dans le cas d’un LDF de taille supérieure ou égale à 8 Go :

  • Si le LDF doit faire 8 Go, donnez une taille de départ de 8 Go.
  • Si le LDF doit faire 20 Go, donnez une taille de départ de 8 Go, puis étendez-la à 16 Go, puis à 24 Go (si l’on accepte les 4 Go excédentaires, sinon 20 Go).
  • Si le LDF doit faire 40 Go, donnez une taille de départ de 8 Go, puis étendez-la à 16 Go, puis à 24 Go, puis à 32 Go, puis, enfin, à 40 Go.
  • Etc…

L’idée générale est d’allouer, par étapes, les VLFs en attribuant une taille d’allocation maximale (chunks) de 8 Go afin d’avoir des VLFs idéalement dimensionnés de façon à limiter les fragmentations.

Pour ce qui est d’un LDF de taille inférieure à 8 Go, la taille de départ peut correspondre à la taille finale souhaitée.

Remarquons qu’il est important de trouver un juste milieu concernant le nombre de VLFs au sein d’un journal de transactions. Ainsi, un trop petit nombre de VLFs peut empêcher la rapidité de la journalisation des transactions (faute d’espace disponible par rapport à la taille des transactions à traiter) tandis qu’un trop grand nombre de VLFs (> 50) peut potentiellement augmenter les risques de fragmentation du journal de transactions et donc impacter les performances transactionnelles.
Notons bien que la taille et le nombre de VLFs dépendent principalement de la taille des chunks ajoutés au journal de transactions. De manière générale, un chunk de taille :

  • Supérieure à 1 Go équivaut à 16 VLFs.
  • Comprise entre 64 Mo et 1 Go équivaut à 8 VLFs.
  • Inférieure ou égale à 64 Mo équivaut à 4 VLFs.

Plus de détails sur le blog de Kimberly Tripp : http://www.sqlskills.com/BLOGS/KIMBERLY/post/Transaction-Log-VLFs-too-many-or-too-few.aspx.

          Réduction de la taille d’un journal des transactions

          Troncature

La troncature (ou troncation) est une opération qui permet de réduire virtuellement la taille du fichier de journal de transactions LDF. Pour ce faire, le moteur SQL libère les VLFs inactifs en les « marquant » comme réutilisables et/ou supprimables. Il procède de la sorte en s’occupant des VLFs inactifs situés avant le début de la partie active du LDF : c’est-à-dire le VLF actif du fichier LDF contenant le numéro de séquence le plus petit appelé MinLSN (ou Minimum Recovery LSN), sachant que si ledit VLF devient inactif, le VLF qui le succèdera comme début de la partie active (i.e., le VLF contenant la plus vieille transaction active/non-validée) du LDF héritera du MinLSN.

Petit exemple illustratif…

Supposons qu’on ait 3 VLFs inactifs non-tronqués (VLFs en orange : VLF1, VLF2 et VLF5) et 2 VLFs actifs (VLFs en bleu : VLF3 et VLF4), et que le début de la partie active du LDF commence par VLF3 qui contient donc le MinLSN :


Lors du lancement d’une opération de troncature, les VLFs VLF1 et VLF2 seront tronqués (VLFs en vert), car tous inactifs et situés avant MinLSN :


Si, entre-temps, le VLF3 devient inactif, MinLSN sera hérité par le VLF actif contenant la transaction la plus vieille (supposons VLF4) :

En relançant une opération de troncature, on obtient le résultat suivant :


Il faut d’ailleurs noter que la troncature d’un VLF actif n’est pas possible, car celui-ci est vital pour la récupération de la base de données (i.e., validation du résultat de ses transactions actives dans la base). De ce fait, pour pouvoir le tronquer, il faut attendre qu’il devienne inactif suite à la validation (ou annulation) de toutes ses transactions actives.

Bien que cette opération permette de limiter la multiplication d’un trop grand nombre de VLF au sein du fichier de journal de transactions LDF (ce qui pourrait nuire aux performances transactionnelles) en permettant à ceux inactifs d’être réutilisés, il a pour mauvaise manie celle de briser la chaîne des sauvegardes transactionnelles en perturbant les LSN.

Pour lancer une troncature manuelle d’un fichier LDF appelé « LaBD_log.LDF » d’une base de données LaBD qui est en mode de récupération FULL ou BULK-LOGGED, il existe plusieurs possibilités du point de vue de T-SQL :

BACKUP LOG LaBD WITH TRUNCATE_ONLY –- ou NO_LOG
GO

Ou bien, et c’est plus recommandé car TRUNCATE_ONLY et NO_LOG sont obsolètes à partir de MSQL 2008, repassage de la base de données en SIMPLE.

Pour les bases de données en SIMPLE, il n’est nul besoin de les tronquer manuellement, le moteur SQL se chargeant de le faire à chaque point de contrôle.

          Shrink

Contrairement à la troncature qui ne permet qu’une réduction « virtuelle » de la taille du fichier de journal de transactions LDF, le shrink est une opération qui permet de le réduire physiquement.

Pour ce faire, il se contente de supprimer les VLFs inactifs et libres en partant de la fin du fichier de journal de transactions LDF.

Dans le cas où une valeur de paramètre de target_size spécifiée au sein de la commande DBCC SHRINKFILE est égale à, supposons, 500 Mo et que le fichier de journal de transactions LDF pèse, imaginons, 1 Go, le moteur SQL devra donc réduire la taille du fichier de journal de transactions LDF à 500 Mo. Pour ce faire, il va s’y prendre comme suit :

  1. Marquage de la zone du LDF par target_size (i.e., 500 Mo) de sorte qu’entre le début du LDF (appelons-le VLF1) et la zone où se trouve target_size (supposons sur un VLF appelé VLF3), l’ensemble de la taille des VLFs présents dans cet intervalle soit égal à 500 Mo.
  2. Suppression des VLFs inactifs et libres situés après la zone target_size, et
    en partant de la fin du fichier.
  3. Si le moteur SQL rencontre un VLF actif (appelons-le VLF3), et que la valeur de target_size n’a toujours pas été pleinement satisfaite, il va alors déplacer le contenu actif de VLF3 vers le premier VLF inactif tronqué (appelons-le VLF1) qu’il trouve au début du fichier de journal de transactions LDF ce qui va rendre VLF3 inactif, son contenu actif initial étant désormais stocké dans VLF1, puis :
    1. Si la base de données est en mode de récupération SIMPLE, le moteur SQL va tronquer le VLF pour le rendre réutilisable/supprimable, puis va procéder à sa suppression et, si la valeur de target_size n’est toujours pas satisfaite, continuer son travail de shrink des VLFs suivants de cette façon jusqu’à ce que le fichier LDF soit réduit à la valeur spécifiée.
    2. Si la base n’est pas en mode de récupération SIMPLE,
      ou qu’aucune troncature n’a eu lieu pendant le shrink, le moteur SQL va alors lancer un message indiquant qu’il n’a pas pu réduire complètement le journal de transactions LDF à la valeur de target_size souhaitée, mais qu’une troncature peut être faite pour satisfaire la suite de l’opération ce qui nécessitera également un nouveau shrink. Et cela, sachant qu’il est possible que la portion active déplacée dans le VLF1 devienne, entre-temps, inactive et donc rapidement libérable.
Il faut noter que le shrink du journal de transactions LDF contrairement à la troncature, ne détruit pas la chaîne des sauvegardes transactionnelles, ni ne fragmente les indexes de la base de données. De ce fait, on pourrait penser que sachant qu’une sauvegarde transactionnelle, en plus de sauvegarder les transactions, tronque les VLFs inactifs, il serait judicieux d’automatiser un shrink du journal de transactions LDF à chaque fin d’une sauvegarde transactionnelle. Mais cependant, cela n’est pas une bonne idée car peut provoquer un certain nombre d’effets négatifs, à savoir :

  • Fragmentation (voire corruption) du fichier de journal de transactions LDF, due à un trop grand nombre de shrinks.
  • Fragmentation du disque où est situé le fichier de journal de transactions LDF.
  • Augmentation significative de la taille de tempDB (cette base ayant tendance à absorber les opérations « lourdes » de type DBCC (DataBase Consistency Check), comme le shrink).

Pour lancer un shrink du fichier LDF, vous pouvez exécuter la commande suivante :

USE LaBD
GO
DBCC SHRINKFILE ('LaBD_Log', <target_size>)
GO

Pour maximiser les résultats du travail de shrink du moteur SQL, une troncature est souhaitable en guise d’opération préliminaire (ce qui va, par contre, briser la chaîne des sauvegardes transactionnelles)[1]. Et surtout, le passage en SIMPLE est plus correct, le moteur SQL se chargeant de tronquer automatiquement les VLFs inactifs du LDF :

USE LaBD
GO
ALTER DATABASE LaBD SET RECOVERY SIMPLE
GO
DBCC SHRINKFILE ('LaBD_Log', <target_size>)
GO

Après cette opération, vous pouvez repasser LaBD à FULL si jamais vous souhaitez reconstituer une nouvelle chaîne de sauvegardes transactionnelles (ce qui nécessitera soit une nouvelle sauvegarde complète, soit une nouvelle sauvegarde différentielle).

Notez que si aucune valeur de target_size n’est spécifiée, le moteur SQL choisira de réduire la taille du fichier LDF à sa taille minimale.

         Contrôle de la fréquence des sauvegardes transactionnelles (TRN)

Les sauvegardes transactionnelles TRN ne permettent pas vraiment de réduire la taille physique d’un fichier de journal de transactions LDF. Toutefois, elles ont l’avantage de tronquer les VLFs inactifs sans que cela ne nuise à la chaîne des sauvegardes transactionnelles, en plus de sauvegarder les dernières transactions ayant eu lieu au sein de la base de données depuis la dernière sauvegarde transactionnelle.

Cela aura donc pour effet de permettre au fichier LDF d’économiser de l’espace en réutilisant ses VLFs inactifs.

C’est donc pourquoi, une bonne approche dans la gestion de la volumétrie d’un fichier de journal de transactions LDF au sein d’une base de données n’ayant pas de mode de récupération SIMPLE consiste à maximiser la fréquence des sauvegardes transactionnelles (toutes les 15 minutes, 30 minutes et heures, par exemple). Cela permettrait de non seulement améliorer considérablement la stratégie de restauration de la base de données, mais également d’économiser potentiellement de l’espace au sein du fichier de journal de transactions LDF, les VLFs inactifs étant libérés et donc rendus réutilisables, voire supprimables, après chaque sauvegarde transactionnelle.

Le lancement de sauvegardes transactionnelles est très simple :

BACKUP LOG LaBD TO DISK= 'S:\SQLBackups\TRN\LaBD\xxxx_LaBD.trn'
GO

Cependant, dans notre cas, il est préférable d’automatiser leurs lancements via un job planifié et exécuté fréquemment par le SQL Agent. Nous aborderons les plans de maintenance dans un futur billet.

Gestion de fichiers de données multiples

La création de plusieurs fichiers LDF au sein d’une même base de données n’apporte aucun gain de performance. En effet, le moteur SQL considère chaque fichier LDF d’une base de données comme une partie d’un fichier LDF unique. Autrement dit, un ensemble de VLFs tel que le dernier VLF physique du 1er LDF est le prédécesseur du 1er VLF physique du 2nd LDF. De plus, l’allocation d’espace pour les fichiers LDF se fait via une approche round-robin (« A chacun son tour ») : si tous les fichiers LDF d’une base de données sont saturés, le moteur SQL va allouer de l’espace d’abord au 1er VLF avant de passer au 2nd LDF (à condition qu’aucun VLF n’ait été, entre-temps, tronqué).

Malgré tout, la création d’un 2nd LDF peut être exceptionnellement utile en cas de saturation du 1er LDF (et en admettant que le disque soit plein, rendant ainsi impossible la création de nouveaux VLFs) au sein d’une base de données où la perte de données est interdite (et donc la rupture de la chaîne des transactions à bannir pour permettre une restauration point-in-time au cas où), la création d’un 2nd LDF temporaire à placer dans un disque ayant de l’espace peut s’avérer utile.

Contrairement aux fichiers de données d’un même filegroup (MDF/NDF), le remplissage de fichiers LDF (hors allocation de nouveaux VLFs) ne se fait pas via une approche round-robin. En effet, tous les fichiers LDF d’une base de données étant vus comme un sous-ensemble d’un même fichier LDF, le moteur SQL stockera des transactions en fonction de l’état des VLFs de chaque journal de transactions de la base de données. Ainsi, supposons qu’une base de données ait 2 fichiers LDF :

  • Si tous les VLFs du 1er fichier LDF sont utilisés (et que donc aucun n’est libre), le 1er VLF libre du 2nd fichier LDF sera utilisé.
  • Si en étant sur le 2nd fichier LDF (suite au point précédent, par exemple), le moteur SQL constate qu’un VLF a, entre temps, été libéré dans le 1er LDF, il placera, alors, la prochaine transaction dans le 1er LDF, même s’il existe au moins un VLF libre au sein du 2nd LDF.

Côté suppression d’un fichier LDF, en admettant qu’il y ait 2 fichiers LDF au sein d’une base de données, il faudrait, après troncature (via repassage en mode de récupération SIMPLE, par exemple), d’abord vider tous les fichiers LDF via l’option EMPTYFILE de la commande « DBCC SHRINKFILE », puis lancer la suppression du 2nd LDF via REMOVE FILE. En voici un exemple de code T-SQL :

USE LaBD
GO
sp_helpfile –- pour afficher toutes les infos sur les fichiers de LaBD
GO
ALTER DATABASE LaBD SET RECOVERY SIMPLE –- troncature des LDFs
GO
DBCC SHRINKFILE(LaBD_log2,EMPTYFILE) –- vidage physique du 2nd LDF
GO –- possibilité de lancer "sp_helpfile" pour connaitre la taille des LDFs
ALTER DATABASE LaBD REMOVE FILE LaBD_log2 –- suppression du 2nd LDF
GO

Même si la suppression d’un LDF peut se faire via l’interface de SQL Server Management Studio (en allant dans les propriétés de la base de données) il est plus conventionnel et efficace de s’y prendre en T-SQL.

Remarquez que si, malgré le vidage des fichiers LDF, l’erreur « The file xxx.ldf cannot be removed because it is not empty. » est levée, il faudrait donc taper la commande « DBCC LOGINFO(LaBD) » afin de voir si :

  • Tous les VLFs du LDF à supprimer sont inactifs (valeur 0 dans la colonne Status).
  • Il y a, au moins, 2 VLFs (inactifs ou actifs, peu importe) dans le 1er LDF.

Si le 2ème point n’est pas respecté, il est possible de créer manuellement des VLFs supplémentaires en allouant de l’espace au 1er LDF, soit via SQL Server Management Studio (dans la section Files de la fenêtre de propriétés de la base de données), soit via T-SQL en exécutant cet exemple de code (la valeur de SIZE doit être supérieure à la taille initiale actuelle du LDF) :

ALTER DATABASE LaBD MODIFY FILE (
     NAME ='LaBD_log',–- nom logique du 1er LDF
     SIZE = 124 MB) –- on donne arbitrairement 124 Mo de taille au LDF
GO

Si, par contre, le 1er point est l’élément bloquant, il faudra soit attendre que les transactions actives se terminent, soit les annuler. Vous pouvez lancer « DBCC OPENTRAN » pour connaître les transactions en cours d’exécution, ou exécuter la requête suivante (qui ne fonctionne que sur les versions supérieures ou égales à SQL Server 2005) :

SELECT session_id,
     start_time,
     text,
     database_id
FROM sys.dm_exec_requests
     CROSS APPLY sys.dm_exec_sql_text(sql_handle)
GO
Notez qu’un fichier LDF d’une base de données ne peut pas être vidé si cette dernière est en cours de sauvegarde. Vous pouvez exécuter la requête « SELECT name, log_reuse_wait_desc FROM sys.databases » pour vérifier l’état d’une base de données, en espérant qu’il ait idéalement la valeur NOTHING. Plus de détail dans un autre billet à ce sujet.

Par ailleurs, pour des raisons d’intégrité, de sécurité et de consistance, il est important de ne jamais supprimer le 1er LDF d’une base de données, qui est le journal de transactions principal.

En résumé…

Le fichier de journal de transactions est une pièce importante de l’architecture d’une base de données, et est également un élément essentiel à connaître lors de l’exploitation d’une base de données SQL Server.

A la vue des différents éléments abordés à travers ce billet, une bonne pratique générale consistera à :

  • Placer tout fichier de journal de transactions sur un LUN dédié favorisant les écritures (RAID 1 ou RAID 1+0 ou, mieux, RAID 10).
  • Choisir le bon mode de récupération en fonction des besoins (idéalement FULL ou BULK-LOGGED pour les bases de production critiques et SIMPLE pour les bases de développement ou de tests).
  • Bannir, dans la mesure du possible, l’utilisation de la troncature (car bris de la chaîne des sauvegardes transactionnelles) et du shrink (car fragmentation possible du fichier et/ou du disque d’accueil) du fichier de journal de transactions LDF au sein d’une base de données de production critique.
  • Multiplier au maximum la réalisation des sauvegardes du journal de transactions LDF afin de mieux gérer la taille, et donc la croissance, du fichier de journal de transactions sans nuire au fonctionnement de la chaîne de sauvegardes des transactions TRN associée.

Il existe diverses façons de connaître l’état (taille, statut des transactions présentes, etc…) d’un fichier LDF, que ce soit via des requêtes systèmes ou les DMV (Dynamic Management Views) ou même via le Performance Dashboard Reports[2].
Dans le futur, nous traiterons de l’audit avancé d’un journal de transactions.
–-

Références


[1]On rappelle que le shrink ne fonctionne que s’il y a, au moins, un VLF inactif et libre. Et dans le cas où cette contrainte n’est pas respectée, une troncature au préalable devient donc nécessaire.[2]Pour être exact, un rapport de performance appelé Disk Usage est accessible dans le menu contextuel Reports via clic-droit sur une base de données, au sein de l’explorateur d’objets de SQL Server Management Studio. A droite du rapport se trouve un camembert bicolore représentant la répartition de l’utilisation du LDF, où l’espace utilisé correspond à l’ensemble des VLFs utilisés et non-libérés et l’espace inutilisé à l’ensemble des VLFs réutilisables (car libérés/tronqués) et/ou l’ensemble des VLFs n’ayant jamais été encore utilisés. Nous abroderons les Performance Dashboard Reports dans un autre billet.
Publicités

Un commentaire sur “[SQL Server] Gestion d’un journal de transactions

  1. Bonsoir Yves,

    Oui, en multipliant les sauvegardes transactionnelles afin de tronquer le journal des logs. Amorcer un shrink permettra, par la suite, de réduire physiquement – dans la mesure du possible – ledit journal en supprimant les VLFs inactifs.

    Plus de détails explicatifs dans ce billet.

    @+!

    M.


    Yves BOTTIN – Submitted on 2014/05/14 at 08:40
    Bonjour Mohamed,

    J’ai un souci d’augmentation importante de la taille du fichier ldf sur une base de données en mirroir. Ce comportement semble normal dans le cadre du miroring, mais y a t’il de le réduire ?

    D’avance merci

Laisser un commentaire

Entrez vos coordonnées ci-dessous ou cliquez sur une icône pour vous connecter:

Logo WordPress.com

Vous commentez à l'aide de votre compte WordPress.com. Déconnexion / Changer )

Image Twitter

Vous commentez à l'aide de votre compte Twitter. Déconnexion / Changer )

Photo Facebook

Vous commentez à l'aide de votre compte Facebook. Déconnexion / Changer )

Photo Google+

Vous commentez à l'aide de votre compte Google+. Déconnexion / Changer )

Connexion à %s