Journal des transactions (SQL Server)
Chaque base de données SQL Server possède un journal des transactions qui enregistre toutes les transactions et les modifications apportées par chacune d'entre elles. Le journal des transactions doit être vidé régulièrement pour éviter qu'il ne soit saturé. Toutefois, certains facteurs peuvent retarder la troncation du journal. Par conséquent, il est important de surveiller la taille du journal. Certaines opérations peuvent faire l'objet d'une journalisation minimale afin de réduire leur impact sur la taille des journaux de transactions.
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. Le journal des transactions ne doit jamais être supprimé ou déplacé, à moins d'en avoir pleinement compris les conséquences.
[!REMARQUE]
Les points de référence connus à partir desquels vous pouvez commencer à appliquer les journaux des transactions pendant la récupération de base de données sont créés par les points de contrôle. Pour plus d'informations, consultez Points de contrôle de base de données (SQL Server).
Dans cette rubrique :
Avantages : opérations prises en charge par le journal des transactions
Troncation du journal des transactions
Facteurs pouvant retarder la troncation du journal
Opérations pouvant faire l'objet d'une journalisation minimale
Tâches associées
Avantages : 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 de la haute disponibilité et de solutions de récupération d'urgence : Groupes de disponibilité AlwaysOn, mise en miroir de bases de données et copie des journaux de transaction.
.gif "Icône de flèche utilisée avec le lien Retour en haut")
[Haut de la page]
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. La troncation du journal est essentielle pour empêcher que le journal se remplisse. 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'était jamais tronqué, il finirait par occuper tout l'espace disque alloué à ses fichiers journaux physiques.
Pour éviter ce problème, à moins que la troncation du journal ne 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 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 qu'il s'agit d'une sauvegarde de fichier journal en copie seule).
Pour plus d'informations, consultez Facteurs pouvant retarder la troncation du journal, plus loin dans cette rubrique.
[!REMARQUE]
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 des transactions.
[Haut de la page]
Facteurs pouvant retarder la troncation du journal
Lorsque les enregistrements de journal restent actifs longtemps, la troncation du journal des transactions est différée, et le journal des transactions peut éventuellement se remplir complètement.
Important
Pour plus d'informations sur la marche à suivre en cas de saturation du journal des transactions, consultez Résoudre les problèmes liés à un journal des transactions saturé (erreur SQL Server 9002).
La troncation du journal peut être différée pour différents motifs. Vous pouvez découvrir les raisons éventuelles qui empêchent 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 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. |
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. (Tous les modes de récupération) Il s'agit d'une raison classique 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 restauration 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).
|
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 pertinentes aux publications sont encore non 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 Réplication SQL Server. |
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 classique et habituellement brève du retard de la troncation du journal. |
8 |
LOG_SCAN |
Une analyse du journal se produit. (Tous les modes de récupération) Il s'agit d'une raison classique et habituellement brève du retard de la troncation du journal. |
9 |
AVAILABILITY_REPLICA |
Un réplica secondaire d'un groupe de disponibilité applique des enregistrements des journaux de transactions de cette base de données à une base de données secondaire correspondante. (Mode de récupération complète) Pour plus d'informations, consultez Vue d'ensemble des groupes de disponibilité AlwaysOn (SQL Server). |
10 |
— |
À usage interne uniquement |
11 |
— |
À usage interne uniquement |
12 |
— |
À usage 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 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. |
[Haut de la page]
Opérations pouvant faire l'objet d'une journalisation minimale
La journalisation minimale implique de ne journaliser que les informations requises pour pouvoir récupérer la transaction sans prendre en charge la récupération jusqu'à une date et heure. Cette rubrique 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 lorsqu'une sauvegarde est en cours).
[!REMARQUE]
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 restauration complète, sont journalisées au minimum en modes simple et de récupération utilisant les journaux de transactions :
Opérations d'importation en bloc (bcp, BULK INSERT et INSERT... SELECT). Pour plus d'informations sur les conditions dans lesquelles la journalisation d'une importation en bloc dans une table est minimale, consultez Conditions requises pour une journalisation minimale dans l'importation en bloc.
[!REMARQUE]
Lorsque 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.
[!REMARQUE]
Lorsque 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 à l'aide de la clause .WRITE dans l'instruction UPDATE lors de l'insertion ou de l'ajout de nouvelles données. Notez que la journalisation minimale n'est pas utilisée quand des valeurs existantes sont mises à jour. Pour plus d'informations sur les types de données de valeur élevée, consultez Types de données (Transact-SQL).
Instructions WRITETEXT 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. Notez que la journalisation minimale n'est pas utilisée quand des valeurs existantes sont mises à jour.
[!REMARQUE]
L'emploi des instructions WRITETEXT et UPDATETEXT est déconseillé, c'est pourquoi vous devez éviter 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érations CREATE INDEX (y compris les vues indexées).
Opérations ALTER INDEX REBUILD ou DBCC DBREINDEX.
[!REMARQUE]
L'emploi de l'instruction DBCC DBREINDEX est déconseillé, c'est pourquoi vous devez l'éviter dans les nouvelles applications.
Reconstruction d'un nouveau segment de mémoire DROP INDEX (le cas échéant).
[!REMARQUE]
La désallocation de pages d'index au cours d'une opération DROP INDEX est toujours entièrement journalisée.
[Haut de la page]
Tâches associées
Managing the transaction log
Sauvegarde du journal des transactions (mode de récupération complète)
Restauration du journal des transactions (mode de récupération complète)
[Haut de la page]
Voir aussi
Concepts
Conditions requises pour une journalisation minimale dans l'importation en bloc
Sauvegarde et restauration des bases de données SQL Server
Points de contrôle de base de données (SQL Server)