Partager via

Le journal des transactions

  • Article

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 rétablir 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 du journal des transactions et les structures internes, consultez Guide d’architecture et gestion du journal des transactions SQL Server.

Conseil

Différents points de contrôle créent des points de référence connus et fiables à partir desquels vous pouvez commencer à appliquer les journaux des transactions lors de la récupération d'une 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 sur la réplication transactionnelle, 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 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 du journal est géré indépendamment du cache des mémoires tampons des pages de données, ce qui se traduit par un code simple, rapide et robuste dans le moteur de base de données SQL Server. Pour plus d’informations, consultez 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. Les fichiers peuvent être configurés pour croître automatiquement en définissant la valeur FILEGROWTH pour le journal. Le risque d'insuffisance de l'espace dans le journal des transactions et la surcharge administrative sont ainsi réduits. Pour plus d’informations, consultez Options de fichiers et de groupes 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 du journal des transactions et les structures internes, consultez Guide d’architecture et gestion 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 faire l'objet d'une journalisation minimale afin de réduire leur impact sur la taille des journaux de transactions.

La troncation du journal supprime les fichiers journaux virtuels inactifs du journal des transactions logique d’une base de données SQL 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. 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 à l’aide du mode 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 d’un mode de récupération simple), jusqu’à ce que vous créez une sauvegarde complète de base de données.

Pour en savoir plus, reportez-vous à Facteurs pouvant retarder la troncation du journal, dans la suite de 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 pouvant retarder la troncation du journal. Si l’espace de stockage est à nouveau nécessaire après une réduction de journal, le journal des transactions croît de nouveau, introduisant une surcharge au niveau des performances pendant les opérations d’accroissement du journal.

Facteurs pouvant retarder la troncation du journal

Lorsque les enregistrements de journal restent actifs longtemps, la troncation du journal des transactions est retardée et le journal des transactions peut se remplir entièrement, comme nous l'avons déjà mentionné dans cet article.

Important

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

En réalité, la troncation du journal peut être différée pour plusieurs raisons. Le cas échéant, découvrez ce qui empêche de tronquer le journal en interrogeant les colonnes log_reuse_wait et log_reuse_wait_desc de l’affichage 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 un ou plusieurs fichiers journaux virtuels réutilisables.
1 CHECKPOINT Aucun point de contrôle n'est apparu depuis la dernière troncation du journal ou le début du journal n'est pas encore allé au-delà d'un fichier journal virtuel (VLF) (tous les modes de récupération).

Il s'agit d'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é. (Mode de récupération complète ou mode de récupération utilisant les journaux de transactions 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 modes 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 modes 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 la manière de les faire sortir 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 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 SQL Server Replication.
7 DATABASE_SNAPSHOT_CREATION Un instantané de base de données est créé (tous les modes 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 modes 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 modes 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 effectué quand le fichier journal de transactions dépasse 1,5 Go par rapport au dernier point de contrôle (inclut les tables sur disque et les tables à mémoire optimisée).

Pour en savoir plus, reportez-vous à Opération de point de contrôle pour les tables à mémoire optimisée et [Processus de journalisation et de point de contrôle pour les tables à mémoire optimisée] (https://blogs.msdn.microsoft.com/sqlcat/2016/05/20/logging-and-checkpoint-process-for-memory-optimized-tables-2/)

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

Lajournalisation minimale implique de ne journaliser que les informations obligatoires pour pouvoir récupérer la transaction sans prendre en charge la récupération jusqu’à une date et heure. Cet article identifie les opérations qui sont journalisées au minimum en mode de récupération utilisant les journaux de transactions (ainsi qu'en mode de récupération simple, sauf quand une sauvegarde est en cours).

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.

    Quand la réplication transactionnelle est activée, les opérations BULK INSERT sont entièrement journalisées, même dans le mode de récupération utilisant les journaux de transactions.

  • Opérations SELECT - INTO clause.

    Quand la réplication transactionnelle est activée, les opérations SELECT INTO sont entièrement journalisées, même dans le mode de récupération utilisant les journaux de transactions.

  • 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

    L’emploi des instructions WRITETEXT et UPDATETEXT est déprécié : évitez de les utiliser dans les nouvelles applications.

  • Si la base de données est en mode simple ou de récupération utilisant les journaux de transactions, certaines opérations DDL avec index sont journalisées au minimum qu'elles soient exécutées hors connexion ou en ligne. Les opérations journalisées minimales impliquant les index sont les suivantes :

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

    • Opération ALTER INDEX REBUILD ou DBCC DBREINDEX.

      Les opérations de création d’index utilisent la journalisation minimale, mais peuvent être retardées lorsqu’une sauvegarde est exécutée simultanément. Ce délai est dû aux exigences de synchronisation des pages du pool de mémoires tampons journalisées minimales lors de l’utilisation du mode de récupération simple ou utilisant les journaux de transactions.

      Avertissement

      L’instruction DBCC DBREINDEX est déconseillé : é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 pages d’index pendant une opération DROP INDEX est toujours entièrement journalisée.

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

- Détecter un problème lié à un journal des transactions saturé (erreur de serveur SQL 9002)
Sauvegarder le journal des transactions (mode de récupération complète uniquement) - Sauvegarder un journal des transactions

- Sauvegarder le journal des transactions quand 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 de journal des transactions (SQL Server)

Contenu connexe