Forum aux questions SQLJournaux de transactions de taille importante, conditions d'utilisation de Repair et bien plus encore

Paul S. Randal

Q J'ai remarqué un comportement étrange des sauvegardes que j'espère que vous pourrez expliquer. De temps à autres, nous sauvegardons notre base de données de production de 62 Go pour actualiser les données utilisées par nos développeurs d'applications. Nous supprimons toujours l'ancienne copie avant de restaurer la nouvelle. La copie restaurée possède la même taille que la base de données de production et les données semblent identiques, mais le processus de restauration est beaucoup plus long que le processus de sauvegarde. Que se passe-t-il ? Pourquoi la restauration prend-elle beaucoup plus de temps que la sauvegarde ?

R Il n'y a vraiment rien d'étrange. En fonction des circonstances, il s'agit généralement du comportement prévu. La différence de temps requise par une sauvegarde par rapport à une restauration provient des étapes que chaque processus doit exécuter.

La sauvegarde d'une base de données comprend deux étapes. Elle effectue essentiellement des E/S de lecture de la base de données et des E/S d'écriture sur le périphérique de sauvegarde :

Étape de sauvegarde 1 Lire toutes les données allouées depuis les fichiers de données et les écrire sur le périphérique de sauvegarde.

Étape de sauvegarde 2 Lire un journal des transactions et l'écrire sur le périphérique de sauvegarde.

La taille exacte de journaux des transactions nécessaire peut varier grandement, mais il s'agit essentiellement de la taille requise pour pouvoir récupérer la base de données restaurée à un moment déterminé. (J'ai donné une explication plus approfondie de ceci dans un message de blog à l'adresse sqlskills.com/blogs/paul/2008/01/31/MoreOnHowMuchTransactionLogAFullBackupIncludes.aspx.)

Restaurer une base de données, d'autre part, peut comprendre jusqu'à quatre étapes. Le travail impliqué est en outre plus compliqué que de simples E/S de lecture et écriture :

Étape de restauration 1 Si les fichiers de base de données n'existent pas, créez-les.

Étape de restauration 2 Lire toutes les données et le journal des transactions à partir de la sauvegarde et les écrire dans les fichiers de base de données correspondants.

Étape de restauration 3 Exécuter la phase REDO de la récupération sur le journal des transactions.

Étape de restauration 4 Exécuter la phase UNDO de la récupération sur le journal des transactions.

Le temps total écoulé pour les deux étapes de la sauvegarde doit être à peu près identique au temps requis pour l'étape de restauration 2 (en supposant qu'elle dispose de matériel similaire et d'un serveur sans activité utilisateur). L'étape 1 de la restauration peut être longue si les fichiers de données sont de taille importante et doivent être remis à zéro (ce qui est le comportement de SQL Server 2000 et le comportement par défaut de SQL Server 2005).

Pour empêcher ceci, ne supprimez pas les fichiers existants avant de démarrer la restauration. Vous pouvez également activer l'initialisation instantanée afin que les fichiers soient créés très rapidement (vous trouverez plus d'informations à ce sujet à l'adresse msdn.microsoft.com/library/ms175935.aspx).

Les étapes 3 et 4 de la restauration exécutent la récupération de la base de données restaurée pour la rendre cohérente du point de vue des transactions. Il s'agit du processus qu'une base de données traverserait également pendant la récupération après incident. La durée nécessaire à cette récupération dépendra de la taille du journal des transactions devant être traitée. Par exemple, si une transaction de longue durée était active au moment auquel a été effectuée la sauvegarde, tout le journal des transactions pour cette transaction est dans la sauvegarde et doit être restauré.

Q J'essaie de choisir entre l'envoi de journaux et la mise en miroir de base de données pour fournir une copie redondante de notre base de données de production. Je m'inquiète de la taille du journal des transactions qui devra être envoyée entre les serveurs, en particulier pour les opérations de reconstruction d'index que nous effectuons chaque nuit. J'ai entendu dire que la mise en miroir envoie les commandes de reconstruction au lieu du journal des transactions et que la reconstruction est effectuée sur le miroir. Est-ce vrai ? Ceci devrait faire de la mise en miroir une solution meilleure que l'envoi de journaux avec le modèle de récupération BULK_LOGGED, n'est-ce pas ?

R Ce que vous avez entendu dire n'est pas vrai. La mise en miroir de base de données s'effectue en envoyant les véritables enregistrements de journal des transactions de la base de données principale vers le serveur de miroir, où ils sont « réeffectués » dans la base de données de miroir. Aucune traduction et aucun filtrage de type quelconque n'interviennent, pas plus qu'un type quelconque d'interception de commandes T-SQL pour une base de données en miroir.

La mise en miroir de base de données prend uniquement en charge le modèle de récupération FULL (complet), ce qui signifie qu'une opération de reconstruction d'index sera toujours entièrement enregistrée dans le journal. En fonction de la taille des index concernés, ceci pourrait signifier qu'une quantité significative de journal des transactions est produite et provoquer à son tour un gros fichier journal sur la base de données principale et une bande passante de réseau substantielle pour envoyer les enregistrements de journal au miroir.

Vous pouvez considérer la mise en miroir de base de données comme un envoi en temps réel de journaux (ce qui en fait était le nom utilisé pour cette fonctionnalité très tôt durant le développement de SQL Server 2005). Avec l'envoi de journaux, les sauvegardes du journal des transactions de la base de données principale sont périodiquement expédiées au serveur secondaire et restaurées sur la base de données secondaire.

L'envoi de journaux prend en charge les modèles de récupération FULL et BULK_LOGGED. Pour une opération de reconstruction d'index dans une base de données à envoi de journaux avec modèle de récupération FULL, la quantité de journal des transactions produite sera identique à celle produite pour une base de données mise en miroir. Cependant, dans le scénario de base de données avec envoi de journaux, les données sont envoyées à la base de données redondante dans une sauvegarde de journal (ou un ensemble de sauvegardes de journal) au lieu d'un flux continu.

Si le modèle de récupération BULK_LOGGED est utilisé dans la base de données à envoi de journaux pendant que l'index est reconstruit, seule une taille minimale de journal des transactions sera produite. Cependant, la sauvegarde suivante du journal des transactions contiendra également toutes les étendues de fichier de données qui ont été modifiées par l'opération de reconstruction d'index à enregistrement minimum dans le journal. Ceci signifie que les sauvegardes de journal couvrant la reconstruction d'index dans le modèle de récupération BULK_LOGGED auront presque exactement la même taille que ceux couvrant la reconstruction d'index dans le modèle de récupération FULL.

Par conséquent, la quantité d'informations nécessitant d'être envoyée à la base de données redondante est presque exactement la même pour un index reconstruit sur une base de données en miroir et sur une base de données à envoi de journaux. La véritable différence réside dans la manière dont les informations sont envoyées : en continu ou par lots.

Il existe de nombreux autres facteurs à prendre en compte pour le choix parmi ces deux approches (beaucoup trop de facteurs pour les aborder dans un seul article du Forum aux questions SQL). Vous devez observer dans quelle mesure ces facteurs correspondent à vos exigences (comme la limite de perte de données acceptable et le temps d'indisponibilité admissible) avant de prendre une décision.

Q J'exécute SQL Server 2005 et l'une de mes bases de données possède un journal des transactions qui n'arrête pas de grossir. La base de données est en mode de récupération complet et j'effectue des sauvegardes de journal des transactions. Je pensais que ceci devait empêcher le journal des transactions de grossir. Que se passe-t-il exactement ici ?

R Vous avez raison, la prise de sauvegardes de journal des transactions en mode de récupération complet est essentielle. Cependant, il existe d'autres facteurs qui peuvent contribuer à la croissance du journal des transactions. Cela dépend entièrement de ce qui fait que le journal des transactions est nécessaire (ou actif). Les autres facteurs courants (mis à part un manque de sauvegardes de journal des transactions) qui peuvent provoquer votre problème incluent la réplication, la mise en miroir de base de données et une transaction active.

La réplication effectue une lecture asynchrone de vos enregistrements de journal des transactions et charge ensuite les transactions à répliquer dans une base de données de distribution séparée. Tous les enregistrements de journal des transactions qui n'ont pas encore été lus par la tâche de lecteur de journal de réplication ne peuvent pas être libérés. Si votre charge de travail produit beaucoup d'enregistrements de journal des transactions et que vous avez défini un intervalle important pour la fréquence d'exécution du lecteur de journal de réplication, de nombreux enregistrements peuvent s'accumuler et provoquer l'augmentation de la taille du journal des transactions.

Si vous exécutez une mise en miroir de base de données asynchrone, il peut exister une accumulation des enregistrements de journal des transactions qui n'ont pas été envoyés de la base de données principale au miroir (qui ont appelé la file d'attente SEND de mise en miroir de base de données). Les enregistrements de journal des transactions ne peuvent pas être libérés tant qu'ils n'ont pas réussi à être envoyés. Avec un taux élevé de génération d'enregistrements de journal des transactions et une bande passante limitée sur le réseau (ou d'autres problèmes de matériel), l'accumulation peut grossir énormément et provoquer une augmentation de la taille du journal des transactions.

Pour terminer, si un utilisateur démarre une transaction explicite (comme l'utilisation d'une instruction BEGIN TRAN) et effectue ensuite une modification (telle qu'une instruction DDL ou une action d'insertion/mise à jour/suppression), les enregistrements de journal des transactions générés doivent être conservés jusqu'à ce que l'utilisateur valide ou restaure la transaction. Ceci signifie que tous les enregistrements de journal des transactions suivants produits par les autres transactions ne peuvent pas non plus être libérés, car le journal des transactions ne peut pas être libéré de manière sélective. Si cet utilisateur, par exemple, rentre chez lui après sa journée de travail sans terminer la transaction, le journal des transactions continue à grossir car de plus en plus d'enregistrements de journaux des transactions sont générés sans pouvoir être libérés.

Vous pouvez découvrir pourquoi le journal des transactions ne peut pas être libéré en interrogeant la vue de catalogue de système sys.databases et en examinant la colonne log_reuse_wait_desc comme ceci :

SELECT name AS [Database], 
  log_reuse_wait_desc AS [Reason]
FROM master.sys.databases;

Si la raison s'avère être une transaction active, vous pouvez utiliser l'instruction DBCC OPENTRAN pour obtenir plus d'informations sur la transaction :

DBCC OPENTRAN ('dbname')

Q J'ai entendu dire que REPAIR_ALLOW_DATA_LOSS devrait uniquement être utilisé en dernier recours pour une récupération après altération. La restauration depuis les sauvegardes devrait être utilisée à sa place. Pouvez-vous m'expliquer pourquoi Repair pour SQL Server 2005 ne devrait pas être utilisé et pourquoi il est inclus dans le produit, considérant à quel point il est « dangereux » ?

R Pour commencer, j'ai en fait écrit Repair pour SQL Server 2005. Le problème avec REPAIR_ALLOW_DATA_LOSS (que j'appellerai simplement Repair à partir de maintenant) est que son fonctionnement n'est pas clair. Le nom de l'option Repair a été choisi pour indiquer que son exécution peut mener à une perte de données de la base de données. La manière dont la fonctionnalité répare habituellement une structure de base de données endommagée consiste à supprimer la structure endommagée et à corriger ensuite la base de données qui a référencé ou était référencée par la structure supprimée. Repair est véritablement prévu pour être une méthode de dernier recours pour rendre la base de données cohérente. Il n'a pas pour objectif essentiel de sauvegarder les données de l'utilisateur. Repair ne s'efforce pas de supprimer les données de l'utilisateur, mais il ne s'efforce pas non plus de les enregistrer.

Ceci peut sembler être une méthode irresponsable pour la conduite des réparations, mais lorsque Repair nécessite d'être utilisé, il offre la méthode la plus rapide et la plus fiable de réparation de l'altération. La vitesse a une importance essentielle dans une situation de récupération après incident et l'exactitude est absolument nécessaire. Il est presque impossible de créer des algorithmes de réparation plus complexes pouvant en toute certitude fonctionner rapidement et correctement dans tous les cas. Il existe certains algorithmes complexes dans le code de réparation pour résoudre par exemple le problème se posant lorsque la même page ou étendue est allouée à deux index, mais l'algorithme se compose essentiellement de Repair plus quelques corrections.

Il existe cependant certains problèmes avec Repair dont vous devez être conscient :

• Repair ne prend pas en compte les contraintes de clé étrangère lors de la suppression des structures endommagées et peut donc supprimer des enregistrements d'une table possédant une relation de clé étrangère avec une autre table. Il n'existe aucun moyen de déterminer si ceci s'est produit sans exécuter DBCC CHECK­CONSTRAINTS après avoir exécuté Repair.
• Repair ne prend pas en compte (et n'a pas la possibilité de le faire) la logique métier inhérente ou les relations de données définies au niveau de l'application qui pourraient être mises en défaut par la suppression de certaines données. Encore une fois, il n'existe aucune possibilité de déterminer si quoi que ce soit est en défaut, à moins d'exécuter la vérification de cohérence personnalisée intégrée à l'application.
• Certaines opérations de Repair ne peuvent pas être répliquées. L'exécution de Repair sur une topologie à distributeur ou pair à pair peut introduire des incohérences dans la topologie, ce qui doit être corrigé manuellement.

Pour ces raisons, il est toujours judicieux d'effectuer une récupération après altération en utilisant une sauvegarde au lieu d'exécuter Repair. Repair est toutefois proposé dans le produit car il est toujours possible de se retrouver dans une situation dans laquelle la base de données est endommagée, il n'existe aucune sauvegarde et vous devez trouver un moyen de remettre en ligne la base de données rapidement.

Q Je viens juste de commencer dans une nouvelle société en tant qu'administrateur de bases de données et j'ai la responsabilité de plusieurs applications et de leurs bases de données principales. L'une des applications offre de très mauvaises performances durant les mises à jour. J'ai fait une enquête et j'ai découvert que les tables utilisées par l'application possèdent chacune un nombre important d'index. Après m'être renseigné autour de moi, il semble que l'ancien administrateur de base de données avait l'habitude d'ajouter un index à chaque colonne de table, plus certaines combinaisons. J'ai du mal à croire que tous ces index sont nécessaires. Comment déterminer ceux que je peux abandonner sans incident ? Nous utilisons SQL Server 2005.

R Le nombre important d'index est sans doute le facteur ayant la plus grande responsabilité dans la médiocrité des performances, comme vous l'avez déduit. Chaque fois qu'une ligne est insérée, mise à jour ou supprimée de la table, des opérations correspondantes doivent être effectuées dans chaque index non mis en cluster. Ceci ajoute une charge importante en termes d'E/S, d'utilisation d'UC et de génération du journal des transactions.

Dans SQL Server 2000, le seul moyen de déterminer les index qui étaient utilisés était d'utiliser le profilage et d'examiner les diagrammes de requêtes. Il existe une nouvelle vue dans SQL Server 2005, la vue de gestion dynamique (DMV), qui effectue un suivi de l'utilisation des index : sys.dm_db_index_usage_stats.

Cette vue effectue un suivi de chaque utilisation d'index (et de la manière dont il a été utilisé) depuis que la base de données dont il fait partie a démarré. Les statistiques de toutes les bases de données sont perdues lorsque SQL Server est arrêté et les statistiques de chaque base de données sont perdues lorsque cette base de données est fermée ou détachée. Le principe de cette manipulation est que, si un index n'apparaît pas dans le résultat, il n'a pas été utilisé depuis le démarrage de la base de données.

Une approche simple de suivi de l'utilisation des index consiste à effectuer des instantanés périodiques du résultat de la vue DMV et de comparer les instantanés. Ce que de nombreuses personnes négligent est qu'il faut effectuer un suivi de l'utilisation des index au cours d'un cycle d'activité complet. Si vous avez uniquement pris un instantané pour une seule journée, vous trouverez peut-être plusieurs index non utilisés. Mais si ces index sont utilisés, imaginons, pour permettre une exécution beaucoup plus rapide des rapports de fin de mois, ils ne devraient probablement pas être supprimés. Si un index n'a véritablement pas été utilisé au cours d'un cycle d'activité complet, vous pouvez alors probablement l'abandonner, récupérer l'espace et améliorer les performances.

Pour obtenir un code simple que vous pouvez utiliser pour prendre des instantanés périodiques du DMV, consultez mon article de blog à l'adresse sqlskills.com/blogs/paul/2007/10/05/IndexesFromEveryAngleHowCanYouTellIfAnIndexIsBeingUsed.aspx.

Paul S. Randal est directeur général de SQLskills.COM et MVP SQL Server. Paul a écrit DBCC CHECKDB/repair pour SQL Server 2005 et était responsable de Core Storage Engine pendant le développement de SQL Server 2008. Expert de la récupération après incident, de la haute disponibilité et de la maintenance de base de données, Paul présente fréquemment des conférences. Vous trouverez son blog à l'adresse SQLskills.com/blogs/paul.