Par: Sergey Gigoyan | Mise à jour: 2015-11-18 | Commentaires (9) | Connexes: Plus > Administration de base de données
Problème
SQL Server permet d’utiliser plus d’un fichier journal de transactions, mais la question se pose de savoir s’il est nécessaireet quel est l’avantage d’avoir plusieurs fichiers journaux de transactions. Certains développeurs pensent à tort que le fait d’avoir des fichiers journaux multiplétransaction peut augmenter les performances car SQL Server peut les utiliser en parallèle. SQL Server utiliseun seul fichier journal des transactions pour le moment et il n’y a pas de parallélisme dans ce cas. Parfois, plus d’un fichier journal peut êtreeffectué à des fins de dépannage. Donc, normalement, il n’est pas nécessaire d’avoir plus d’un fichier journal.Considérons un cas, lorsque notre base de données contient plus d’un fichier journal et que nous ne devrions en conserver qu’un seul.Cette astuce vise à décrire comment supprimer correctement les fichiers journaux inutiles et n’en conserver qu’un seul.
Solution
Avant de commencer à illustrer comment supprimer les fichiers journaux inutiles, décrivons brièvement le fonctionnement de SQL Server avec des fichiers journaux: lorsque la base de données contient plusieurs fichiers journaux, SQL Server continue d’écrire sur le premier jusqu’à ce qu’il soit plein, puis passe au second et ainsi de suite. Une fois que le dernier fichier journal devient complet, SQL Server revient au premier et le cycle se poursuit.Cependant, comme nous l’avons mentionné, plusieurs fichiers journaux peuvent parfois être nécessaires. Par exemple, lorsque le disque, où se trouve le fichier journal, devient plein et nous devons créer le deuxième fichier journal dans un autre emplacement, mais après avoir résolu le problème, nous devons supprimer le deuxième fichier journal, car il n’est pas utile d’avoir plus d’un fichier journal.Maintenant, supposons que nous ayons une base de données avec deux fichiers journaux, et notre tâche est de supprimer le second.Le script suivant crée la base de données TestDB avec deux fichiers journaux et une table TestTable. Pour l’exécuter par vous-même, vous devrez remplacer « D:\SQL Données » avec un chemin de dossier existant.
USE GOCREATE DATABASE ON PRIMARY ( NAME = N'TestDB', FILENAME = N'D:\SQL DATA\TestDB.mdf' , SIZE = 4096KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ) LOG ON ( NAME = N'TestDB_log1', FILENAME = N'D:\SQL DATA\TestDB_log1.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%), ( NAME = N'TestDB_log2', FILENAME = N'D:\SQL DATA\TestDB_log2.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)GOUSE TestDBGOCREATE TABLE TestTable(ID INT IDENTITY(1,1),Value BIGINT)--Change recovery model for TestDB to FULL (if your model database is in FULL recovery model, TestDB has been already created in this mode)ALTER DATABASE TestDB SET RECOVERY FULL
Comprenons maintenant la structure physique du journal des transactions: Le fichier journal des transactions interne se compose Defichiers journaux virtuels (VLF), qui sont l’unité de gestion du fichier journal des transactions. Cela signifie que lorsque le moteur de base de données grandit ou rétrécit le fichier journal, il le fait avec des VLF complets (par exemple, il ne peut pas réduire la moitié du VLF). La taille des fichiers journaux virtuels ainsi que leur numéro dans le fichier journal physique n’est pas fixe et est gérée dynamiquement par le moteur de base de données. Pour surveiller le fichier journal des transactions en internenous utilisons la commande « DBCC LOGINFO », qui fournit des informations sur les fichiers journaux virtuels. Dans le script ci-dessous, nous avons utilisé cette commande pour notre base de données:
--Getting log files infoDBCC LOGINFO('TestDB')
Et le résultat est le suivant:
FileID est un ID de fichier journal pour notre base de données. L’état indique que VLF est réutilisable ounot (valeurs possibles: 0 – oui, 2 – non). Comme nous pouvons le voir, il n’y a qu’un seul VLF avecStatus=2. Maintenant, quand nous allons insérer des données dans la table de test et surveiller la croissance des fichiers journaux:
USE TestDBGO--Checking log information before insertionSELECT file_id, name, type_desc, physical_name, size, max_sizeFROM sys.database_files--Inserting data into TestTable;WITH ValueTable AS(SELECT 1 nUNION ALL SELECT n+ 1FROM ValueTableWHERE n
Avec cet exemple, nous pouvons voir que les deux fichiers journaux ont augmenté et qu’il y a maintenant des VLF avec « Status = 2 » dans le deuxième fichier journal (FileID = 3) également:
Maintenant, nous devons supprimer TestDB_log2.fichier ldf. Notez que nous ne pouvons supprimer que les fichiers journaux secondaires. La suppression du fichier journal principal n’est pas autorisée par SQL Server.Chaque base de données n’a qu’un seul fichier journal principal et le premier fichier journal créé dans le script de création de base de données est considéré comme le principal.Si nous essayons de supprimer le deuxième fichier journal:
USE masterGO--Remove TestDB_log2 fileALTER DATABASE TestDB REMOVE FILE TestDB_log2
Nous recevrons le message suivant:
Nous ne pouvons supprimer le fichier journal des transactions que lorsqu’il est vide, nous devons donc d’abord le vider it.To pour ce faire, nous devrions sauvegarder le journal des transactions. Étant donné que notre base de données « TestDB » est nouvellement créée et qu’il n’y a pas de sauvegardes complètes, nous devons émettre une sauvegarde complète de la base de données pour la base de données TestDB, après quoi nous pouvons émettre une sauvegarde du journal des transactions:
--Full backupBACKUP DATABASE TestDB TO DISK =N'D:\SQL DATA\TestDB.bak'--Transaction log backupBACKUP LOG TestDB TO DISK =N'D:\SQL DATA\TestDB.trn'
La sauvegarde du journal des transactions tronque le fichier journal (il y a quelques exceptions, qui sont hors de portée de cettep). La troncature du journal supprime les fichiers journaux virtuels inactifs du début du journal logique et libère de l’espace dans le fichier journal. Cependant, la troncature ne réduit pas la taille d’un fichier journal physique. Cela ne fait que libérer de l’espace, qui peut être réutilisé. Lançons à nouveau « DBCC LOGINFO »:
Comme nous pouvons le voir, il n’y a pas de fichiers journaux virtuels dans le « TestDB_log2.fichier « ldf » avec Status= 2 et maintenant notre fichier journal est vide et prêt à être supprimé:
--Remove TestDB_log2 fileALTER DATABASE TestDB REMOVE FILE TestDB_log2
Le retrait est réussi.
Cependant, lorsque nous vérifions à nouveau le journal des informations, nous verrons que le fichier journal logique existe toujours:
--Checking log informationSELECT file_id, name, type_desc, physical_name, size, max_sizeFROM sys.database_files
Si nous effectuons une autre sauvegarde du journal, le fichier sera supprimé:
--Transaction log backupBACKUP LOG TestDB TO DISK =N'D:\SQL DATA\TestDB.trn'--Checking log informationSELECT file_id, name, type_desc, physical_name, size, max_sizeFROM sys.database_files
Étapes suivantes
- Gardez cette astuce à l’esprit pour déterminer l’utilisation du journal des transactions et la façon de supprimer le fichier journal inutile.
- Consultez ces ressources :
- Administration de la base de données | Journal des transactions
- https://technet.microsoft.com/en-us/library/ms191433( v = sql.105).aspx
- https://technet.microsoft.com/en-us/library/ms179355( v = sql.105).aspx
- https://technet.microsoft.com/en-us/library/ms345414( v = sql.105).aspx
À propos de l’auteur
Sergey Gigoyan est un professionnel de la base de données avec plus de 10 ans d’expérience, spécialisé dans la conception de bases de données, le développement, le réglage des performances, l’optimisation, la haute disponibilité, la conception BI et DW.Voir tous mes conseils
Article Dernière mise à jour : 18/11/2015