Le journal des transactions augmente de manière inattendue ou est saturé sur un ordinateur SQL Server

Traductions disponibles Traductions disponibles
Numéro d'article: 317375 - Voir les produits auxquels s'applique cet article
Agrandir tout | Réduire tout

Sommaire

Résumé

Dans SQL Server 7.0, SQL Server 2000 et SQL Server 2005, avec le paramètre d'étendue automatique, les fichiers du journal des transactions peuvent augmenter automatiquement.

Généralement, la taille du fichier du journal des transactions se stabilise lorsque celui-ci peut contenir le nombre maximal de transactions susceptibles de se produire entre les troncations du journal que les points de contrôle ou les sauvegardes du journal des transactions déclenchent.

Cependant, dans certaines situations, le journal des transactions peut devenir très volumineux et manquer d'espace ou être saturé. Généralement, le message d'erreur suivant s'affiche lorsqu'un fichier du journal des transactions occupe l'espace disque disponible et ne peut plus augmenter :
Erreur : 9002, Gravité : 17, État : 2
Le fichier journal de la base de données '%.*ls' est plein.
Si vous utilisez SQL Server 2005, un message d'erreur semblable au suivant s'affiche :
Erreur : 9002, Gravité : 17, État : 2
Le journal des transactions de la base de données '%.*ls' est plein. Pour savoir pourquoi il est impossible de réutiliser de l'espace dans le journal, consultez la colonne log_reuse_wait_desc dans sys.databases
Outre ce message d'erreur, SQL Server peut marquer des bases de données comme suspectes en raison d'un manque d'espace pour l'augmentation du journal des transactions. Pour plus d'informations sur la façon de récupérer dans cette situation, reportez-vous à la rubrique « Insufficient Disk Space » (Espace disque insuffisant) dans la documentation SQL Server en ligne.

De plus, l'augmentation du journal des transactions peut provoquer les situations suivantes :
  • Le fichier du journal des transactions est très volumineux.
  • Les transactions peuvent échouer et commencer à s'annuler.
  • La réalisation des transactions peut être très longue.
  • Des problèmes de performances peuvent survenir.
  • Un blocage peut se produire.

Causes

L'augmentation du journal des transactions peut se produire pour les raisons ou dans les scénarios suivants : Remarque Dans SQL Server 2005, vous pouvez consulter les colonne log_reuse_wait et log_reuse_wait_desc de la vue de catalogue sys.databases afin de déterminer les éléments suivants :
  • Raisons pour lesquelles l'espace du journal des transactions n'est pas réutilisé
  • Raisons pour lesquelles le journal des transactions ne peut pas être tronqué

Transactions non validées

Les transactions explicites restent non validées si vous n'émettez pas une commande COMMIT ou ROLLBACK explicite. Cela se produit le plus souvent lorsqu'une application émet une commande CANCEL ou Transact SQL KILL sans commande ROLLBACK correspondante. L'annulation de la transaction se produit, mais elle ne s'annule pas ; par conséquent, SQL Server ne peut pas tronquer chaque transaction ultérieure car la transaction interrompue est encore ouverte. Vous pouvez utiliser la référence Transact-SQL DBCC OPENTRAN pour vérifier l'existence d'une transaction active dans une base de données à un moment donné. Pour plus d'informations sur ce scénario particulier, cliquez sur le numéro ci-dessous pour afficher l'article correspondant dans la Base de connaissances Microsoft.
295108 Une transaction incomplète peut contenir un grand nombre de verrous et provoquer un blocage
171224 Compréhension du fonctionnement de la commande Transact-SQL KILL
En outre, reportez-vous à la rubrique « DBCC OPENTRAN » dans la documentation en ligne de SQL Server.

Scénarios qui peuvent provoquer des transactions non validées :
  • Une conception d'application qui suppose que toutes les erreurs provoquent des annulations.
  • Une conception d'application qui ne prend pas entièrement en compte le comportement de SQL Server lorsqu'il revient aux transactions nommées ou aux transactions nommées spécialement imbriquées. Si vous essayez de revenir à une transaction nommée interne, le message d'erreur suivant s'affiche :
    Serveur : Msg 6401, Niveau 16, État 1, Ligne 13 Impossible d'annuler (ROLLBACK) InnerTran. Ni transaction ni point d'enregistrement trouvés.
    Une fois que SQL Server a généré ce message d'erreur, il continue jusqu'à l'instruction suivante. Ce comportement est voulu par la conception même du produit. Pour plus d'informations, reportez-vous à la rubrique « Nested Transactions » (Transactions imbriquées) ou « Inside SQL Server » (À l'intérieur de SQL Server) de la documentation en ligne sur SQL Server.

    Microsoft vous conseille d'agir comme suit lors de la conception de votre application :
    • Ouvrez une unité de transaction (envisagez la possibilité qu'une autre procédure puisse appeler la vôtre).
    • Vérifiez @@TRANCOUNT avant d'émettre une commande ou une instruction COMMIT, ROLLBACK, RETURN ou similaire.
    • Écrivez votre code en prévoyant qu'un @@TRANCOUNT externe puisse venir s'imbriquer dans les vôtres et être annulés lorsqu'une erreur se produit.
    • Examinez les options du point d'enregistrement et de marquage pour les transactions. (Elles ne libèrent pas les verrous !)
    • Procédez à un test complet.
  • Une application qui permet une interaction utilisateur à l'intérieur des transactions. La transaction reste ouverte pendant longtemps, ce qui provoque le blocage et l'augmentation du journal des transactions car la transaction ouverte ne peut pas être tronquée et de nouvelles transactions sont ajoutées au journal après la transaction ouverte.
  • Une application qui ne vérifie pas si des transactions sont ouvertes dans @@TRANCOUNT.
  • Des erreurs réseau ou autre qui ferment la connexion de l'application cliente sur SQL Server sans l'en informer.
  • Groupement de connexions. Une fois les threads de travail créés, SQL Server les réutilise s'ils ne traitent pas une connexion. Si une connexion utilisateur démarre une transaction et se déconnecte avant de la valider ou de l'annuler, et qu'une connexion ultérieure réutilise le même thread, la transaction précédente reste ouverte. Les verrous de la transaction précédente restent alors ouverts, ce qui empêche la troncation des transactions validées dans le journal, avec pour effet des fichiers journaux volumineux. Pour plus d'informations sur le groupement de connexions, cliquez sur le numéro ci-dessous pour afficher l'article correspondant dans la Base de connaissances Microsoft.
    164221 Comment faire pour activer le groupement de connexions dans une application ODBC

Transactions très volumineuses

Les enregistrements dans les fichiers du journal des transactions peuvent être tronqués selon la transaction. Si la transaction a une portée étendue, cette transaction, ainsi que toutes les transactions ultérieures, n'est supprimée du journal des transactions que si elle est terminée. Une telle situation peut provoquer des fichiers journaux volumineux. Si la transaction est suffisamment volumineuse, le fichier journal peut utiliser tout l'espace disque disponible et provoquer l'affichage d'un message d'erreur de type « journal des transactions saturé », tel que l'Erreur 9002. Pour plus d'informations sur ce que vous devez faire lorsque vous recevez ce type de message d'erreur, reportez-vous à la rubrique « Plus d'informations » de cet article. En outre, l'annulation de transactions volumineuses demande beaucoup de temps et utilise les ressources du serveur SQL.

Opérations : DBCC DBREINDEX et CREATE INDEX

En raison des modifications apportées au modèle de récupération dans SQL Server 2000, lorsque vous utilisez le mode de restauration complète et que vous exécutez DBCC DBREINDEX, le journal des transactions peut augmenter considérablement par rapport à SQL Server 7.0 dans un mode de récupération équivalent avec SELECT INTO ou BULK COPY quand l'option « Trunc. Log on chkpt. » est désactivée.

Bien que la taille du journal des transactions après l'opération DBREINDEX puisse être un problème, cette approche procure de meilleures performances de restauration du journal.

Lors de la restauration à partir des sauvegardes du journal des transactions

Cette opération est décrite dans l'article suivant de la Base de connaissances Microsoft :
232196 L'espace du journal semble augmenter après une restauration à partir de la sauvegarde

Si vous définissez SQL Server 2000 pour utiliser le mode de journalisation en bloc et que vous émettez une instruction BULK COPY ou SELECT INTO, chaque étendue modifiée est marquée puis sauvegardée lors de la sauvegarde du journal des transactions. Bien que cela vous permette de sauvegarder les journaux des transactions et de récupérer après des pannes même après des opérations en bloc, cela s'ajoute à la taille des journaux des transactions. SQL Server 7.0 n'inclut pas cette fonctionnalité. SQL Server 7.0 enregistre uniquement les étendues qui sont modifiées, mais il n'enregistre pas les étendues réelles. Par conséquent, la journalisation prend considérablement plus de place dans SQL Server 2000 que dans SQL Server 7.0 en mode de journalisation en bloc qu'en mode complet.

Les applications clientes ne traitent pas tous les résultats

Si vous émettez une requête vers SQL Server et que vous ne traitez pas le résultat immédiatement, il se peut que vous mainteniez des verrous et que vous réduisiez la concurrence sur votre serveur.

Supposons, par exemple, que vous émettiez une requête qui exige des lignes de deux pages pour remplir votre jeu de résultats. SQL Server analyse, compile et exécute la requête. Cela signifie que les verrous partagés sont placés sur les deux pages qui contiennent les lignes que vous devez avoir pour satisfaire à votre requête. Supposons également que toutes les lignes ne tiennent pas sur un paquet TDS SQL Server (la méthode par laquelle le serveur communique avec le client). Les paquets TDS sont remplis et envoyés au client. Si toutes les lignes de la première page tiennent sur le paquet TDS, SQL Server libère le verrou partagé sur cette page mais laisse un verrou partagé sur la deuxième page. SQL Server attend ensuite que le client demande plus de données (vous pouvez le faire à l'aide de DBNEXTROW/DBRESULTS, SQLNextRow/SQLResults ou FetchLast/FetchFirst, par exemple).

Cela signifie que le verrou partagé est maintenu jusqu'à ce que le client demande le reste des données. D'autres procédures qui demandent des données de la deuxième page peuvent donc être bloquées.

Expiration des requêtes avant la fin de l'augmentation d'un journal des transactions et affichage de faux messages d'erreurs « Journal saturé »

Dans cette situation, bien que l'espace disque soit suffisant, vous pouvez continuer de recevoir un message d'erreur « espace insuffisant ».

Cette situation varie pour SQL Server 7.0 et SQL Server 2000.

Une requête peut provoquer l'augmentation automatique du journal des transactions si celui-ci est presque saturé. Un délai supplémentaire peut être nécessaire, ce qui peut provoquer l'arrêt d'une requête ou le dépassement de sa période d'expiration. SQL Server 7.0 renvoie une Erreur 9002 dans cette situation. Ce problème ne s'applique pas à SQL Server 2000.

Dans SQL Server 2000, si l'option Réduire automatiquement est activée pour une base de données, il y a un tout petit laps de temps pendant lequel un journal des transactions essaie d'augmenter automatiquement sans y parvenir car la fonction Réduire automatiquement s'exécute simultanément. Cela peut également provoquer de fausses instances de l'Erreur 9002.

Généralement, l'augmentation automatique des fichiers du journal des transactions survient rapidement. Cependant, dans les situations suivantes, cela peut prendre plus de temps que d'habitude :
  • Les incréments de croissance sont trop petits.
  • Le serveur est lent pour diverses raisons.
  • Les lecteurs de disques ne sont pas assez rapides.

Transactions non répliquées

La taille du journal des transactions de la base de données de l'éditeur peut augmenter si vous utilisez la réplication. Les transactions qui affectent les objets qui sont répliqués sont marqués « Pour réplication ». Ces transactions, telles que les transactions non validées, ne sont pas supprimées après un point de vérification ou après la sauvegarde du journal des transactions tant que la tâche de lecture du journal n'a pas copié les transactions dans la base de données de distribution et annulé le marquage. Si un problème avec la tâche de lecture du journal l'empêche de lire ces transactions dans la base de données de l'éditeur, la taille du journal des transactions continue d'augmenter parallèlement au nombre de transactions non répliquées. Vous pouvez utiliser la référence Transact-SQL DBCC OPENTRAN pour identifier la plus ancienne transaction non répliquée.

Pour plus d'informations sur la résolution des problèmes liés aux transactions non répliquées, reportez-vous aux rubriques « sp_replcounters » et « sp_repldone » dans la documentation en ligne de SQL Server.

Pour plus d'informations, cliquez sur le numéro ci-dessous pour afficher l'article correspondant dans la Base de connaissances Microsoft.
306769 CORRECTIF : Impossible de tronquer le journal des transactions d'une base de données avec publication de capture instantanée
240039 CORRECTIF : DBCC OPENTRAN ne rapporte pas d'informations de réplication
198514 CORRECTIF : Les transactions restent dans le journal en cas de restauration sur le nouveau serveur

Plus d'informations

Le journal des transactions de toute base de données est géré comme un ensemble de fichiers journaux virtuels dont SQL Server détermine la taille en interne d'après la taille totale du fichier journal et l'incrément de croissance utilisé lorsque le journal augmente. Un journal augmente toujours en unités de fichiers journaux virtuels complètes et ne peut être compressé que vers la limite du fichier journal virtuel. Un fichier journal virtuel peut exister dans l'un des trois états suivants : ACTIF, RÉCUPÉRABLE ET RÉUTILISABLE.
  • ACTIF : la partie active du journal débute au numéro de séquence d'enregistrement minimal qui représente une transaction active (non validée) et se termine au dernier numéro écrit. Chaque fichier journal virtuel qui contient une partie du journal actif est considéré comme un fichier journal virtuel actif. (L'espace non utilisé dans le journal physique ne fait partie d'aucun fichier journal virtuel.)
  • RÉCUPÉRABLE : la partie du journal qui précède la transaction active la plus ancienne est nécessaire uniquement pour maintenir l'ordre des sauvegardes du journal en vue d'une récupération.
  • RÉUTILISABLE: si vous ne conservez pas les sauvegardes du journal des transactions ou si vous avez déjà procédé à la sauvegarde du journal, SQL Server réutilise les fichiers journaux virtuels qui précèdent la plus ancienne transaction active.
Lorsque SQL Server atteint la fin du fichier journal physique, il commence à réutiliser cet espace dans le fichier physique en émettant une opération CIRCLE BACK au début des fichiers. En effet, SQL Server recycle l'espace dans le fichier journal qui n'est plus nécessaire pour procéder à une récupération ou à une sauvegarde. S'il convient de conserver une séquence de sauvegarde, la partie du journal précédant le numéro de séquence d'enregistrement minimal ne peut pas être remplacée tant que vous n'avez pas procédé à la sauvegarde ou à la troncation de ces enregistrements de journal. Une fois la sauvegarde du journal effectuée, SQL Server peut recommencer à écrire au début du fichier. Une fois les premières données du ficher journal écrasées, la partie réutilisable du journal se situe alors entre la fin du journal logique et la partie active du journal.

Pour plus d'informations, reportez-vous à la rubrique « Transaction Log Physical Architecture » (Architecture physique du journal des transactions) de la documentation en ligne sur SQL Server. Un excellent diagramme ainsi qu'une discussion sur ce sujet sont également disponibles à la page 190 de « Inside SQL Server 7.0 » (Soukup, Ron. Inside Microsoft SQL Server 7.0, Microsoft Press, 1999), ainsi qu'à la page 182 à 186 de « Inside SQL Server 2000 » (Delaney, Kalen. Inside Microsoft SQL Server 2000, Microsoft Press, 2000). Les bases de données SQL Server 7.0 et SQL Server 2000 disposent des options d'augmentation et de réduction automatique. Vous pouvez utiliser ces options pour compresser ou augmenter la taille de votre journal des transactions.

Pour plus d'informations sur la façon dont ces options peuvent affecter votre serveur, cliquez sur le numéro ci-dessous pour afficher l'article correspondant dans la Base de connaissances Microsoft.
315512 Éléments à prendre en compte pour configurer l'étendue et la réduction automatiques dans SQL Server
Il existe une différence entre la troncation et la compression du fichier du journal des transactions. Lorsque SQL Server tronque un fichier du journal des transactions, cela signifie que le contenu de ce fichier (par exemple, les transactions validées) est supprimé. Cependant, lorsque vous regardez la taille du fichier en vous basant sur l'espace disque (par exemple, dans l'Explorateur Windows ou en utilisant la commande dir), celle-ci reste inchangée. Toutefois, l'espace à l'intérieur du fichier .ldf peut désormais être réutilisé par de nouvelles transactions. Ce n'est que lorsque SQL Server réduit la taille du fichier du journal des transactions que vous voyez effectivement un changement dans la taille physique du fichier journal.

Pour plus d'informations sur la façon de réduire des journaux des transactions, cliquez sur le numéro ci-dessous pour afficher l'article correspondant dans la Base de connaissances Microsoft.
256650 Comment faire pour réduire le journal des transactions de SQL Server 7.0
272318 Réduction du journal de transactions dans SQL Server 2000 avec DBCC SHRINKFILE
Pour plus d'informations sur l'utilisation du journal des transactions de SQL Server 6.5, cliquez sur le numéro ci-dessous pour afficher l'article correspondant dans la Base de connaissances Microsoft.
110139 Causes du remplissage d'un journal des transactions SQL

Propriétés

Numéro d'article: 317375 - Dernière mise à jour: vendredi 12 juillet 2013 - Version: 7.1
Les informations contenues dans cet article s'appliquent au(x) produit(s) suivant(s):
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2005 Express Edition
  • Microsoft SQL Server 2005 Workgroup Edition
  • Microsoft SQL Server 2000 Standard
  • Microsoft SQL Server 7.0 Standard
Mots-clés : 
kbsqlserverengine kbinfo KB317375
L'INFORMATION CONTENUE DANS CE DOCUMENT EST FOURNIE PAR MICROSOFT SANS GARANTIE D'AUCUNE SORTE, EXPLICITE OU IMPLICITE. L'UTILISATEUR ASSUME LE RISQUE DE L'UTILISATION DU CONTENU DE CE DOCUMENT. CE DOCUMENT NE PEUT ETRE REVENDU OU CEDE EN ECHANGE D'UN QUELCONQUE PROFIT.

Envoyer des commentaires

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com