Partager via

Le journal des transactions

S'applique à :SQL Server

Chaque base de données SQL Server comprend un journal des transactions qui enregistre toutes les transactions et les modifications apportées par chaque transaction.

Le journal des transactions est un composant essentiel de la base de données. En cas de défaillance du système, vous avez besoin de ce journal pour ramener votre base de données à un état cohérent.

Avertissement

Le journal des transactions ne doit être ni supprimé ni déplacé, sauf si vous comprenez pleinement les conséquences d’une telle opération.

Pour plus d’informations sur l’architecture physique et logique du journal des transactions, consultez le guide de gestion et d’architecture du journal des transactions SQL Server.

Conseil

Les points de contrôle créent des points de contrôle connus à partir desquels commencer l’application des journaux de transactions lors de la récupération de la base de données. Pour plus d’informations, consultez Points de contrôle de base de données (SQL Server).

Opérations prises en charge par le journal des transactions

Le journal des transactions prend en charge les opérations suivantes :

  • Récupération des transactions individuelles.
  • Récupération de toutes les transactions incomplètes au démarrage de SQL Server.
  • Restauration par progression d'une base de données, d'un fichier, d'un groupe de fichiers ou d'une page jusqu'au point de défaillance
  • Prise en charge de la réplication transactionnelle
  • Prise en charge des solutions de haute disponibilité et de reprise après sinistre : groupes de disponibilité Always On, mise en miroir des bases de données et expédition des journaux.

Récupération des transactions individuelles

Si une application envoie une instruction ROLLBACK, ou si le moteur de base de données détecte une erreur telle que la perte de la communication avec un client, les enregistrements du journal permettent de restaurer les modifications effectuées par une transaction incomplète.

Récupération de toutes les transactions incomplètes au démarrage de SQL Server

En cas de défaillance d’un serveur, il peut arriver que certaines modifications effectuées dans les bases de données n’aient jamais pu être écrites de la mémoire tampon vers les fichiers de données ou proviennent de transactions incomplètes dans les fichiers de données. Quand elle démarre, une instance de SQL Server exécute une récupération de chaque base de données. Chaque modification enregistrée dans le journal qui risque de ne pas avoir été répercutée dans les fichiers de données est récupérée. Chaque transaction incomplète trouvée dans le journal des transactions est ensuite restaurée afin de préserver l'intégrité de la base de données. Pour plus d’informations, consultez Vue d’ensemble de la restauration et de la récupération (SQL Server).

Restauration par progression d’une base de données, d’un fichier, d’un groupe de fichiers ou d’une page jusqu’au point de défaillance

Après un incident matériel ou une défaillance de disque touchant les fichiers de base de données, vous pouvez restaurer la base de données jusqu'au point de défaillance. Restaurez tout d'abord la dernière sauvegarde complète et différentielle de la base de données, puis restaurez la séquence suivante des sauvegardes des journaux des transactions jusqu'au point de défaillance.

Quand vous restaurez chaque sauvegarde de journal, le moteur de base de données réapplique toutes les modifications enregistrées dans le journal pour restaurer par progression toutes les transactions. Quand la dernière sauvegarde de fichier journal a été restaurée, le moteur de base de données utilise les informations des journaux pour restaurer toutes les transactions qui n'ont pas été terminées à ce stade. Pour plus d’informations, consultez Vue d’ensemble de la restauration et de la récupération (SQL Server).

Prise en charge de la réplication transactionnelle

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. Pour plus d’informations, consultez Fonctionnement de la réplication transactionnelle.

Prise en charge des solutions de haute disponibilité et récupération d’urgence

Les solutions de serveur de secours, les groupes de disponibilité AlwaysOn, les mises en miroir de base de données et les copies des journaux de transactions dépendent fortement du journal des transactions.

Dans un scénario de groupe de disponibilité Always On, chaque mise à jour d'une base de données sur le réplica principal est immédiatement reproduite dans des copies distinctes de la base de données sur tous les réplicas secondaires. Le réplica principal envoie immédiatement chaque enregistrement de journal aux réplicas secondaires, qui appliquent les enregistrements de journal entrants aux bases de données de disponibilité, avec une restauration par progression continue du journal. Pour plus d’informations, consultez Instances de cluster de basculement Always On (SQL Server).

Dans un scénario de copie des journaux de transactions, le serveur principal envoie les sauvegardes du journal des transactions de la base de données primaire vers une ou plusieurs destinations. Chaque serveur secondaire restaure les sauvegardes du journal dans sa base de données secondaire locale. Pour plus d’informations, consultez À propos de la copie des journaux de transaction (SQL Server).

Dans un scénario de mise en miroir de base de données, toutes les mises à jour d’une base de données, à savoir la base de données principale, sont immédiatement reproduites dans une copie distincte complète de la base de données, la base de données miroir. L'instance du serveur principal envoie chaque enregistrement du journal immédiatement à l'instance du serveur miroir, qui applique ces enregistrements à la base de données miroir en la restaurant constamment par progression. Pour plus d’informations, consultez La mise en miroir de bases de données (SQL Server).

Caractéristiques du journal des transactions

Caractéristiques du journal des transactions du moteur de base de données SQL Server :

  • Le journal des transactions est mis en œuvre sous la forme d'un fichier ou d'un ensemble de fichiers distinct dans la base de données. Le cache de journal est géré séparément du cache de mémoire tampon pour les pages de données. Cette séparation entraîne un code simple, rapide et robuste dans le moteur de base de données SQL Server. Pour plus d’informations, consultez l’architecture physique du journal des transactions.

  • Le format des enregistrements et des pages du journal ne suit pas obligatoirement celui des pages de données.

  • Le journal des transactions peut être implémenté dans plusieurs fichiers. Vous pouvez configurer les fichiers à développer automatiquement en définissant la FILEGROWTH valeur du journal. Cette configuration réduit le risque d’épuisement de l’espace dans le journal des transactions, en même temps que la surcharge administrative. Pour plus d’informations, consultez les options de fichier et de groupe de fichiers ALTER DATABASE (Transact-SQL).

  • Le mécanisme de réutilisation de l'espace des fichiers journaux est rapide et a une incidence minimale sur le débit des transactions.

Pour plus d’informations sur l’architecture physique et logique du journal des transactions, consultez le guide de gestion et d’architecture du journal des transactions SQL Server.

Troncation du journal des transactions

La troncation du journal libère de l'espace dans le fichier journal pour que le journal des transactions puisse le réutiliser. Vous devez régulièrement tronquer le journal des transactions pour l’empêcher de remplir l’espace imparti. Plusieurs facteurs peuvent retarder la troncation du journal. Il est donc important de surveiller sa taille. Certaines opérations peuvent être journalisées minimalement pour réduire leur effet sur la taille du journal des transactions.

La troncation du journal supprime les fichiers journaux virtuels inactifs (VLF) du journal des transactions logiques d’une base de données SQL Server, libérant de l’espace dans le journal logique pour une réutilisation par le journal des transactions physiques. Si un journal des transactions n’est jamais tronqué, il finit par occuper tout l’espace disque alloué aux fichiers journaux physiques.

Pour éviter de manquer d’espace, à moins que la troncation du journal soit retardée pour une raison quelconque, la troncation se produit automatiquement après les événements suivants :

  • En mode de récupération simple, après un point de contrôle.

  • En mode de récupération complète ou en mode de récupération utilisant les journaux de transactions, si un point de contrôle s'est produit depuis la sauvegarde précédente, la troncation se produit après une sauvegarde de fichier journal (sauf s'il s'agit d'une sauvegarde de copie uniquement).

  • Lorsque vous créez d’abord une base de données qui utilise le modèle de récupération complète, le journal des transactions est réutilisé en fonction des besoins (comme une base de données à l’aide du modèle de récupération simple), jusqu’à ce que vous créiez une sauvegarde complète de base de données.

Pour plus d’informations, consultez Facteurs qui peuvent retarder la troncation du journal plus loin dans cet article.

La troncation du journal ne réduit pas la taille du fichier journal physique. Pour réduire la taille physique d'un fichier journal physique, vous devez réduire le fichier journal. Pour plus d'informations sur la réduction de la taille du fichier journal physique, consultez Gérer la taille du fichier journal de transactions. Toutefois, 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 et, en procédant ainsi, introduisez une surcharge de performances pendant les opérations de croissance du journal.

Facteurs pouvant retarder la troncation du journal

Lorsque les enregistrements de journal restent actifs pendant une longue période, la troncation du journal des transactions est retardée et le journal des transactions peut se remplir, comme décrit précédemment dans cet article.

Important

Pour plus d’informations sur la façon de répondre à un journal des transactions complet, consultez Résoudre les problèmes d’un journal des transactions complet (erreur SQL Server 9002).

La troncation du journal peut être retardée pour différentes raisons. Pour savoir ce qui empêche votre troncation de journal, interrogez les colonnes log_reuse_wait et log_reuse_wait_desc de la vue catalogue sys. databases. Le tableau suivant décrit les valeurs possibles de ces colonnes.

Valeur log_reuse_wait Valeur log_reuse_wait_desc Description
0 NOTHING Il existe actuellement un ou plusieurs fichiers journaux virtuels réutilisables (VLF).
1 CHECKPOINT Aucun point de contrôle n’a eu lieu depuis la dernière troncation du journal, ou la tête du journal n’a pas encore été déplacée au-delà d’un fichier journal virtuel (VLF). (Tous les modèles de récupération.)

Ce scénario est une raison courante de retarder la troncation du journal. Pour plus d’informations, consultez Points de contrôle de base de données (SQL Server).
2 LOG_BACKUP Une sauvegarde du journal est requise avant que le journal des transactions puisse être tronqué. (Modèles de récupération complètes ou journalisées en bloc uniquement.)

Lorsque la sauvegarde de journal suivante est terminée, l'espace du journal peut devenir réutilisable.
3 ACTIVE_BACKUP_OR_RESTORE Une sauvegarde de données ou une restauration est en cours. (Tous les modèles de récupération.)

Si une sauvegarde des données empêche la troncation du journal, l'annulation de l'opération de sauvegarde peut résoudre le problème immédiat.
4 ACTIVE_TRANSACTION Une transaction est active (tous les modèles de récupération) :

Une transaction longue peut exister au démarrage de la sauvegarde du fichier journal. Dans ce cas, libérer l'espace peut requérir une autre sauvegarde du fichier journal. Les transactions longues empêchent la troncation du journal dans tous les modes de récupération, notamment le mode de récupération simple, où le journal des transactions est généralement tronqué sur chaque point de contrôle automatique.

Une transaction est différée. Une transaction différée est en fait une transaction active dont la restauration est bloquée à cause d'une ressource indisponible. Pour plus d’informations sur les causes des transactions différées et sur la façon de les déplacer hors de l’état différé, consultez transactions différées (SQL Server).

Les transactions à long terme peuvent également remplir le journal des transactions de tempdb. tempdb est implicitement utilisé par les transactions utilisateur pour les objets internes tels que les tables de travail pour le tri, les fichiers de travail pour le hachage, les tables de travail de curseur et la gestion de version de ligne. Même si la transaction utilisateur inclut uniquement les données de lecture (requêtes SELECT), des objets internes peuvent être créés et utilisés dans des transactions utilisateur. Ensuite, le journal des transactions tempdb peut être rempli.
5 DATABASE_MIRRORING La mise en miroir de bases de données est interrompue ou, en mode haute performance, la base de données miroir se trouve derrière la base de données principale de manière significative. (Mode de récupération complète uniquement.)

Pour plus d’informations, consultez La mise en miroir de bases de données (SQL Server).
6 REPLICATION Durant les réplications transactionnelles, les transactions liées aux publications ne sont pas encore remises à la base de données de distribution. (Mode de récupération complète uniquement.)

Pour plus d’informations sur la réplication transactionnelle, consultez la réplication SQL Server.
7 DATABASE_SNAPSHOT_CREATION Un instantané de base de données est en cours de création. (Tous les modèles de récupération.)

Il s'agit d'une raison courante et habituellement brève du retard de la troncation du journal.
8 LOG_SCAN Une analyse de journal se produit. (Tous les modèles de récupération.)

Il s'agit d'une raison courante et habituellement brève du retard de la troncation du journal.
9 AVAILABILITY_REPLICA Un réplica secondaire d'un groupe de disponibilité applique les enregistrements du journal des transactions de cette base de données à une base de données secondaire associée. (Mode de récupération complète uniquement.)

Pour plus d’informations, consultez Qu’est-ce qu’un groupe de disponibilité AlwaysOn ?.
10 - Utilisation interne uniquement.
11 - Utilisation interne uniquement.
12 - Utilisation interne uniquement.
13 OLDEST_PAGE Si une base de données est configurée pour utiliser des points de contrôle indirects, la page la plus ancienne dans la base de données peut être plus ancienne que le numéro séquentiel dans le journal (LSN) du point de contrôle. Dans ce cas, la page la plus ancienne peut retarder la troncation du journal. (Tous les modèles de récupération.)

Pour plus d’informations sur les points de contrôle indirects, consultez Points de contrôle de base de données (SQL Server).
14 OTHER_TRANSIENT Cette valeur n'est pas utilisée actuellement.
16 XTP_CHECKPOINT Un point de contrôle OLTP en mémoire doit être effectué. Pour les tables à mémoire optimisée, un point de contrôle automatique est pris lorsque le fichier journal des transactions devient supérieur à 1,5 Go depuis le dernier point de contrôle. (Inclut les tables à mémoire optimisée et basées sur disque.)

Pour plus d’informations, consultez l’opération de point de contrôle pour les tables optimisées en mémoireet le processus de journalisation et de point de contrôle pour les tables mémoire optimisées.

Opérations pouvant faire l’objet d’une journalisation minimale

La journalisation minimale implique de journaliser uniquement les informations requises pour récupérer la transaction sans prendre en charge la récupération dans le temps. Cet article identifie les opérations qui sont enregistrées minimalement sous le modèle de récupération journalisé en bloc (et également sous le modèle de récupération simple, sauf lorsqu’une sauvegarde est en cours d’exécution).

La journalisation minimale n'est pas prise en charge pour les tables à mémoire optimisée.

En mode de récupération complète, toutes les opérations en bloc sont entièrement journalisées. Cependant, vous pouvez minimiser la journalisation d'un ensemble d'opérations en bloc en faisant temporairement passer la base de données en mode de récupération utilisant les journaux de transactions pour les opérations en bloc. La journalisation minimale est plus efficace que la journalisation complète et réduit la possibilité pour une opération en bloc de grande envergure d'occuper l'espace disponible du journal des transactions pendant une transaction en bloc. En revanche, si la base de données est endommagée ou perdue lors de la journalisation minimale, vous ne pouvez pas récupérer la base de données jusqu'au point de défaillance.

Les opérations suivantes, qui sont entièrement journalisées en mode de récupération complète, font l'objet d'une journalisation minimale en modes simple et de récupération utilisant les journaux de transactions :

  • Opérations d’importation en bloc (bcp, BULK INSERT et INSERT). Pour plus d'informations sur les conditions dans lesquelles la journalisation d'une importation en bloc dans une table est minimale, consultez Prérequis pour une journalisation minimale dans l’importation en bloc.

    Lorsque la réplication transactionnelle est activée, BULK INSERT les opérations sont entièrement journalisées même sous le modèle de récupération journalisé en bloc.

  • Opérations SELECT - INTO clause.

    Lorsque la réplication transactionnelle est activée, SELECT INTO les opérations sont entièrement journalisées même sous le modèle de récupération journalisé en bloc.

  • Mises à jour partielles vers des types de données de valeur élevée, en incluant la clause .WRITE dans l’instruction UPDATE pendant l’insertion ou l’ajout de nouvelles données. La journalisation minimale n'est pas utilisée lors de la mise à jour de valeurs existantes. Pour plus d’informations sur les types de données de valeur élevée, consultez Types de données.

  • InstructionsWRITETEXT et UPDATETEXT lors de l'insertion ou de l'ajout de nouvelles données dans les colonnes de type données text, ntext, et image . La journalisation minimale n'est pas utilisée lors de la mise à jour de valeurs existantes.

    Avertissement

    Les WRITETEXT instructions et UPDATETEXT les instructions sont déconseillées. Évitez de les utiliser dans de nouvelles applications.

  • Si la base de données est définie sur le modèle de récupération simple ou journalisée en bloc, certaines opérations DDL d’index sont journalisées minimalement, que l’opération soit exécutée hors connexion ou en ligne. Les opérations d’index journalisées minimalement sont les suivantes :

    • OpérationsCREATE INDEX (vues indexées comprises).

    • Opération ALTER INDEX REBUILD ou DBCC DBREINDEX.

      Les opérations de génération d’index utilisent une journalisation minimale, mais peuvent être retardées lorsqu’il existe une sauvegarde en cours d’exécution simultanée. Ce délai est dû aux exigences de synchronisation des pages de pool de mémoires tampons journalisées minimalement lorsque vous utilisez le modèle de récupération simple ou journalisé en bloc.

      Avertissement

      L’instruction DBCC DBREINDEX est déconseillée. Évitez de l’utiliser dans les nouvelles applications.

    • Reconstruction d’un nouveau segment de mémoire DROP INDEX (le cas échéant). La désallocation de page d’index pendant une DROP INDEX opération est toujours entièrement journalisée.

Tâche Article
Gérer le journal des transactions Gérer la taille du fichier journal des transactions

Résoudre les problèmes liés à un journal des transactions complet (erreur SQL Server 9002)
Sauvegarder le journal des transactions (mode de récupération complète uniquement) Sauvegarder un journal des transactions

Sauvegarder le journal des transactions lorsque la base de données est endommagée (SQL Server)
Restaurer le journal des transactions (mode de récupération complète uniquement) Restaurer une sauvegarde du journal des transactions (SQL Server)

Contenu connexe

  • Last updated on