Guide d’architecture et gestion du journal des transactions SQL ServerSQL Server Transaction Log Architecture and Management Guide

S’APPLIQUE À : ouiSQL Server ouiAzure SQL Database ouiAzure SQL Data Warehouse ouiParallel Data Warehouse APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure SQL Data Warehouse yesParallel Data Warehouse

Chaque base de données SQL ServerSQL Server possède un journal des transactions qui enregistre toutes les transactions et les modifications apportées par chacune d'entre elles.Every SQL ServerSQL Server database has a transaction log that records all transactions and the database modifications that are made by each transaction. Le journal des transactions est un composant essentiel de la base de données et, en cas de défaillance du système, vous pouvez en avoir besoin pour rétablir la cohérence de la base de données.The transaction log is a critical component of the database and, if there is a system failure, the transaction log might be required to bring your database back to a consistent state. Ce guide contient des informations sur l'architecture physique et logique du journal des transactions.This guide provides information about the physical and logical architecture of the transaction log. Ces informations pourront vous aider à gérer plus efficacement les journaux des transactions.Understanding the architecture can improve your effectiveness in managing transaction logs.

Architecture logique du journal des transactionsTransaction Log Logical Architecture

Le journal des transactions de SQL ServerSQL Server fonctionne de façon logique comme s'il s'agissait d'une chaîne d'enregistrements de journal.The SQL ServerSQL Server transaction log operates logically as if the transaction log is a string of log records. Chacun de ces enregistrements est identifié par un numéro séquentiel dans le journal (LSN).Each log record is identified by a log sequence number (LSN). Chaque nouvel enregistrement est écrit à la fin logique du journal avec un LSN supérieur à celui de l'enregistrement qui le précède.Each new log record is written to the logical end of the log with an LSN that is higher than the LSN of the record before it. Lors de leur création, les enregistrements de journal sont stockés séquentiellement.Log records are stored in a serial sequence as they are created. Chacun d'eux contient l'ID de la transaction à laquelle il appartient.Each log record contains the ID of the transaction that it belongs to. Pour chaque transaction, tous les enregistrements de journal associés sont reliés de façon individuelle dans une chaîne grâce aux pointeurs arrière qui accélèrent la restauration de la transaction.For each transaction, all log records associated with the transaction are individually linked in a chain using backward pointers that speed the rollback of the transaction.

Les enregistrements de journal relatifs aux modifications de données consignent soit l'opération logique effectuée, soit les images avant/après des données modifiées.Log records for data modifications record either the logical operation performed or they record the before and after images of the modified data. L'image avant est une copie des données avant que l'opération n'ait été effectuée, tandis que l'image après est une copie des données après que l'opération a été effectuée.The before image is a copy of the data before the operation is performed; the after image is a copy of the data after the operation has been performed.

Les étapes pour récupérer une opération dépendent du type de journal d'enregistrement :The steps to recover an operation depend on the type of log record:

  • Opération logique enregistréeLogical operation logged

    • Si vous repositionnez l'opération logique avant, elle est effectuée à nouveau.To roll the logical operation forward, the operation is performed again.

    • Si vous annulez l'opération logique, l'opération inverse est effectuée.To roll the logical operation back, the reverse logical operation is performed.

  • Image avant et après enregistréesBefore and after image logged

    • Si vous repositionnez l'opération avant, l'image après est appliquée.To roll the operation forward, the after image is applied.

    • Si vous annulez l'opération, l'image avant est appliquée.To roll the operation back, the before image is applied.

De nombreux types d'opérations sont enregistrés dans le journal des transactions.Many types of operations are recorded in the transaction log. Ces opérations comprennent :These operations include:

  • Le début et la fin de chaque transaction.The start and end of each transaction.

  • Chaque modification de données (insertion, mise à jour ou suppression).Every data modification (insert, update, or delete). Il s'agit des modifications apportées aux tables, y compris les tables système, par les procédures stockées système ou les instructions DDL (Data Definition Language).This includes changes by system stored procedures or data definition language (DDL) statements to any table, including system tables.

  • Chaque allocation ou désallocation de page et d'étendue.Every extent and page allocation or deallocation.

  • Création ou suppression d'une table ou d'un index.Creating or dropping a table or index.

Les opérations de restauration sont également consignées dans le journal.Rollback operations are also logged. Chaque transaction réserve de l'espace dans le journal des transactions afin qu'il existe suffisamment d'espace journal pour prendre en charge une restauration déclenchée par une instruction de restauration explicite ou par la détection d'une erreur.Each transaction reserves space on the transaction log to make sure that enough log space exists to support a rollback that is caused by either an explicit rollback statement or if an error is encountered. Le volume d'espace réservé dépend des opérations effectuées dans la transaction, mais il est généralement égal au volume d'espace utilisé pour la journalisation de chaque opération.The amount of space reserved depends on the operations performed in the transaction, but generally it is equal to the amount of space used to log each operation. Cet espace réservé est libéré lorsque la transaction est terminée.This reserved space is freed when the transaction is completed.

La section du fichier journal comprise entre le premier enregistrement de journal nécessaire à une restauration portant sur l’ensemble de la base de données et la fin du journal représente la partie active du journal, également appelée le journal actif.The section of the log file from the first log record that must be present for a successful database-wide rollback to the last-written log record is called the active part of the log, or the active log. Cette section est indispensable pour procéder à une récupération complète de la base de données.This is the section of the log required to a full recovery of the database. Aucune partie de ce journal actif ne peut être tronquée.No part of the active log can ever be truncated. Le LSN (numéro séquentiel dans le journal) de ce premier enregistrement est le LSN de récupération minimum (MinLSN) .The log sequence number (LSN) of this first log record is known as the minimum recovery LSN (MinLSN).

Architecture physique du journal des transactionsTransaction Log Physical Architecture

Le journal des transactions d'une base de données s'étend sur un ou plusieurs fichiers physiques.The transaction log in a database maps over one or more physical files. D'un point de vue conceptuel, le fichier journal est une chaîne d'enregistrements.Conceptually, the log file is a string of log records. D'un point de vue physique, la séquence des enregistrements du journal est stockée de façon efficace dans l'ensemble de fichiers physiques qui implémente le journal des transactions.Physically, the sequence of log records is stored efficiently in the set of physical files that implement the transaction log. Chaque base de données doit posséder au moins un fichier journal.There must be at least one log file for each database.

Le Moteur de base de données SQL ServerSQL Server Database Engine divise chaque fichier journal physique en un certain nombre de fichiers journaux virtuels.The Moteur de base de données SQL ServerSQL Server Database Engine divides each physical log file internally into a number of virtual log files (VLFs). La taille et le nombre de ces fichiers journaux virtuels sont variables.Virtual log files have no fixed size, and there is no fixed number of virtual log files for a physical log file. Le Moteur de base de donnéesDatabase Engine choisit dynamiquement la taille des fichiers journaux virtuels en créant ou en étendant des fichiers journaux.The Moteur de base de donnéesDatabase Engine chooses the size of the virtual log files dynamically while it is creating or extending log files. Le Moteur de base de donnéesDatabase Engine essaie de ne conserver qu’un petit nombre de fichiers virtuels.The Moteur de base de donnéesDatabase Engine tries to maintain a small number of virtual files. Après une extension du fichier journal, la taille des fichiers virtuels est la somme de la taille du journal existant et de la taille du nouvel incrément de fichier.The size of the virtual files after a log file has been extended is the sum of the size of the existing log and the size of the new file increment. La taille et le nombre des fichiers journaux virtuels ne peuvent être ni configurés, ni définis par les administrateurs.The size or number of virtual log files cannot be configured or set by administrators.

Notes

La création du fichier journal virtuel suit cette méthode :Virtual log file (VLF) creation follows this method:

  • Si la croissance suivante est inférieure à 1/8 de la taille physique actuelle du journal, 1 fichier journal virtuel qui couvre la taille de croissance est créé (à partir de SQL Server 2014 (12.x)SQL Server 2014 (12.x))If the next growth is less than 1/8 of current log physical size, then create 1 VLF that covers the growth size (Starting with SQL Server 2014 (12.x)SQL Server 2014 (12.x))
  • Si la croissance suivante est supérieure à 1/8 de la taille actuelle du journal, utilisez la méthode antérieure à 2014 :If the next growth is more than 1/8 of the current log size, then use the pre-2014 method:
    • Si la croissance est inférieure à 64 Mo, 4 fichiers journaux virtuels qui couvrent la taille de croissance sont créés (par exemple, pour une croissance de 1 Mo, quatre fichiers journaux virtuels de 256 Ko sont créés)If growth is less than 64MB, create 4 VLFs that cover the growth size (e.g. for 1 MB growth, create four 256KB VLFs)
    • Si la croissance se situe entre 64 Mo et 1 Go, 8 fichiers journaux virtuels qui couvrent la taille de croissance sont créés (par exemple, pour une croissance de 512 Mo, huit fichiers journaux virtuels de 64 Mo sont créés)If growth is from 64MB up to 1GB, create 8 VLFs that cover the growth size (e.g. for 512MB growth, create eight 64MB VLFs)
    • Si la croissance est supérieure à 1 Go, 16 fichiers journaux virtuels qui couvrent la taille de croissance sont créés (par exemple, pour une croissance de 8 Go, seize fichiers journaux virtuels de 512 Ko sont créés)If growth is larger than 1GB, create 16 VLFs that cover the growth size (e.g. for 8 GB growth, create sixteen 512MB VLFs)

Si la taille des fichiers journaux s’accroît par de nombreux petits incréments, de nombreux fichiers journaux virtuels sont créés.If the log files grow to a large size in many small increments, they will have many virtual log files. ce qui peut ralentir le démarrage de la base de données ainsi que les opérations de sauvegarde et de restauration.This can slow down database startup and also log backup and restore operations. À l’inverse, si les fichiers journaux sont définis avec une grande taille et avec un seul incrément ou peu d’incréments, un petit nombre de fichiers journaux virtuels très volumineux sont créés.Conversely, if the log files are set to a large size with few or just one increment, they will have few very large virtual log files. Pour plus d’informations sur une estimation correcte de la taille nécessaire et de la croissance automatique d’un journal des transactions, reportez-vous à la section Recommandations de Gérer la taille du fichier journal des transactions.For more information on properly estimating the required size and autogrow setting of a transaction log, refer to the Recommendations section of Manage the size of the transaction log file.

Il est conseillé d’affecter aux fichiers journaux une taille proche de la taille finale nécessaire, avec les incréments nécessaires pour atteindre la distribution optimale des fichiers journaux virtuels, et un incrément_accroissement relativement important.We recommend that you assign log files a size value close to the final size required, using the required increments to achieve optimal VLF distribution, and also have a relatively large growth_increment value. Consultez le conseil ci-dessous pour déterminer la distribution optimale des fichiers journaux virtuels pour la taille actuelle du journal des transactions.See the tip below to determine the optimal VLF distribution for the current transaction log size.

  • La valeur size, telle que définie par l’argument SIZE de ALTER DATABASE est la taille initiale du fichier journal.The size value, as set by the SIZE argument of ALTER DATABASE is the initial size for the log file.
  • La valeur de incrément_accroissement (également appelée valeur d’accroissement automatique), telle que définie par l’argument FILEGROWTH de ALTER DATABASE, correspond à la quantité d’espace ajoutée au fichier chaque fois de l’espace supplémentaire s’avère nécessaire.The growth_increment value (also referred as the autogrow value), as set by the FILEGROWTH argument of ALTER DATABASE, is the amount of space added to the file every time new space is required.

Pour plus d’informations sur les arguments FILEGROWTH et SIZE de ALTER DATABASE, consultez Options de fichiers et de groupes de fichiers (Transact-SQL) ALTER DATABASE.For more information on FILEGROWTH and SIZE arguments of ALTER DATABASE, see ALTER DATABASE (Transact-SQL) File and Filegroup Options.

Conseil

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.To determine the optimal VLF distribution for the current transaction log size of all databases in a given instance, and the required growth increments to achieve the required size, see this script.

Le journal des transactions est un fichier cumulatif.The transaction log is a wrap-around file. Considérons, par exemple, une base de données possédant un fichier journal physique divisé en quatre fichiers journaux virtuels.For example, consider a database with one physical log file divided into four VLFs. Lors de la création de la base de données, le fichier journal logique commence au début du fichier journal physique.When the database is created, the logical log file begins at the start of the physical log file. Les nouveaux enregistrements du journal sont ajoutés à la fin du journal logique, qui s'étend vers la fin du journal physique.New log records are added at the end of the logical log and expand toward the end of the physical log. Le fait de tronquer le journal permet de libérer tous les journaux virtuels dont les enregistrements précèdent tous le MinLSN (numéro séquentiel dans le journal minimum).Log truncation frees any virtual logs whose records all appear in front of the minimum recovery log sequence number (MinLSN). Le MinLSN est le numéro séquentiel dans le journal du plus ancien enregistrement du journal requis pour une opération de restauration réussie de l’ensemble de la base de données.The MinLSN is the log sequence number of the oldest log record that is required for a successful database-wide rollback. Le journal des transactions de la base de données exemple ressemblerait à celui de l'illustration suivante :The transaction log in the example database would look similar to the one in the following illustration.

tranlog3

Lorsque la fin du journal logique atteint la fin du fichier journal physique, le nouvel enregistrement du journal revient au début du fichier journal physique.When the end of the logical log reaches the end of the physical log file, the new log records wrap around to the start of the physical log file.

tranlog4

Le cycle se répète indéfiniment tant que la fin du journal logique n'a pas atteint le début du journal logique.This cycle repeats endlessly, as long as the end of the logical log never reaches the beginning of the logical log. Si les anciens enregistrements du journal sont tronqués suffisamment souvent pour laisser de la place à tous les nouveaux enregistrements créées jusqu'au point de contrôle suivant, le journal ne se remplit jamais.If the old log records are truncated frequently enough to always leave sufficient room for all the new log records created through the next checkpoint, the log never fills. Si la fin du journal logique atteint le début du journal logique, l'une ou l'autre des situations suivantes se produit :However, if the end of the logical log does reach the start of the logical log, one of two things occurs:

  • Si le paramètre FILEGROWTH est activé pour le journal et que l’espace disque est suffisant, le fichier s’étend en fonction de la taille spécifiée dans le paramètre growth_increment, les nouveaux enregistrements du journal étant ajoutés à l’extension.If the FILEGROWTH setting is enabled for the log and space is available on the disk, the file is extended by the amount specified in the growth_increment parameter and the new log records are added to the extension. Pour plus d’informations sur le paramètre FILEGROWTH, consultez Options de fichiers et de groupes de fichiers ALTER DATABASE (Transact-SQL).For more information about the FILEGROWTH setting, see ALTER DATABASE File and Filegroup Options (Transact-SQL).

  • Si le paramètre FILEGROWTH n’est pas activé ou si l’espace disque réservé au fichier journal est inférieur à la taille spécifiée dans le paramètre growth_increment, l’erreur 9002 est générée.If the FILEGROWTH setting is not enabled, or the disk that is holding the log file has less free space than the amount specified in growth_increment, an 9002 error is generated. Pour plus d’informations, consultez Résoudre les problèmes liés à un journal des transactions saturé.Refer to Troubleshoot a Full Transaction Log for more information.

Si le journal contient plusieurs fichiers journaux physiques, le journal logique va se déplacer dans tous les fichiers journaux physiques avant de revenir au début du premier fichier journal physique.If the log contains multiple physical log files, the logical log will move through all the physical log files before it wraps back to the start of the first physical log file.

Important

Pour plus d’informations sur la gestion de la taille du journal des transactions, consultez Gérer la taille du fichier journal des transactions.For more information about transaction log size management, see Manage the Size of the Transaction Log File.

Troncation de journalLog Truncation

La troncation du journal est essentielle pour empêcher que le journal se remplisse.Log truncation is essential to keep the log from filling. La troncation du journal supprime les fichiers journaux virtuels inactifs du journal des transactions logique d'une base de données SQL ServerSQL Server , ce qui libère de l'espace dans le journal logique de façon à ce qu'il soit réutilisé par le journal des transactions physique.Log truncation deletes inactive virtual log files from the logical transaction log of a SQL ServerSQL Server database, freeing space in the logical log for reuse by the physical transaction log. Si un journal des transactions n'était jamais tronqué, il finirait par occuper tout l'espace disque alloué à ses fichiers journaux physiques.If a transaction log were never truncated, it would eventually fill all the disk space that is allocated to its physical log files. Toutefois, une opération de point de contrôle est requise avant que le journal des transactions puisse être tronqué.However, before the log can be truncated, a checkpoint operation must occur. Un point de contrôle écrit les pages modifiées en mémoire actuelles (appelées pages de modifications) et les informations du journal des transactions de la mémoire vers le disque.A checkpoint writes the current in-memory modified pages (known as dirty pages) and transaction log information from memory to disk. Lorsque le point de contrôle est créé, la partie inactive du journal des transactions est marquée comme réutilisable.When the checkpoint is performed, the inactive portion of the transaction log is marked as reusable. Après cela, elle peut être libérée par troncation du journal.Thereafter, the inactive portion can be freed by log truncation. Pour plus d’informations sur les points de contrôle, consultez Points de contrôle de base de données (SQL Server).For more information about checkpoints, see Database Checkpoints (SQL Server).

Les illustrations suivantes montrent un journal des transactions avant et après une troncation.The following illustrations show a transaction log before and after truncation. La première illustration montre un journal des transactions qui n'a jamais été tronqué.The first illustration shows a transaction log that has never been truncated. Actuellement, quatre fichiers journaux virtuels sont utilisés par le journal logique.Currently, four virtual log files are in use by the logical log. Le journal logique commence avant le premier fichier journal virtuel et se termine au journal virtuel 4.The logical log starts at the front of the first virtual log file and ends at virtual log 4. L'enregistrement NSEmin se trouve dans le journal virtuel 3.The MinLSN record is in virtual log 3. Les journaux virtuels 1 et 2 contiennent uniquement des enregistrements de journal inactifs.Virtual log 1 and virtual log 2 contain only inactive log records. Ces enregistrements peuvent être tronqués.These records can be truncated. Le journal virtuel 5 est encore inutilisé et ne fait pas partie du journal logique actuel.Virtual log 5 is still unused and is not part of the current logical log.

tranlog2

La deuxième illustration montre le journal après sa troncation.The second illustration shows how the log appears after being truncated. Les journaux virtuels 1 et 2 ont été libérés en vue de leur réutilisation.Virtual log 1 and virtual log 2 have been freed for reuse. Le journal logique commence désormais au début du journal virtuel 3.The logical log now starts at the beginning of virtual log 3. Le journal virtuel 5 est encore inutilisé et ne fait pas partie du journal logique actuel.Virtual log 5 is still unused, and it is not part of the current logical log.

tranlog3

La troncation du journal se produit automatiquement après les événements suivants, à moins qu'elle ne soit retardée pour une raison quelconque :Log truncation occurs automatically after the following events, except when delayed for some reason:

  • En mode de récupération simple, après un point de contrôle.Under the simple recovery model, after a checkpoint.
  • En mode de restauration complète ou en mode de récupération utilisant les journaux de transactions, après une sauvegarde du journal, si un point de contrôle s'est produit depuis la dernière sauvegarde.Under the full recovery model or bulk-logged recovery model, after a log backup, if a checkpoint has occurred since the previous backup.

La troncation du journal peut être retardée pour différents motifs.Log truncation can be delayed by a variety of factors. En cas de retard prolongé de la troncation du journal, le journal des transactions peut se remplir complètement.In the event of a long delay in log truncation, the transaction log can fill up. Pour plus d’informations, consultez Facteurs pouvant retarder la troncation du journal et Résoudre les problèmes liés à un journal des transactions saturé (erreur SQL Server 9002).For information, see Factors that can delay log truncation and Troubleshoot a Full Transaction Log (SQL Server Error 9002).

Journal des transactions à écriture anticipéeWrite-Ahead Transaction Log

Cette section décrit le rôle que joue le journal des transactions à écriture anticipée (journal WAL) au niveau de l'enregistrement sur disque des modifications apportées aux données.This section describes the role of the write-ahead transaction log in recording data modifications to disk. SQL ServerSQL Server utilise un algorithme WAL (write-ahead logging) qui garantit qu’aucune modification de données n’est écrite sur le disque avant l’écriture du journal associé sur celui-ci.uses a write-ahead logging (WAL) algorithm, which guarantees that no data modifications are written to disk before the associated log record is written to disk. Ainsi, les propriétés ACID (Atomicité, Cohérence, Isolation et Durabilité) d'une transaction sont conservées.This maintains the ACID properties for a transaction.

Pour comprendre le fonctionnement du journal à écriture anticipée, il est important que vous sachiez comment les données modifiées sont écrites sur le disque.To understand how the write-ahead log works, it is important for you to know how modified data is written to disk. SQL ServerSQL Server gère un cache des tampons dans lequel il lit les pages de données lorsque celles-ci doivent être extraites.maintains a buffer cache into which it reads data pages when data must be retrieved. Lorsqu’une page est modifiée dans le cache des tampons, elle n’est pas réécrite immédiatement sur le disque, mais elle est marquée comme erronée.When a page is modified in the buffer cache, it is not immediately written back to disk; instead, the page is marked as dirty. Une page peut avoir plusieurs écritures logiques avant son écriture physique sur le disque.A data page can have more than one logical write made before it is physically written to disk. Pour chaque écriture logique, un enregistrement du journal des transactions est inséré dans le cache du journal qui enregistre la modification.For each logical write, a transaction log record is inserted in the log cache that records the modification. L'enregistrement doit être écrit sur le disque avant que la page de modifications associée n'ait été supprimée du cache et écrite sur le disque.The log records must be written to disk before the associated dirty page is removed from the buffer cache and written to disk. Le processus de point de contrôle analyse régulièrement le cache à la recherche de tampons contenant des pages issues d'une base de données spécifiée et écrit toutes les pages de modifications sur le disque.The checkpoint process periodically scans the buffer cache for buffers with pages from a specified database and writes all dirty pages to disk. Les points de contrôle permettent une récupération ultérieure du système en créant un point où toutes les pages de modifications sont effectivement écrites sur le disque.Checkpoints save time during a later recovery by creating a point at which all dirty pages are guaranteed to have been written to disk.

Le processus d'écriture d'une page de données modifiée, du cache des tampons vers le disque, porte le nom de vidage.Writing a modified data page from the buffer cache to disk is called flushing the page. SQL ServerSQL Server possède une logique qui empêche la suppression d’une page de modifications avant que l’enregistrement du journal associé n’ait été écrit.has logic that prevents a dirty page from being flushed before the associated log record is written. Les enregistrements de journal sont écrits sur le disque quand les tampons de journaux sont vidés.Log records are written to disk when the log buffers are flushed. Cela se produit chaque fois qu’une transaction est validée ou que les tampons de journaux sont saturés.This happens whenever a transaction commits or the log buffers become full.

Sauvegardes du journal de transactionsTransaction Log Backups

Cette section présente les concepts sur la sauvegarde et la restauration (application) des journaux de transactions.This section presents concepts about how to back up and restore (apply) transaction logs. En mode de récupération complète et en mode de récupération utilisant les journaux de transactions, la sauvegarde régulière des journaux de transactions (sauvegardes des journaux) est indispensable pour pouvoir récupérer les données.Under the full and bulk-logged recovery models, taking routine backups of transaction logs (log backups) is necessary for recovering data. Sauvegardez le journal pendant l'exécution d'une sauvegarde complète.You can back up the log while any full backup is running. Pour plus d’informations sur les modes de récupération, consultez Sauvegarde et restauration des bases de données SQL Server.For more information about recovery models, see Back Up and Restore of SQL Server Databases.

Avant de pouvoir créer la première sauvegarde du journal, vous devez créer une sauvegarde complète, telle qu'une sauvegarde de base de données ou la première d'une série de sauvegardes de fichiers.Before you can create the first log backup, you must create a full backup, such as a database backup or the first in a set of file backups. La restauration d'une base de données à l'aide seulement de sauvegardes de fichiers peut être complexe.Restoring a database by using only file backups can become complex. Par conséquent, nous vous recommandons de commencer par une sauvegarde de base de données complète dès que possible.Therefore, we recommend that you start with a full database backup when you can. Puis, sauvegardez le journal des transactions régulièrement.Thereafter, backing up the transaction log regularly is necessary. Vous pouvez ainsi réduire les risques de perte de travail mais aussi permettre la troncation du journal des transactions.This not only minimizes work-loss exposure but also enables truncation of the transaction log. En général, le journal des transactions est tronqué après chaque sauvegarde de journal conventionnelle.Typically, the transaction log is truncated after every conventional log backup.

Important

Nous vous recommandons d’effectuer des sauvegardes de journaux suffisamment fréquentes pour répondre à vos besoins, en particulier votre tolérance des pertes de données comme celles causées par un stockage de journal endommagé.We recommend taking frequent enough log backups to support your business requirements, specifically your tolerance for work loss such as might be caused by a damaged log storage. La fréquence appropriée des sauvegardes de journaux dépend de votre gestion des risques liés aux pertes de données et du nombre de sauvegardes de journaux qu'il vous est possible de stocker, gérer et potentiellement restaurer.The appropriate frequency for taking log backups depends on your tolerance for work-loss exposure balanced by how many log backups you can store, manage, and, potentially, restore. Pensez à l’objectif de délai de récupération et à l’objectif de point de récupération quand vous implémentez votre stratégie de récupération, en particulier la cadence des sauvegardes de fichier journal.Think about the required RTO and RPO when implementing your recovery strategy, and specifically the log backup cadence. Réaliser une sauvegarde de journal tous les 15 à 30 minutes peut être suffisant.Taking a log backup every 15 to 30 minutes might be enough. Si vos besoins nécessitent de minimiser les risques de perte de travail, vous devez envisager des sauvegardes de journaux plus fréquentes.If your business requires that you minimize work-loss exposure, consider taking log backups more frequently. Une meilleure fréquence pour les sauvegardes de fichiers journaux offre l'avantage d'augmenter la fréquence de la troncation des journaux qui produit des fichiers journaux plus petits.More frequent log backups have the added advantage of increasing the frequency of log truncation, resulting in smaller log files.

Important

Pour limiter le nombre des sauvegardes de fichiers journaux à restaurer, il est essentiel de sauvegarder vos données régulièrement.To limit the number of log backups that you need to restore, it is essential to routinely back up your data. Vous pouvez, par exemple, planifier une sauvegarde complète hebdomadaire et des sauvegardes différentielles quotidiennes de la base de données.For example, you might schedule a weekly full database backup and daily differential database backups.
Là encore, pensez à l’objectif de délai de récupération et à l’objectif de point de récupération quand vous implémentez votre stratégie de récupération, en particulier la cadence des sauvegardes différentielles et complètes de base de données.Again, think about the required RTO and RPO when implementing your recovery strategy, and specifically the full and differential database backup cadence.

Pour plus d’informations sur les sauvegardes des journaux de transactions, consultez Sauvegardes des journaux de transactions (SQL Server).For more information about transaction log backups, see Transaction Log Backups (SQL Server).

Séquence de journaux de transactions consécutifsThe Log Chain

Une séquence continue de sauvegardes de journaux s’appelle une séquence de journaux de transactions consécutifs.A continuous sequence of log backups is called a log chain. Une séquence de journaux de transactions consécutifs commence par une sauvegarde complète de la base de données.A log chain starts with a full backup of the database. Généralement, une nouvelle séquence de journaux de transactions consécutifs ne démarre que lorsque la base de données est sauvegardée pour la première fois ou après que le mode de récupération simple est remplacé par le mode de récupération complète ou le mode de récupération utilisant les journaux de transactions.Usually, a new log chain is only started when the database is backed up for the first time or after the recovery model is switched from simple recovery to full or bulk-logged recovery. Si vous ne choisissez pas de remplacer les jeux de sauvegarde existants lors de la création d'une sauvegarde complète de base de données, la séquence de journaux de transactions consécutifs existante reste intacte.Unless you choose to overwrite existing backup sets when creating a full database backup, the existing log chain remains intact. Grâce à la séquence de journaux de transactions consécutifs intacte, vous pouvez restaurer votre base de données à partir d'une sauvegarde complète de base de données du support de sauvegarde, suivie de toutes les sauvegardes de fichiers journaux suivantes jusqu'à votre point de récupération.With the log chain intact, you can restore your database from any full database backup in the media set, followed by all subsequent log backups up through your recovery point. Le point de récupération peut être la fin de la dernière sauvegarde de fichier journal ou un point de récupération spécifique dans chacune des sauvegardes de fichiers journaux.The recovery point could be the end of the last log backup or a specific recovery point in any of the log backups. Pour plus d’informations, consultez Sauvegardes du journal des transactions (SQL Server).For more information, see Transaction Log Backups (SQL Server).

Pour restaurer une base de données jusqu'au point d'échec, la séquence de journaux de transactions consécutifs doit être intacte.To restore a database up to the point of failure, the log chain must be intact. Autrement dit, la séquence ininterrompue des sauvegardes des journaux de transactions doit aller jusqu'au point de défaillance.That is, an unbroken sequence of transaction log backups must extend up to the point of failure. Le point de commencement de cette séquence du journal dépend du type des sauvegardes de données que vous restaurez : base de données, partielle ou fichiers.Where this sequence of log must start depends on the type of data backups you are restoring: database, partial, or file. Pour une sauvegarde partielle ou de base de données, la séquence des sauvegardes des journaux doit s'étendre à partir de la fin d'une sauvegarde partielle ou de base de données.For a database or partial backup, the sequence of log backups must extend from the end of a database or partial backup. Pour un jeu de sauvegardes de fichiers, la séquence des sauvegardes des journaux doit s'étendre à partir du début d'un jeu complet de sauvegardes de fichiers.For a set of file backups, the sequence of log backups must extend from the start of a full set of file backups. Pour plus d’informations, consultez Appliquer les sauvegardes du journal de transactions (SQL Server).For more information, see Apply Transaction Log Backups (SQL Server).

Restaurer des sauvegardes du journalRestore Log Backups

La restauration d'une sauvegarde de journal restaure par progression les modifications enregistrées dans le journal des transactions, afin de recréer l'état exact de la base de données qui existait au début de la sauvegarde du journal.Restoring a log backup rolls forward the changes that were recorded in the transaction log to re-create the exact state of the database at the time the log backup operation started. Lorsque vous restaurez une base de données, vous devez restaurer les sauvegardes des journaux créées à la suite de la sauvegarde complète de base de données que vous restaurez ou à partir de la première sauvegarde de fichiers que vous restaurez.When you restore a database, you will have to restore the log backups that were created after the full database backup that you restore, or from the start of the first file backup that you restore. En règle générale, vous devez restaurer une série de sauvegardes de journaux jusqu'au point de récupération, après avoir restauré les données les plus récentes ou une sauvegarde différentielle.Typically, after you restore the most recent data or differential backup, you must restore a series of log backups until you reach your recovery point. Ensuite, vous récupérez la base de données.Then, you recover the database. Cette opération restaure toutes les transactions qui n'étaient pas terminées au début de la récupération et place la base de données en ligne.This rolls back all transactions that were incomplete when the recovery started and brings the database online. Une fois la base de données récupérée, vous ne pouvez plus restaurer des sauvegardes.After the database has been recovered, you cannot restore any more backups. Pour plus d’informations, consultez Appliquer les sauvegardes du journal de transactions (SQL Server).For more information, see Apply Transaction Log Backups (SQL Server).

Points de contrôle et partie active du journalCheckpoints and the Active Portion of the Log

Les points de contrôle vident les pages de données incorrectes de la mémoire cache de la base de données active sur le disque,Checkpoints flush dirty data pages from the buffer cache of the current database to disk. ce qui réduit la partie active du journal devant être traitée durant une récupération complète d'une base de données.This minimizes the active portion of the log that must be processed during a full recovery of a database. Au cours d'une récupération complète, les types d'actions suivants sont effectués :During a full recovery, the following types of actions are performed:

  • Les enregistrements de journal concernant des modifications qui n'ont pas été vidées sur le disque avant l'arrêt du système sont restaurés par progression.The log records of modifications not flushed to disk before the system stopped are rolled forward.
  • Toutes les modifications associées à des transactions incomplètes (telles que les transactions pour lesquelles il n'existe pas d'enregistrement de journal COMMIT ou ROLLBACK) sont restaurées.All modifications associated with incomplete transactions, such as transactions for which there is no COMMIT or ROLLBACK log record, are rolled back.

Opération de point de contrôleCheckpoint Operation

Un point de contrôle effectue les processus suivants dans la base de données :A checkpoint performs the following processes in the database:

  • Écrit un enregistrement dans le fichier journal qui marque le début du point de contrôle.Writes a record to the log file, marking the start of the checkpoint.

  • Stocke les informations enregistrées pour le point de contrôle dans une chaîne d'enregistrements de journal des points de contrôle.Stores information recorded for the checkpoint in a chain of checkpoint log records.

    L'une des informations consignées dans le point de contrôle est le numéro séquentiel dans le journal du premier enregistrement de journal qui doit être présent pour permettre une restauration à l'échelle de la base de données.One piece of information recorded in the checkpoint is the log sequence number (LSN) of the first log record that must be present for a successful database-wide rollback. Ce NSE porte le nom de NSE de récupération minimum (NSEmin).This LSN is called the Minimum Recovery LSN (MinLSN). Le NSEmin est le minimum de :The MinLSN is the minimum of the:

    • NSE du début du point de contrôle ;LSN of the start of the checkpoint.
    • NSE du début de la transaction active la plus ancienne ;LSN of the start of the oldest active transaction.
    • NSE du début de la transaction de réplication la plus ancienne qui n'a pas encore été transmise à la base de données de distribution.LSN of the start of the oldest replication transaction that has not yet been delivered to the distribution database.

    Les enregistrements de point de contrôle contiennent également une liste de toutes les transactions actives qui ont modifié la base de données.The checkpoint records also contain a list of all the active transactions that have modified the database.

  • Si la base de données utilise le mode de récupération simple, signalez pour une utilisation ultérieure l'espace qui précède le NSEmin.If the database uses the simple recovery model, marks for reuse the space that precedes the MinLSN.

  • Écrit sur le disque toutes les pages de journal et de données incorrectes.Writes all dirty log and data pages to disk.

  • Écrit un enregistrement marquant la fin du point de contrôle dans le fichier journal.Writes a record marking the end of the checkpoint to the log file.

  • Écrit le numéro LSN du début de cette chaîne dans la page de démarrage de la base de données.Writes the LSN of the start of this chain to the database boot page.

Activités entraînant un point de contrôleActivities that cause a Checkpoint

Des points de contrôle interviennent dans les situations suivantes :Checkpoints occur in the following situations:

  • Une instruction CHECKPOINT est exécutée explicitement.A CHECKPOINT statement is explicitly executed. Un point de contrôle intervient dans la base de données active pour la connexion.A checkpoint occurs in the current database for the connection.
  • Une opération journalisée minimale est effectuée dans la base de données ; par exemple, une opération de copie en bloc est réalisée sur une base de données qui se sert du mode de récupération utilisant les journaux de transactions.A minimally logged operation is performed in the database; for example, a bulk-copy operation is performed on a database that is using the Bulk-Logged recovery model.
  • Des fichiers de base de données ont été ajoutés ou supprimés à l'aide de l'instruction ALTER DATABASE.Database files have been added or removed by using ALTER DATABASE.
  • Une instance de SQL Server est arrêtée par une instruction SHUTDOWN ou via l’arrêt du service SQL Server (MSSQLSERVER).An instance of SQL Server is stopped by a SHUTDOWN statement or by stopping the SQL Server (MSSQLSERVER) service. Ces opérations provoquent la création d’un point de contrôle dans chaque base de données dans l’instance de SQL Server.Either action causes a checkpoint in each database in the instance of SQL Server.
  • Une instance de SQL Server génère régulièrement des points de contrôle automatiques dans chaque base de données, afin de réduire la durée nécessaire à l’instance pour récupérer la base de données.An instance of SQL Server periodically generates automatic checkpoints in each database to reduce the time that the instance would take to recover the database.
  • Une sauvegarde de la base de données est effectuée.A database backup is taken.
  • Une activité nécessitant l'arrêt de la base de données est effectuée.An activity requiring a database shutdown is performed. Par exemple, la valeur ON est attribuée à AUTO_CLOSE et la dernière connexion utilisateur à la base de données est fermée, ou une modification d'une option de base de données nécessitant un redémarrage de la base de données est effectuée.For example, AUTO_CLOSE is ON and the last user connection to the database is closed, or a database option change is made that requires a restart of the database.

Points de contrôle automatiquesAutomatic Checkpoints

Le moteur de base de données SQL Server génère des points de contrôle automatiques.The SQL Server Database Engine generates automatic checkpoints. L'intervalle entre les points de contrôle automatiques est basé sur la quantité d'espace de journal utilisée et la durée écoulée depuis le dernier point de contrôle.The interval between automatic checkpoints is based on the amount of log space used and the time elapsed since the last checkpoint. Cet intervalle de temps entre les points de contrôle automatiques peut varier fortement et être long si les modifications apportées à la base de données sont peu nombreuses.The time interval between automatic checkpoints can be highly variable and long, if few modifications are made in the database. Inversement, les points de contrôle automatiques peuvent être fréquents si les données modifiées sont nombreuses.Automatic checkpoints can also occur frequently if lots of data is modified.

Utilisez l’option de configuration de serveur intervalle de récupération pour calculer l’intervalle pour toutes les bases de données sur une instance de serveur.Use the recovery interval server configuration option to calculate the interval between automatic checkpoints for all the databases on a server instance. Cette option spécifie la durée maximale que le moteur de base de données doit utiliser pour récupérer une base de données durant un redémarrage du système.This option specifies the maximum time the Database Engine should use to recover a database during a system restart. Le moteur de base de données estime le nombre d’enregistrements de journal qu’il peut traiter au cours de l’ intervalle de récupération durant une opération de récupération.The Database Engine estimates how many log records it can process in the recovery interval during a recovery operation.

L'intervalle entre les points de contrôle automatiques dépend également du mode de récupération :The interval between automatic checkpoints also depends on the recovery model:

  • Si la base de données utilise le mode de restauration complète ou le mode de récupération utilisant les journaux de transactions, un point de contrôle automatique est généré chaque fois que le nombre d’enregistrements du journal atteint une valeur que le moteur de base de données estime pouvoir traiter pendant la durée spécifiée dans l’option intervalle de récupération.If the database is using either the full or bulk-logged recovery model, an automatic checkpoint is generated whenever the number of log records reaches the number the Database Engine estimates it can process during the time specified in the recovery interval option.

  • Si la base de données utilise le mode de récupération simple, un point de contrôle automatique est généré chaque fois que le nombre des enregistrements de journal atteint la plus faible de ces deux valeurs :If the database is using the simple recovery model, an automatic checkpoint is generated whenever the number of log records reaches the lesser of these two values:

    • Le journal est saturé à 70 %.The log becomes 70 percent full.
    • Le nombre d’enregistrements de journal atteint le nombre que le moteur de base de données estime pouvoir traiter au cours de la durée spécifiée dans l’option intervalle de récupération.The number of log records reaches the number the Database Engine estimates it can process during the time specified in the recovery interval option.

Pour plus d’informations sur la configuration de l’intervalle de récupération, consultez Configurer l’option de configuration du serveur recovery interval.For information about setting the recovery interval, see Configure the recovery interval Server Configuration Option.

Conseil

L’option d’installation avancée -k de SQL Server permet à un administrateur de base de données de limiter le comportement d’E/S de point de contrôle en fonction du débit du sous-système d’E/S pour certains types de points de contrôle.The -k SQL Server advanced setup option enables a database administrator to throttle checkpoint I/O behavior based on the throughput of the I/O subsystem for some types of checkpoints. L’option d’installation -k s’applique aux points de contrôle automatiques, ainsi qu’à tous les points de contrôle non accélérés.The -k setup option applies to automatic checkpoints and any otherwise unthrottled checkpoints.

Les points de contrôle automatiques tronquent la section inutilisée du journal des transactions si la base de données utilise le mode de récupération simple.Automatic checkpoints truncate the unused section of the transaction log if the database is using the simple recovery model. Cependant, ils ne tronquent pas le journal si la base de données utilise le mode de récupération complète ou le mode de récupération utilisant les journaux de transactions.However, if the database is using the full or bulk-logged recovery models, the log is not truncated by automatic checkpoints. Pour plus d’informations, consultez Journal des transactions.For more information, see The Transaction Log.

L’instruction CHECKPOINT fournit désormais un argument checkpoint_duration facultatif qui spécifie la durée demandée, en secondes, permettant aux points de contrôle de terminer leurs tâches.The CHECKPOINT statement now provides an optional checkpoint_duration argument that specifies the requested period of time, in seconds, for checkpoints to finish. Pour plus d’informations, consultez CHECKPOINT.For more information, see CHECKPOINT.

journal actifActive Log

La section du fichier journal comprise entre le MinLSN et le dernier enregistrement de journal écrit s’appelle la partie active du journal, ou journal actif.The section of the log file from the MinLSN to the last-written log record is called the active portion of the log, or the active log. Cette section est indispensable pour procéder à une récupération complète de la base de données.This is the section of the log required to do a full recovery of the database. Aucune partie de ce journal actif ne peut être tronquée.No part of the active log can ever be truncated. Tous les enregistrements de journal doivent être tronqués à partir des parties du journal situées avant le MinLSN.All log records must be truncated from the parts of the log before the MinLSN.

L'illustration ci-dessous présente une version simplifiée de la fin d'un journal de transactions comportant deux transactions actives.The following illustration shows a simplified version of the end-of-a-transaction log with two active transactions. Les enregistrements du point de contrôle ont été compactés en un enregistrement unique.Checkpoint records have been compacted to a single record.

active_log

LSN 148 est le dernier enregistrement du journal des transactions.LSN 148 is the last record in the transaction log. Au moment où le point de contrôle enregistré au numéro LSN 147 était traité, Tran 1 avait été validée et Tran 2 était la seule transaction active.At the time that the recorded checkpoint at LSN 147 was processed, Tran 1 had been committed and Tran 2 was the only active transaction. Ainsi, le premier enregistrement de Tran 2 est l'enregistrement de journal le plus ancien pour une transaction active au moment du dernier point de contrôle.That makes the first log record for Tran 2 the oldest log record for a transaction active at the time of the last checkpoint. Par ailleurs, le numéro LSN 142 est l'enregistrement du début de la transaction pour Tran 2, la valeur MinLSN.This makes LSN 142, the Begin transaction record for Tran 2, the MinLSN.

Transactions de longue duréeLong-Running Transactions

Le journal actif doit contenir chaque partie de toutes les transactions non validées.The active log must include every part of all uncommitted transactions. Une application qui démarre une transaction et qui ne la valide pas ou ne la restaure pas empêche le moteur de base de données de faire progresser le MinLSN.An application that starts a transaction and does not commit it or roll it back prevents the Database Engine from advancing the MinLSN. Ceci peut provoquer deux types de problèmes :This can cause two types of problems:

  • Si le système est arrêté après que la transaction a effectué de nombreuses modifications non validées, la phase de récupération lors du démarrage ultérieur peut être beaucoup plus longue que la durée spécifiée dans l’option intervalle de récupération .If the system is shut down after the transaction has performed many uncommitted modifications, the recovery phase of the subsequent restart can take much longer than the time specified in the recovery interval option.
  • Le journal peut devenir très volumineux parce qu'il ne peut pas être tronqué au-delà du MinLSN.The log might grow very large, because the log cannot be truncated past the MinLSN. Cela se produit même si la base de données utilise le modèle de récupération simple, dans lequel le journal des transactions est généralement tronqué sur chaque point de contrôle automatique.This occurs even if the database is using the simple recovery model, in which the transaction log is generally truncated on each automatic checkpoint.

Transactions de réplicationReplication Transactions

L'Agent de lecture du journal surveille le journal des transactions de chaque base de données configurée pour la réplication transactionnelle et copie les transactions devant être répliquées à partir du journal des transactions dans la base de données de distribution.The Log Reader Agent monitors the transaction log of each database configured for transactional replication, and it copies the transactions marked for replication from the transaction log into the distribution database. Le journal actif doit contenir toutes les transactions qui sont marquées pour la réplication mais qui n'ont pas encore été transmises à la base de données de distribution.The active log must contain all transactions that are marked for replication, but that have not yet been delivered to the distribution database. Si ces transactions ne sont pas répliquées à temps, elles peuvent empêcher la troncature du journal.If these transactions are not replicated in a timely manner, they can prevent the truncation of the log. Pour plus d’informations, consultez Réplication transactionnelle.For more information, see Transactional Replication.

Voir aussiSee also

Pour plus d’informations sur le journal des transactions et les bonnes pratiques relatives à la gestion des journaux, nous vous recommandons de lire les articles et les ouvrages suivants.We recommend the following articles and books for additional information about the transaction log and log management best practices.

Journal des transactions (SQL Server) The Transaction Log (SQL Server)
Gérer la taille du fichier journal des transactions Manage the size of the transaction log file
Sauvegardes des journaux de transactions (SQL Server) Transaction Log Backups (SQL Server)
Points de contrôle de base de données (SQL Server) Database Checkpoints (SQL Server)
Configurer l’option de configuration de serveur d’intervalle de récupération Configure the recovery interval Server Configuration Option
sys.dm_db_log_info (Transact-SQL) sys.dm_db_log_info (Transact-SQL)
sys.dm_db_log_space_usage (Transact-SQL) sys.dm_db_log_space_usage (Transact-SQL)
Fonctionnement de la journalisation et de la récupération dans SQL Server, par Paul Randall Understanding Logging and Recovery in SQL Server by Paul Randal
Gestion du journal des transactions SQL Server de Tony Davis et Gail ShawSQL Server Transaction Log Management by Tony Davis and Gail Shaw