[IT168 Server Academy] Les journaux de transactions sont une partie très importante mais souvent ignorée de la structure de la base de données. Comme il n’est pas aussi actif que le schéma de la base de données, peu de personnes prêtent attention au journal des transactions.
Le journal des transactions est un enregistrement des modifications de la base de données. Il peut enregistrer toute opération sur la base de données et enregistrer les résultats de l'enregistrement dans un fichier séparé. Pour chaque processus de transaction, le journal des transactions contient des enregistrements très complets et les fichiers de données peuvent être restaurés à leur état avant la transaction sur la base de ces enregistrements. Dès le début de l'action de transaction, le journal des transactions est dans un état d'enregistrement. Toutes les opérations sur la base de données pendant la transaction entrent dans la portée de l'enregistrement. L'enregistrement n'est terminé que lorsque l'utilisateur clique sur Soumettre ou Retour. Chaque base de données possède au moins un journal de transactions et un fichier de données.
Pour des raisons de performances, SQL Server stocke les modifications utilisateur dans le cache. Ces modifications sont immédiatement écrites dans le journal des transactions, mais pas dans le fichier de données. Le journal des transactions utilise un point de marquage pour déterminer si une transaction a écrit des données du cache vers le fichier de données. Lorsque SQL Server redémarre, il vérifie le dernier point de marquage dans le journal et efface les enregistrements de transaction après ce point de marquage, car ces enregistrements de transaction n'écrivent pas réellement les données du cache dans le fichier de données. Cela empêche ces transactions interrompues de modifier les fichiers de données.
Tenir à jour le journal des transactions
Étant donné que de nombreuses personnes oublient souvent le journal des transactions, cela peut également entraîner des problèmes dans le système. Au fur et à mesure que le système continue de fonctionner, de plus en plus d'enregistrements de journaux seront enregistrés et la taille des fichiers journaux deviendra également de plus en plus grande, conduisant finalement à un espace disque disponible insuffisant. À moins que les journaux ne soient nettoyés fréquemment au cours du travail quotidien, les fichiers journaux finiront par occuper tout l'espace disponible dans la partition. La configuration par défaut du journal est une capacité illimitée. Si vous travaillez dans cette configuration, il continuera à s'étendre et finira par occuper tout l'espace disponible. Les deux situations peuvent empêcher la base de données de fonctionner.
La sauvegarde de routine des journaux de transactions peut empêcher efficacement les fichiers journaux de consommer un espace disque excessif. Le processus de sauvegarde tronque les parties du journal qui ne sont plus nécessaires. La méthode de troncature consiste d'abord à marquer les anciens enregistrements comme inactifs, puis à écraser les nouveaux journaux à l'emplacement des anciens journaux, empêchant ainsi la taille du journal des transactions d'augmenter. Si des sauvegardes régulières du journal ne peuvent pas être effectuées, il est préférable de définir la base de données sur un « modèle de récupération simple ». Dans ce mode, le système forcera le journal des transactions à tronquer automatiquement chaque fois qu'un point de marquage est enregistré, écrasant ainsi l'ancien journal par un nouveau journal.
Le processus de troncature se produit lors de la sauvegarde ou du marquage d'anciens points comme inactifs, ce qui permet d'écraser les anciens enregistrements de transactions, mais ne réduit pas l'espace disque réel occupé par le journal des transactions. Même si le journal n’est plus utilisé, il occupera toujours un certain espace. Par conséquent, lors de la maintenance, le journal des transactions doit également être compressé. Les journaux de transactions sont compressés en supprimant les enregistrements inactifs, réduisant ainsi l'espace disque physique occupé par les fichiers journaux.
Le fichier journal des transactions de la base de données actuelle peut être compressé à l'aide de l'instruction DBCC SHRINKDATABASE. L'instruction DBCC SHRINKFILE est utilisée pour compresser le fichier journal des transactions spécifié. De plus, l'opération de compression automatique peut également être activée dans la base de données. Lorsqu'un journal est compressé, les anciens enregistrements sont d'abord marqués comme inactifs, puis les enregistrements marqués comme inactifs sont complètement supprimés. Selon la méthode de compression utilisée, vous ne verrez peut-être pas les résultats immédiatement. Idéalement, le travail de compression doit être effectué pendant les périodes où le système n'est pas très occupé, sinon cela pourrait affecter les performances de la base de données.
La restauration de la sauvegarde des enregistrements de transactions de base de données
peut être utilisée pour restaurer la base de données dans un état spécifié, mais la sauvegarde des enregistrements de transactions elle-même n'est pas suffisante pour terminer la tâche de restauration de la base de données et les fichiers de données sauvegardés doivent également participer au travail de récupération. Lors de la restauration d'une base de données, la première étape consiste à restaurer les fichiers de données. Ne définissez pas l'intégralité du fichier de données à l'état terminé tant que l'intégralité du fichier de données n'a pas été restaurée, sinon le journal des transactions ne sera pas restauré. Une fois la récupération du fichier de données terminée, le système restaurera la base de données à l'état souhaité par l'utilisateur via la sauvegarde du journal des transactions. S'il existe des sauvegardes de plusieurs fichiers journaux après la dernière sauvegarde de la base de données, le programme de sauvegarde les restaurera dans l'ordre en fonction de l'heure à laquelle ils ont été créés.
Un autre processus appelé envoi de journaux peut fournir des capacités de sauvegarde de base de données plus puissantes. Lorsque l'envoi de journaux est configuré, il peut copier l'intégralité de la base de données sur un autre serveur. Dans ce cas, les journaux de transactions sont également envoyés périodiquement au serveur de sauvegarde pour la récupération des données. Cela maintient le serveur dans un état de sauvegarde à chaud, le mettant à jour à mesure que les données changent. L'autre serveur est appelé serveur de surveillance et peut être utilisé pour surveiller les signaux d'expédition envoyés à des intervalles spécifiés. Si aucun signal n'est reçu dans le délai spécifié, le serveur de surveillance enregistrera cet événement dans le journal des événements. Ce mécanisme rend l'envoi de journaux souvent utilisé dans les plans de reprise après sinistre.
Le journal des transactionsd'optimisation des performances
joue un rôle important dans la base de données et a également un certain impact sur les performances globales du système. Grâce à plusieurs options, nous pouvons optimiser les performances du journal des transactions. Étant donné que le journal des transactions est un processus d'écriture continu sur le disque, aucune lecture n'a lieu pendant ce processus. Par conséquent, placer le fichier journal sur un disque indépendant jouera un certain rôle dans l'optimisation des performances.
Une autre mesure d'optimisation concerne la taille du fichier journal. Nous pouvons définir la taille du fichier journal pour qu'elle ne dépasse pas quelques pour cent de l'espace disque dur, ou déterminer sa taille. S'il est trop élevé, cela gaspillera de l'espace disque, et s'il est trop petit, cela forcera le fichier journal à essayer continuellement de se développer, entraînant une diminution des performances de la base de données.
Fichier journal des transactions Le fichier journal des transactions est un fichier utilisé pour enregistrer les mises à jour de la base de données, avec une extension ldf.
Dans SQL Server 7.0 et SQL Server 2000, si la fonctionnalité de croissance automatique est définie, le fichier journal des transactions se développera automatiquement.
En général, la taille du journal des transactions est stable lorsqu'elle peut accueillir le nombre maximum de transactions qui se produisent entre la troncature du journal des transactions, qui est déclenchée par un point de contrôle ou une sauvegarde du journal des transactions.
Cependant, dans certains cas, le journal des transactions peut devenir si volumineux qu'il manque d'espace ou devient plein. Généralement, lorsque le fichier journal des transactions remplit l'espace disque disponible et ne peut plus être développé, vous recevez un message d'erreur semblable au suivant :
Erreur : 9002, Gravité : 17, État : 2
Le fichier journal de la base de données '%.*ls' est plein.
En plus de ce message d'erreur, SQL Server peut marquer la base de données comme SUSPECT en raison du manque d'espace d'extension du journal des transactions. Pour plus d'informations sur la façon de récupérer de cette situation, consultez la rubrique « Espace disque faible » dans l'aide en ligne de SQL Server.
De plus, l'expansion du journal des transactions peut entraîner les situations suivantes :
· Très gros fichiers journaux de transactions.
· La transaction peut échouer et commencer à être annulée.
· Les transactions peuvent prendre beaucoup de temps.
· Des problèmes de performances peuvent survenir.
· Un blocage peut survenir.
Cause L'expansion du journal des transactions peut se produire pour les raisons ou situations suivantes :
· Transactions non validées · Transactions très volumineuses · Opérations : DBCC DBREINDEX et CREATE INDEX
· Lors de la restauration à partir d'une sauvegarde du journal des transactions · L'application client ne traite pas tous les résultats · La requête expire avant que le journal des transactions ne soit complètement développé et que vous receviez un faux message d'erreur « Journal plein » · Résolution de transaction non répliquée provoquée par le fichier journal full Lorsque la base de données SQL ne peut pas écrire dans le fichier, deux méthodes sont disponibles :
Une solution : effacer le journal.
1. Ouvrez l'analyseur de requêtes et entrez la commande DUMP TRANSACTION nom de la base de données AVEC NO_LOG
2. Ouvrez à nouveau Enterprise Manager--cliquez avec le bouton droit sur la base de données que vous souhaitez compresser--Toutes les tâches--Réduire la base de données--Réduire les fichiers--Sélectionner le fichier journal--Sélectionner la réduction à XXM en mode réduction, et un l'autorisation de rétrécir vous sera donnée ici. Pour atteindre le nombre minimum de M, saisissez directement ce numéro et validez.
L'autre méthode comporte certains risques, car le fichier journal de SQL SERVER n'est pas immédiatement écrit dans le fichier de base de données principal. S'il n'est pas géré correctement, cela entraînera une perte de données.
1 : Supprimer le journal
Détacher la base de données Enterprise Manager -> Serveur -> Base de données -> Clic droit -> Détacher la base de données 2 : Supprimer le fichier LOG Attacher la base de données Enterprise Manager -> Serveur -> Base de données -> Clic droit -> Attacher la base de données Cette méthode génère un nouveau LOG, la taille n'est que plus de 500K.
Remarque : Il est recommandé d'utiliser la première méthode.
Si à l’avenir, vous ne voulez pas que cela s’agrandisse.
Utilisé sous SQL2000 :
Cliquez avec le bouton droit sur la base de données-> Propriétés-> Options-> Récupération après échec-Modèle-Sélectionner-Modèle simple.
Ou utilisez l'instruction SQL :
modifier la base de données nom de la base de données définir la récupération simple
De plus, comme le montre la figure ci-dessus, les propriétés de la base de données ont deux options, liées à la croissance du journal des transactions :
Tronquer le journal au point de contrôle
(Cette option est utilisée dans SQL7.0. Dans SQL 2000, le modèle de récupération après incident est sélectionné comme modèle simple.)
Lorsque la commande CHECKPOINT est exécutée, le contenu du fichier journal des transactions est effacé s'il dépasse 70 % de sa taille. Définissez toujours cette option sur True lors du développement de la base de données.
Rétrécissement automatique
Vérifiez régulièrement la base de données lorsque l'espace inutilisé du fichier de base de données ou du fichier journal dépasse 25 % de sa taille, le système réduira automatiquement le fichier pour rendre l'espace inutilisé égal à 25 %. lors de sa création, il ne le sera pas. Le fichier réduit doit également être plus grand ou égal à sa taille d'origine. La réduction du fichier journal des transactions ne peut être effectuée que lors de sa sauvegarde ou lorsque l'option Tronquer le journal au point de contrôle est définie sur True.
Remarque : Généralement, les propriétés par défaut de la base de données créée par Li Cheng ont été définies, mais en cas de circonstances inattendues, les propriétés de la base de données sont modifiées. Veuillez effacer le journal, puis vérifier les propriétés ci-dessus de la base de données pour empêcher le journal des transactions de s'afficher. faire le plein à nouveau.