Voici un petit lot de requêtes qui permet de connaître le nombre de Virtual Log Files en cours d’utilisation …
En effet le fichier du journal des transactions est découpé en portions logiques, dont le nombre et la taille sont variables …
C’est en fait le cas si on n’a pas bien taillé le fichier dès la création de la base de données.
Ces portions logiques sont nommées fichiers virtuels du journal, ou encore Virtual Log Files, abrégé en VLF dans la littérature.
En effet ce fichier, comme les fichiers de données de la base de données, peut grossir autant que cela est nécessaire.
En suivant donc sa taille, on peut décider d’augmenter la fréquence des sauvegardes de ce fichier, qui permettent dans la majorité des cas de maintenir le fichier à une taille stable.
Un manÅ“uvre d’urgence consiste à effectuer une sauvegarde du fichier du journal des transactions avec l’option WITH TRUNCATE_ONLY (qui n’est plus disponible sous SQL Server 2008), puis à faire rétrécir le fichier.
Rappelons qu’en aucun cas autre que celui d’urgence ou exceptionnel, on ne rétrécira ce fichier, comme les autres fichiers de la base de données.
Il peut être nécessaire d’y avoir recours :
– par manque d’espace disque,
– parce qu’on a purgé un grand nombre de tables, et qu’on considère qu’elles n’atteindront plus jamais cette taille
– parce qu’on a changé le mode de récupération de la base de données vers le mode SIMPLE
En effet c’est ce dernier qui pilote l’utilisation du fichier du journal des transactions.
En résumé, dans le mode de récupération
=> SIMPLE, les fichiers virtuels du journal des transactions est vidé dès la fin d’une transaction
=> FULL toutes les transactions sont conservées dans leur intégralité jusqu’à la prochaine sauvegarde du fichier
=> BULK_LOGGED, toutes les transactions sont conservées dans leur intégralité jusqu’à la prochaine sauvegarde du fichier, sauf pour certaines opérations, comme la reconstruction des index ou encore le chargement de fichiers par BCP ou BULK INSERT, …
C’est là une vue très simple des modes de récupération, et certains facteurs peuvent affecter sa troncature :
– Une transaction qui a été laissée ouverte. On peut facilement le savoir à l’aide de l’instruction DBCC OPENTRAN.
РUne r̩plication en cours
– Une mise en miroir mise en pause
– Une lecture du fichier du journal des transactions (c’est très court mais cela peut se produire lorsqu’on utilise Change Data Capture
– Une création d’une capture instantanée de la base de données
– …
L’instruction (non documentée) DBCC LOGINFO permet de connaître le nombre de fichiers journaux virtuels, ainsi que de savoir combien d’entre eux sont actifs.
Il suffit de la décortiquer avec le lot de requête suivant pour avoir un rapide état des lieux :
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 | ------------------------------- -- Nicolas Souquet - 07/06/2011 ------------------------------- DECLARE @vlf TABLE ( FileId tinyint , FileSize bigint , StartOffset bigint , FSeqNo int , Status tinyint , Parity tinyint , CreateLSN varchar(21) ) INSERT @vlf EXEC ('DBCC LOGINFO') SELECT SUM(FileSize) / 1048576 AS total_file_size_MB , SUM ( CASE Status WHEN 2 THEN FileSize ELSE 0 END ) / 1048576 AS file_size_in_use_MB , COUNT(*) AS VLF_amount , SUM ( CASE Status WHEN 2 THEN 1 ELSE 0 END ) AS VLF_in_use FROM @vlf DBCC SQLPERF(logspace) |
Si l’on souhaite réduire le nombre de VLFs du fichier du journal des transactions, il faudra d’abord le faire rétrécir.
Pour ce faire, il faudra donc procéder à une sauvegarde fichier du journal des transactions (attention parfois deux sont nécessaires)
Ensuite on peut faire grossir le fichier par incréments suivant la règle d’allocation :
– Si la taille de l’incrément est comprise entre 1 et 64Mo, alors 4 VLFs seront ajoutés;
– Si la taille de l’incrément est comprise entre 65Mo et 1Go, alors 8 VLFs seront ajoutés;
– Si la taille de l’incrément plus grande qu’un 1Go, alors 16 VLFs seront ajoutés.
Pour faire grossir le fichier du journal des transactions, il suffit d’exécuter le squelette suivant :
1 2 | ALTER DATABASE maBaseDeDonnees MODIFY FILE (NAME = '[nomLogiqueDuFichierDuJournalDesTransactions]', SIZE = [nouvelleTaille][MB_ou_GB], FILEGROWTH = [tailleIncrement] |
Quelques détails :
=> [nomLogiqueDuFichierDuJournalDesTransactions] se trouve avec la requête suivante :
1 2 3 4 5 6 7 8 | ------------------------------- -- Nicolas Souquet - 07/06/2011 ------------------------------- SELECT name , physical_name , type_desc FROM sys.database_files WHERE type_desc = 'LOG' |
Ou plus généralement :
1 2 3 4 5 6 7 8 9 10 11 | ------------------------------- -- Nicolas Souquet - 07/06/2011 ------------------------------- SELECT D.name AS database_name , MF.name AS logical_file_name , MF.physical_name AS physical_file_name , type_desc AS file_type FROM sys.master_files AS MF INNER JOIN sys.databases AS D ON MF.database_id = D.database_id WHERE MF.type_desc = 'LOG' ORDER BY D.name |
=> [nouvelleTaille] est la taille que l’on souhaite que le fichier du journal des transaction ait
=> [tailleIncrement] est la taille de grossissement du fichier, si cela doit se produire
=> [MB_ou_GB] est le suffixe de taille : on précise MB ou GB, (mais on peut aussi mettre KB ).
Bonne gestion du fichier du journal des transactions !
ElSüket
@++ 
Hello,
Je suis plus pour réduire le journal à une taille définie plutôt que de faire un ‘TRUNCATEONLY’ et le réagrandir ensuite. Pendant l’opération de réagrandissement, le fichier LDF sera zéro-initialisé justement en raison de la structure des VLFs, ça risque de prendre du temps. Tu peux le voir en activant le TF 3004 dans la session qui fait l’alter database modify file.
Merci pour ton post, A+
David B.