The Transaction Log (SQL Server) (Français)

• 10/23/2019
• 11 minutes to read
• • M
  • D
  • f
  • M
  • s
  • +8

Applies to: SQL Server (all supported versions)

Every SQL Server database has a transaction log that records all transactions and the database modifications made by each 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 ramener votre base de données à un état cohérent.

pour plus d’informations sur l’architecture et les composants internes du journal des transactions, reportez-vous au Guide D’Architecture et de gestion du journal des transactions SQL Server.

les Opérations prises en charge par le journal des transactions

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

• Chaque opération de récupération.
• récupération de toutes les transactions incomplètes au démarrage de SQL Server.
• transférer une base de données, un fichier, un groupe de fichiers ou une page restaurés 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é en permanence, mise en miroir des bases de données et expédition des journaux.,

récupération de transaction individuelle

Si une application émet une instructionROLLBACK, ou si le moteur de base de données détecte une erreur telle que la perte de communication avec un client, les enregistrements de journal sont utilisés pour annuler les modifications apportées par une transaction incomplète.,

récupération de toutes les transactions incomplètes lors du démarrage de SQL Server

Si un serveur tombe en panne, les bases de données peuvent être laissées dans un état où certaines modifications n’ont jamais été écrites à partir du cache tampon vers les fichiers de données, et il peut y avoir des modifications à partir de transactions Lorsqu’une instance de SQL Server est démarrée, elle exécute une récupération de chaque base de données. Chaque modification enregistrée dans le journal qui n’ont pas été écrites dans les fichiers de données est récupérée., Chaque transaction incomplète trouvée dans le journal des transactions est ensuite annulée pour s’assurer que l’intégrité de la base de données est préservée. Pour plus d’informations, reportez-vous à la section Présentation de la restauration et de la récupération (SQL Server).

transférer une base de données, un fichier, un groupe de fichiers ou une page restaurés au point de défaillance

Après une perte matérielle ou une défaillance du disque affectant les fichiers de base de données, vous pouvez restaurer la base de données au point de défaillance., Vous d’abord restaurer la dernière sauvegarde complète et la dernière différentielle sauvegarde de base de données, puis de restaurer la séquence suivante des sauvegardes du journal des transactions au point de défaillance.

lorsque vous restaurez chaque sauvegarde de journal, le moteur de base de données réapplique toutes les modifications enregistrées dans le journal pour faire avancer toutes les transactions. Lorsque la dernière sauvegarde du journal est restaurée, le moteur de base de données utilise ensuite les informations du journal pour annuler toutes les transactions qui n’étaient pas terminées à ce stade. Pour plus d’informations, reportez-vous à la section Présentation de la restauration et de la récupération (SQL Server).,

prise en charge de la réplication transactionnelle

L’Agent Log Reader surveille le journal des transactions de chaque base de données configurée pour la réplication transactionnelle et copie les transactions marquées pour la réplication à partir du journal des transactions dans la base de données de distribution. Pour plus d’informations, consultez Comment fonctionne la réplication transactionnelle.

prise en charge des solutions de haute disponibilité et de reprise après sinistre

Les solutions de serveur de secours, toujours sur les groupes de disponibilité, la mise en miroir des bases de données et l’expédition des journaux, s’appuient fortement sur le journal des transactions.,

dans un scénario Always On availability groups, chaque mise à jour d’une base de données, la réplique principale, est immédiatement reproduite dans des copies séparées et complètes de la base de données, les répliques secondaires. La réplique principale envoie immédiatement chaque enregistrement de journal aux réplicas secondaires, qui appliquent les enregistrements de journal entrants aux bases de données de groupes de disponibilité, en les faisant avancer continuellement. Pour plus d’informations, consultez toujours sur les Instances de Cluster de basculement

Dans un scénario d’expédition de journaux, le serveur principal envoie le journal des transactions actif de la base de données principale vers une ou plusieurs destinations., Chaque serveur secondaire restaure le journal dans sa base de données secondaire locale. Pour plus d’informations, voir à propos de L’expédition des journaux.

dans un scénario de mise en miroir de base de données, chaque mise à jour d’une base de données, la base de données principale, est immédiatement reproduite dans une copie séparée et complète de la base de données, la base de données miroir. L’instance du serveur principal envoie immédiatement chaque enregistrement de journal à l’instance du serveur miroir, qui applique les enregistrements de journal entrants à la base de données miroir, en le faisant avancer continuellement. Pour plus d’informations, consultez Mise en miroir de base 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 implémenté en tant que fichier séparé ou ensemble de fichiers dans la base de données. Le cache des journaux est géré séparément du cache tampon pour les pages de données, ce qui permet d’obtenir 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 de journaux et les pages n’est pas contraint de suivre le format des pages de données.,

• Le journal des transactions peut être mis en œuvre dans plusieurs fichiers. Les fichiers peuvent être définis pour se développer automatiquement en définissant la valeur FILEGROWTH pour le journal. Cela réduit le risque de manque d’espace dans le journal des transactions, tout en réduisant les frais administratifs. Pour plus d’informations, reportez-vous à la section ALTER DATABASE (Transact-SQL) File and Filegroup Options.

• Le mécanisme de réutilisation de l’espace dans les fichiers journaux est rapide et a un effet minime sur le débit des transactions.,

pour plus d’informations sur l’architecture et les composants internes du journal des transactions, reportez-vous au Guide D’Architecture et de gestion du journal des transactions SQL Server.

la troncature de journal

la troncature du Journal libère de l’espace dans le fichier journal des fins de réutilisation par le journal des transactions. Vous devez régulièrement tronquer votre journal des transactions pour l’empêcher de remplir l’espace alloué. Plusieurs facteurs peuvent retarder la troncature du journal, donc la surveillance de la taille du journal est importante. Certaines opérations peuvent être enregistrées de manière minimale pour réduire leur impact sur la taille du journal des transactions.,

la troncature 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 finira par remplir tout l’espace disque alloué aux fichiers journaux physiques.

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

• sous le modèle de récupération simple, après un point de contrôle.,
• sous le modèle de récupération complète ou le modèle de récupération consignée en bloc, si un point de contrôle s’est produit depuis la sauvegarde précédente, la troncature se produit après une sauvegarde de journal (sauf s’il s’agit d’une sauvegarde de journal uniquement en copie).

pour plus d’informations, voir facteurs pouvant retarder la troncature du journal, plus loin dans cette rubrique.

facteurs pouvant retarder la troncature du journal

lorsque les enregistrements du journal restent actifs pendant une longue période, la troncature du journal des transactions est retardée et le journal des transactions peut se remplir, comme nous l’avons mentionné plus tôt dans cette longue rubrique.,

En réalité, la troncature du journal peut être retardée pour diverses raisons. Découvrez ce qui, le cas échéant, empêche votre troncature de journal en interrogeant les colonnes log_reuse_wait et log_reuse_wait_desc du système.vue du catalogue des bases de données. Le tableau suivant décrit les valeurs de ces colonnes.,

log_reuse_wait valeur	log_reuse_wait_desc valeur	Description
0	RIEN	Actuellement, il y a un ou plusieurs réutilisables fichiers journaux virtuels).
1	point de contrôle	aucun point de contrôle ne s’est produit depuis la dernière troncature du journal, ou l’en-tête du journal n’a pas encore dépassé un fichier journal virtuel (VLF). (Tous les modèles de récupération) c’est une raison de routine pour retarder la troncature du journal., Pour plus d’informations, consultez points de contrôle de base de données (SQL Server).
2	LOG_BACKUP	Une sauvegarde de journal est requis avant que le journal des transactions peut être tronqué. (Modèles de récupération complets ou en vrac uniquement) lorsque la prochaine sauvegarde du journal est terminée, un certain espace de journal peut devenir réutilisable.
3	ACTIVE_BACKUP_OR_RESTORE	données de sauvegarde ou une restauration est en cours (tous les modèles de récupération). Si une sauvegarde de données empêche la troncature du journal, l’annulation de l’opération de sauvegarde peut aider le problème immédiat.,
4	ACTIVE_TRANSACTION	Une transaction est active (tous les modèles de récupération): Une transaction de longue durée peut exister au début de la sauvegarde du journal. Dans ce cas, la libération de l’espace peut nécessiter une autre sauvegarde du journal. Notez que les transactions de longue durée empêchent la troncature du journal sous tous les modèles de récupération, y compris le modèle de récupération simple, sous lequel 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 en raison 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 de longue durée peuvent également remplir le journal des transactions de tempdb. Tempdb est utilisé implicitement 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 des versions de ligne., Même si la transaction utilisateur n’inclut que des données de lecture (requêtesSELECT), des objets internes peuvent être créés et utilisés sous les transactions utilisateur. Ensuite, le journal des transactions tempdb peut être rempli.
5	DATABASE_MIRRORING	la mise en miroir de la base de données est en pause, ou en mode haute performance, la base de données miroir est significativement en retard sur la base de données principale. (Modèle de récupération complète uniquement) Pour plus d’informations, voir Mise en miroir de base de données (SQL Server).,
6	réplication	pendant les réplications transactionnelles, les transactions relatives aux publications ne sont toujours pas transmises à la base de données de distribution. (Modèle de récupération complète uniquement) Pour plus d’informations sur la réplication transactionnelle, voir 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 routine, et généralement brève, cause de troncature retardée log.
8	LOG_SCAN	Une analyse de journal est en cours., (Tous les modèles de récupération) Il s’agit d’une routine, et généralement brève, cause de troncature retardée log.
9	AVAILABILITY_REPLICA	une réplique 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 correspondante. (Modèle de récupération complète) Pour plus d’informations, voir Vue D’ensemble de toujours sur les groupes de disponibilité (SQL Server).,
10	–	Pour usage interne uniquement
11	–	Pour usage interne uniquement
12	–	Pour usage interne seulement
13	OLDEST_PAGE	Si une base de données est configuré pour utiliser les points de contrôle indirects, page la plus ancienne sur la base de données peut-être plus que le point de contrôle numéro de séquence de journal (LSN). Dans ce cas, la page la plus ancienne peut retarder la troncature du journal. (Tous les modèles de récupération) Pour plus d’informations sur les points de contrôle indirects, voir 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 exécuté.Pour les tables optimisées en mémoire, un point de contrôle automatique est pris lorsque le fichier journal des transactions devient plus grand que 1.,5 Go depuis le dernier point de contrôle (comprend à la fois des tables basées sur disque et optimisées en mémoire) Pour plus d’informations, voir fonctionnement du point de contrôle pour les Tables optimisées en mémoire et (https://blogs.msdn.microsoft.com/sqlcat/2016/05/20/logging-and-checkpoint-process-for-memory-optimized-tables-2/)

opérations>

la journalisation minimale implique la journalisation uniquement des informations requises pour récupérer la transaction sans prendre en charge la récupération à un moment donné., Cette rubrique identifie les opérations qui sont enregistrées de manière minimale sous le modèle de récupération en bloc (ainsi que sous le modèle de récupération simple, sauf lorsqu’une sauvegarde est en cours d’exécution).

Les opérations suivantes, qui sont entièrement consignées dans le modèle de récupération complète, sont consignées de manière minimale dans le modèle de récupération simple et consignée en bloc:

• opérations d’importation en bloc (bcp, BULK INSERT et INSERT… SÉLECTIONNER)., Pour plus d’informations sur la journalisation minimale de l’importation en bloc dans une table, consultez conditions préalables 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 enregistrées, même sous le modèle de récupération journalisée en bloc.

• sélectionnez dans les opérations.

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

• mises à jour partielles des types de données à grande valeur, en utilisant 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 lorsque les valeurs existantes sont mises à jour. Pour plus d’informations sur les types de données à grande valeur, 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 de données text, ntext et image. Notez que la journalisation minimale n’est pas utilisée lorsque les valeurs existantes sont mises à jour.,

  Avertissement

  Le WRITETEXT et UPDATETEXT états sont obsolètes; é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 en vrac, certaines opérations DDL d’index sont enregistrées de manière minimale, que l’opération soit exécutée hors ligne ou en ligne. Les opérations d’index minimalement journalisées sont les suivantes:

  • créer des opérations D’INDEX (y compris les vues indexées).

  • ALTER INDEX REBUILD ou DBCC DBREINDEX operations.,

    Avertissement

    Le DBCC DBREINDEX est obsolète; Ne pas utiliser dans de nouvelles applications.

    Remarque

    Les opérations de génération D’Index utilisent une journalisation minimale mais peuvent être retardées lorsqu’une sauvegarde est exécutée simultanément. Ce retard est causé par les exigences de synchronisation des pages de pool de tampons enregistrées de manière minimale lors de l’utilisation du modèle de récupération simple ou consignée en bloc.

  • DROP INDEX nouvelle reconstruction de tas (le cas échéant). La désallocation de la page d’Index pendant une opérationDROP INDEX est toujours entièrement journalisée.,n La base de données est endommagée (SQL Server)

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

  • restauration D’une sauvegarde du journal des transactions (SQL Server)

  Voir aussi

  guide D’Architecture et de gestion du journal des transactions SQL Server
  Contrôle De La Durabilité des transactions
  Conditions préalables pour une connexion minimale points de contrôle (SQL Server)
  afficher ou modifier les propriétés d’une base de données
  modèles 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)