KB2455009 - CORRECTIF : Performances lentes lors de la récupération d’une base de données s’il existe de nombreux fichiers VLF dans le journal des transactions en SQL Server 2005, en SQL Server 2008 ou dans SQL Server 2008 R2

Symptômes

Prenons l’exemple du scénario suivant :

Vous installez Microsoft SQL Server 2005, Microsoft SQL Server 2008 ou Microsoft SQL Server 2008 R2 sur un ordinateur.
Une ou plusieurs bases de données sont dans le modèle de récupération complète ou journalisée en bloc.
La taille initiale d’un fichier journal des transactions pour la base de données est petite. Toutefois, le fichier transactionnel prend de l’ampleur.
L’incrément de croissance automatique du fichier journal des transactions est configuré pour une petite taille. Par exemple, l’incrément de croissance de croissance automatique est configuré sur un mégaoctet ou sur un pour cent. Vous pouvez également augmenter le fichier journal des transactions à l’aide d’une petite croissance manuelle.
Le fichier journal des transactions augmente par le petit incrément de croissance. Après un certain temps, le fichier journal des transactions est volumineux.

Par exemple, le fichier journal des transactions passe de cent mégaoctets à dix gigaoctets. Le fichier journal des transactions augmente d’un incrément de mégaoctets dans chaque croissance.

Dans ce scénario, vous pouvez rencontrer les problèmes suivants :

La récupération de la base de données est lente lorsque la base de données est démarrée s’il existe un grand volume de travail à récupérer.

Par exemple, une transaction est en attente d’ouverture pendant une longue période. Ou bien, un grand nombre de transactions de petite taille ne sont pas validées lorsque le SQL Server est arrêté ou redémarré. Dans ce cas, vous pouvez recevoir une erreur pendant une longue estimation du temps de récupération dans la phase d’analyse, dans la phase d’annulation ou dans la phase de rétablissement. L’erreur ressemble à ce qui suit et est consignée dans le fichier journal des erreurs SQL Server 2005 :

L’analyse de la base de données « mydatabase » (7) est terminée à 0 % (environ 1 234 secondes restent). Il s’agit d’un message d’information uniquement. Aucune action de l’utilisateur n’est requise.
La récupération de la base de données est lente lorsqu’un fichier de sauvegarde de base de données complet et une séquence de journal des transactions supplémentaire sont restaurés si la base de données est mise en ligne.

Remarque Pour mettre la base de données en ligne pendant l’opération de restauration, utilisez la syntaxe WITH RECOVERY ou WITH STANDBY = <options> syntaxe.
La récupération de la mise en miroir de bases de données est lente sur un serveur partenaire dans la paire de mise en miroir.
La progression de l’agent de lecture du journal pour la réplication transactionnelle est lente pour les opérations suivantes :
- Le journal des transactions d’une base de données d’éditeur est analysé.
- Une commande est ajoutée à une base de données de distribution.
- Les transactions sont répliquées.
Des performances lentes se produisent lorsqu’un instantané de base de données est créé si de nombreuses transactions sont journalisées ou si les journaux des transactions restent actifs pendant une longue période. Ce problème se produit parce que l’instantané doit exécuter la récupération et que les journaux des transactions doivent être restaurées.
Des performances lentes se produisent lorsque vous utilisez la syntaxe DBCC CHECKDB pour créer un instantané de base de données masqué qui doit récupérer entièrement l’instantané de base de données pour exécuter les vérifications de cohérence.

Cause

Ces problèmes se produisent parce que les fichiers journaux virtuels (VLF) dans un fichier journal des transactions peuvent entraîner des performances lentes lorsque SQL Server analyse ces fichiers VLF pendant les opérations suivantes :

Récupération de base de données
Mise en miroir de bases de données
Capture instantanée de base de données
Activités du lecteur du journal de réplication transactionnelle de base de données

Lorsqu’un fichier journal des transactions pour une base de données est développé par de petits incréments dans des conditions de croissance extrême, des milliers à des centaines de milliers de fichiers VLF sont présents dans un ensemble unique de fichiers de journal des transactions (.ldf). Toutefois, la croissance des journaux de grande taille par incréments importants crée uniquement plusieurs fichiers VLF par comparaison. Par exemple, plusieurs peuvent être inférieures à 100 fichiers VLF.

Notez que les VLF sont les divisions ou segments utilisés en interne par SQL Server dans le fichier journal des transactions.

Résolution

Informations sur les mises à jour cumulatives

SQL Server 2008 R2

Le correctif pour ce problème a été publié pour la première fois dans la mise à jour cumulative 6. Pour plus d’informations sur l’obtention de ce package de mise à jour cumulative pour SQL Server 2008 R2, cliquez sur le numéro d’article suivant pour afficher l’article dans la Base de connaissances Microsoft :

2489376 Package de mise à jour cumulative 6 pour SQL Server 2008 R2 Remarque Étant donné que les builds sont cumulatives, chaque nouvelle version de correctif contient tous les correctifs logiciels et tous les correctifs de sécurité inclus dans la version de correctif précédente SQL Server 2008 R2. Nous vous recommandons d’appliquer la version de correctif la plus récente qui contient ce correctif logiciel. Pour plus d'informations, cliquez sur le numéro ci-dessous pour afficher l'article correspondant dans la Base de connaissances Microsoft :

981356 Les builds SQL Server 2008 R2 qui ont été publiées après SQL Server 2008 R2 ont été publiées

SQL Server Service Pack 1 2008

Le correctif pour ce problème a d’abord été publié dans la mise à jour cumulative 12 pour SQL Server Service Pack 1 2008.

Remarque Étant donné que les builds sont cumulatives, chaque nouvelle version de correctif contient tous les correctifs logiciels et tous les correctifs de sécurité inclus dans la version de correctif précédente SQL Server 2008. Microsoft vous recommande d’appliquer la version de correctif la plus récente qui contient ce correctif logiciel. Pour plus d'informations, cliquez sur le numéro ci-dessous pour afficher l'article correspondant dans la Base de connaissances Microsoft :

970365 Les builds SQL Server 2008 qui ont été publiées après SQL Server 2008 Service Pack 1 a été publié Microsoft SQL Server 2008 correctifs logiciels sont créés pour des service packs de SQL Server spécifiques. Vous devez appliquer un correctif logiciel Service Pack 1 SQL Server 2008 à une installation de SQL Server Service Pack 1 2008. Par défaut, tout correctif logiciel fourni dans un service pack SQL Server est inclus dans le service pack de SQL Server suivant.

SQL Server Service Pack 2 2008

Le correctif pour ce problème a d’abord été publié dans La mise à jour cumulative 2 pour SQL Server Service Pack 2 2008.

Remarque Étant donné que les builds sont cumulatives, chaque nouvelle version de correctif contient tous les correctifs logiciels et tous les correctifs de sécurité inclus dans la version de correctif précédente SQL Server 2008. Microsoft vous recommande d’appliquer la version de correctif la plus récente qui contient ce correctif logiciel. Pour plus d'informations, cliquez sur le numéro ci-dessous pour afficher l'article correspondant dans la Base de connaissances Microsoft :

2402659 Les builds SQL Server 2008 publiées après SQL Server 2008 Service Pack 2 ont été publiées microsoft SQL Server les correctifs logiciels 2008 sont créés pour des service packs SQL Server spécifiques. Vous devez appliquer un correctif logiciel Service Pack 2 SQL Server 2008 à une installation de SQL Server Service Pack 2 2008. Par défaut, tout correctif logiciel fourni dans un service pack SQL Server est inclus dans le service pack de SQL Server suivant.

SQL Server Service Pack 3 2005

Le correctif pour ce problème a d’abord été publié dans la mise à jour cumulative 13 pour SQL Server Service Pack 3 2005.

Remarque Étant donné que les builds sont cumulatives, chaque nouvelle version de correctif contient tous les correctifs logiciels et tous les correctifs de sécurité inclus dans la version de correctif précédente SQL Server 2005. Microsoft vous recommande d’appliquer la version de correctif la plus récente qui contient ce correctif logiciel. Pour plus d'informations, cliquez sur le numéro ci-dessous pour afficher l'article correspondant dans la Base de connaissances Microsoft :

960598 Les builds SQL Server 2005 qui ont été publiées après SQL Server 2005 Service Pack 3 ont été publiées microsoft SQL Server les correctifs logiciels 2005 sont créés pour des service packs SQL Server spécifiques. Vous devez appliquer un correctif logiciel Service Pack 3 SQL Server 2005 à une installation de SQL Server Service Pack 3 2005. Par défaut, tout correctif logiciel fourni dans un service pack SQL Server est inclus dans le service pack de SQL Server suivant.

SQL Server Service Pack 4 2005

Le correctif pour ce problème a d’abord été publié dans La mise à jour cumulative 1 pour SQL Server Service Pack 4 2005.

Remarque Étant donné que les builds sont cumulatives, chaque nouvelle version de correctif contient tous les correctifs logiciels et tous les correctifs de sécurité inclus dans la version de correctif précédente SQL Server 2005. Microsoft vous recommande d’appliquer la version de correctif la plus récente qui contient ce correctif logiciel.

Vous devez appliquer un correctif logiciel Service Pack 4 SQL Server 2005 à une installation de SQL Server Service Pack 4 2005. Par défaut, tout correctif logiciel fourni dans un service pack SQL Server est inclus dans le service pack de SQL Server suivant.

État

Microsoft a confirmé l’existence de ce problème dans les produits Microsoft répertoriés dans la section « S’applique à ».

Solution de contournement

Installez ce correctif logiciel et redémarrez SQL Server peut améliorer les performances de récupération. Par exemple, les performances peuvent être améliorées pour les opérations suivantes après avoir installé ce correctif logiciel et redémarré SQL Server :

Récupération de base de données
Mise en miroir de bases de données
Capture instantanée de base de données
Activités du lecteur du journal de réplication transactionnelle de base de données

Si vous ne pouvez pas installer ce correctif logiciel, vous pouvez effectuer les solutions de contournement suivantes pour atténuer un problème existant et éviter une occurrence ultérieure.

Atténuer un problème existant

Attendre la fin

de l’opération de restauration ou de récupération Si vous avez une base de données non récupérée qui connaît des performances lentes lors de la restauration ou de la récupération de la base de données, vous devrez peut-être attendre que l’opération de restauration ou de récupération soit terminée. Par exemple, vous pouvez voir l’état hors connexion ou l’état de récupération dans SQL Server Management Studio (SSMS) pour une base de données non récupérée. L’arrêt SQL Server n’offre généralement aucun soulagement pour une récupération lente et peut prendre plus de temps pour répéter la même phase d’analyse de récupération, la même phase de restauration ou la même phase d’annulation.
Éviter de restaurer la séquence de journaux des transactions qui contient des milliers de fichiers VLF

Si vous rencontrez des performances lentes lors de la restauration et de la récupération d’une base de données à l’aide d’un fichier de sauvegarde, vous pouvez éviter de restaurer les séquences de journaux des transactions qui contiennent des milliers de fichiers VLF. Pour identifier le fichier de sauvegarde qui contient le plus de fichiers journaux virtuels enregistrés, utilisez l’instruction suivante pour afficher les colonnes FirstLSN et LastLSN dans les fichiers de sauvegarde du journal :
RESTORE HEADERONLY FROM DISK='C:\folder\file.trn'

Vous pouvez décider d’éviter de restaurer les fichiers de sauvegarde du journal. Vous pouvez également utiliser l’instruction STOP AT dans les commandes RESTORE pour éviter les parties hautement fragmentées des journaux des transactions. Si vous ne restaurez pas entièrement les séquences de journaux jusqu’au dernier point dans le temps pendant un scénario de récupération d’échec, la perte de données se produit dans votre SQL Server de base de données. Cette perte de données se produit parce que toutes les transactions ne sont pas conservées. Par conséquent, il y a un compromis commercial. Vous pouvez restaurer entièrement un journal des transactions très fragmenté. Toutefois, cette opération peut prendre plusieurs heures. Vous pouvez également utiliser l’instruction STOP AT dans la récupération pour arrêter la récupération avant la partie hautement fragmentée du journal. Toutefois, toutes les transactions manquantes que vous omettez sont perdues.

Notez que sans installer ce correctif logiciel, il n’existe généralement aucun recours sûr pour une récupération accélérée après le redémarrage SQL Server. SQL Server doit localiser la liste des fichiers VLF pour analyser les fichiers journaux, rétablir les transactions terminées, puis annuler les transactions incomplètes pour terminer la récupération afin de mettre la base de données en ligne en toute sécurité. Vous ne pouvez pas ignorer les transactions pendant la récupération en toute sécurité.

Empêcher une occurrence future

Définir l’incrément de croissance automatique de la base de données sur une taille

appropriée Si la taille de l’incrément de croissance automatique est trop petite, il y aura de nombreux fichiers journaux virtuels (VLF) et vous pouvez rencontrer des performances lentes dans SQL Server. Si la taille de l’incrément de croissance automatique est trop importante, les requêtes qui font croître automatiquement les journaux des transactions peuvent devoir attendre longtemps pour terminer une croissance. Par conséquent, une erreur de délai d’attente peut se produire dans SQL Server. Pour contourner ces problèmes, vous pouvez définir la taille de l’incrément de croissance automatique pour votre base de données sur une taille appropriée.
Éliminer le grand nombre de fichiers AVL et utiliser une croissance

manuelle Si de nombreux fichiers VLF se trouvent dans le journal des transactions, réduisez la taille du journal des transactions et augmentez-le avant les pics d’activité pour répondre à la demande à l’aide d’une croissance manuelle. Par exemple, le journal des transactions revient à une taille moyenne raisonnable par incrément important ou par croissance manuelle unique. Par conséquent, la taille du journal des transactions atteint une capacité maximale, et les fichiers de sauvegarde du journal sont planifiés régulièrement. En outre, le journal des transactions peut être tronqué et les fichiers VLF du journal des transactions peuvent être réutilisés dans un cycle.
Comment réduire et augmenter manuellement le journal des transactions

Pour corriger un journal qui a trop de fichiers VLF, procédez comme suit pour réduire le journal et l’augmenter à nouveau manuellement :
1. Si la base de données se trouve dans un mode de récupération complet ou journalisé en bloc, vous devez sauvegarder le journal des transactions pour permettre la tronquée et la réutilisation des fichiers VLF actifs. BACKUP LOG databasename TO DISK='C:\folder\log_backupfile.trn'
  Pour plus d’informations sur la sauvegarde du fichier journal des transactions à l’aide de SSMS, visitez le site web Microsoft Developer Network (MSDN) suivant :
  
  Comment sauvegarder le fichier journal des transactions à l’aide de SSMSPour plus d’informations sur la sauvegarde du fichier journal des transactions à l’aide d’instructions Transact-SQL, visitez le site web MSDN suivant :
  
  Comment sauvegarder le fichier journal des transactions à l’aide d’instructions Transact-SQL
2. Pour déterminer le nom logique du fichier journal des transactions, exécutez l’une des instructions suivantes.
  Instruction 1
  exec sp_helpfile, instruction 2
  sélectionnez * à partir de sys.sysfiles Pour réduire la taille du fichier journal des transactions à la taille souhaitée, utilisez le code suivant : DBCC SHRINKFILE(transactionloglogicalfilename, TRUNCATEONLY)
3. Vous pouvez augmenter la taille du fichier journal des transactions à une taille appropriée. Nous vous recommandons de laisser la taille du fichier journal des transactions atteindre la taille normale maximale. Par conséquent, l’incrément de croissance automatique est évité. Pour définir la taille du journal des transactions, utilisez la page Propriétés de la base de données dans SSMS ou utilisez la syntaxe ALTER DATABASE suivante : MODIFY FILE ( NAME = transactionloglogicalfilenae, SIZE=newtotalsize Mb)
  Pour plus d’informations sur l’augmentation de la taille d’une base de données dans SSMS, visitez le site web MSDN suivant :
  
  Comment augmenter la taille d’une base de données dans SSMSPour plus d’informations sur la syntaxe ALTER DATABASE MODIFY FILE, visitez le site web MSDN suivant :
  
  Informations générales sur la syntaxe ALTER DATABASE MODIFY FILE

Informations supplémentaires

Vous pouvez vérifier le nombre de segments VLF en examinant le fichier journal des erreurs SQL, puis en recherchant le numéro de séquence de journaux (LSN) dans chaque fichier de sauvegarde du journal des transactions. Les premiers chiffres avant le symbole deux-points dans les numéros LSN correspondent au numéro du LSN.

Par exemple, le premier numéro du premier message d’information pour le LSN est 1. Toutefois, le premier numéro du deuxième message d’information pour le LSN est 100001. Dans ce scénario, 100 000 fichiers VLF sont utilisés entre le moment du premier message d’information et le deuxième message d’information. Par conséquent, le journal des transactions fragmentés journalisé contenant de nombreux fichiers journaux virtuels ressemble à ce qui suit :

{Journal a été sauvegardé. Base de données : mydbname, date(heure) de création : 2010/07/08(12:36:46), premier numéro LSN : 1:5068:70, dernier LSN : 1:5108:1, nombre d’appareils de vidage : 1, informations sur l’appareil : (FILE=1, TYPE=DISK : {'C:\folder\logbackup1.trn'}). Il s’agit d’un message d’information uniquement. Aucune action de l’utilisateur n’est requise.

Le journal a été sauvegardé. Base de données : mydbname, date(heure) de création : 2010/07/08(15:36:46), premier numéro LSN : 100001:5108:1, dernier numéro de réseau local : 100002:5108:1, nombre d’appareils de vidage : 1, informations sur l’appareil : (FILE=2, TYPE=DISK : {'C:\folder\logbackup2.trn'}). Il s’agit d’un message d’information uniquement. Aucune action de l’utilisateur n’est requise.}

Le tableau suivant fournit plus d’informations sur les produits ou outils qui vérifient automatiquement la condition décrite dans la section « Symptômes » dans votre instance de SQL Server et dans les versions de SQL Server sur lesquelles la règle est évaluée.

Logiciel de règle	Titre de la règle	Description de la règle	Versions de produit par rapport auxquelles la règle est évaluée
System Center Advisor	SQL Server avec la réplication transactionnelle, les performances de l’Agent de lecture du journal peuvent être affectées en raison de la taille du journal des transactions ou du nombre de VLF	Dans ce SQL Server conseiller d’instance a détecté la réplication transactionnelle de présence avec un nombre de fichiers VLF ou de taille TLOG considérablement supérieur. Les performances de l’Agent de lecture du journal sont négativement affectées par la taille du journal des transactions ou le nombre de VLF. Réduisez la taille du journal des transactions et le nombre de VLF pour améliorer les performances de l’Agent de lecture du journal.	SQL Server 2008 SQL Server 2008 R2 SQL Server 2012

Références

Pour plus d’informations sur l’architecture physique du journal des transactions, visitez le site web MSDN suivant :

Informations générales sur l’architecture physique du journal des transactions

Pour plus d’informations sur les numéros de séquence de journal (LSN), visitez le site web MSDN suivant :

Informations générales sur les numéros

de séquence de journaux Pour plus d’informations sur l’erreur 1413 au démarrage de la mise en miroir de bases de données, visitez le site web MSDN suivant :

Informations générales sur l’erreur 1413 au démarrage

de la mise en miroir de bases de données Pour plus d’informations sur la façon dont une structure de fichier journal peut affecter le temps de récupération de la base de données, visitez le site web MSDN suivant :

Comment une structure de fichier journal peut affecter le temps

de récupération de la base de données Pour plus d’informations sur les fichiers VLF du journal des transactions, visitez le site web MSDN suivant :

Informations générales sur le fichier journal des transactions

Pour plus d’informations sur la création d’un instantané de base de données, visitez le site web MSDN suivant :

Comment créer un instantané de base de donnéesPour plus d’informations sur le modèle de maintenance incrémentielle pour SQL Server, cliquez sur le numéro d’article suivant pour afficher l’article dans la Base de connaissances Microsoft :

935897 Un modèle de maintenance incrémentielle est disponible auprès de l’équipe SQL Server pour fournir des correctifs logiciels pour les problèmes signalés. Pour plus d’informations sur le schéma d’affectation de noms pour SQL Server mises à jour, cliquez sur le numéro d’article suivant pour afficher l’article dans la Base de connaissances Microsoft :

822499Nouveau schéma d’affectation de noms pour les packages de mise à jour logicielle Microsoft SQL Server Pour plus d’informations sur la terminologie des mises à jour logicielles, cliquez sur le numéro d’article suivant pour afficher l’article dans la Base de connaissances Microsoft :

824684 Terminologie standard utilisée pour décrire les mises à jour logicielles Microsoft