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 aurez besoin de ce journal pour rétablir votre base de données à un état cohérent.

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.

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 récupération d’urgence et de haute disponibilité

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 en savoir plus, reportez-vous à Instances de cluster de basculement Always-on.

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.

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.

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 modèle de récupération COMPLÈTE, le journal des transactions sera réutilisé en fonction des besoins (comme une base de données 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.

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.

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.

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).

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 INSERTet INSERT... SELECT). Pour plus d'informations sur les conditions dans lesquelles la journalisation d'une importation en bloc dans une table est minimale, consultez Prerequisites for Minimal Logging in Bulk Import.

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.

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 (Transact-SQL).

• 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érationsALTER INDEX REBUILD ou DBCC DBREINDEX.

    Avertissement

    L’instruction DBCC DBREINDEX est dépréciée : ne l’utilisez pas dans de nouvelles applications.

    Remarque

    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.

  • 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âches associées

Gestion du journal des transactions

• Gérer la taille du fichier journal des transactions

• Résoudre les problèmes liés à un journal des transactions saturé (erreur SQL Server 9002)

Sauvegarde du journal des transactions (mode de récupération complète)

• Sauvegarder un journal des transactions (SQL Server)

• Sauvegarder le journal des transactions quand la base de données est endommagée (SQL Server)

Restauration du journal des transactions (mode de récupération complète)

• Restaurer une sauvegarde de journal des transactions (SQL Server)

Voir aussi

• Guide d’architecture et gestion du journal des transactions SQL Server
• Contrôler la durabilité d'une transaction
• Conditions requises pour une journalisation minimale dans l’importation en bloc
• Sauvegarder et restaurer des bases de données SQL Server
• Vue d'ensemble de la restauration et de la récupération (SQL Server)
• Points de contrôle de base de données (SQL Server)
• Afficher ou modifier les propriétés d’une base de données
• Modes de récupération (SQL Server)
• Sauvegardes du journal des transactions (SQL Server)
• sys.dm_db_log_info (Transact-SQL)
• sys.dm_db_log_space_usage (Transact-SQL)