Vérifier le nombre de VLF de vos bases

Un nombre trop important de VLF (Virtual Log File) peut être à l’origine de problèmes de ressources lors de l’exécution de votre instance SQL Server et notamment en ce qui concerne les sauvegardes.
Cet article montre un script SQL permettant de dénombrer les VLF de toutes vos bases.

Le journal de transactions est découpé en portions mises en mémoire à l’usage et intitulé Virtual Log File (VLF) c’est à dire « fichier virtuel de transaction ». Trop de VLF oblige a multiplier les ressources. Microsoft recommande de ne pas dépasser 200 VLF. La bonne moyenne tournant entre 60 et 100.

Le script suivant montre le nombre de VLF de toutes vos bases actives :

CREATE TABLE ##T (db               NVARCHAR(128), 
                  RecoveryUnitId   INT, 
                  FileId           SMALLINT, 
                  FileSize         NUMERIC(38, 0), 
                  StartOffset      NUMERIC(38, 0), 
                  FSeqNo           NUMERIC(38, 0), 
                  Status           Bit,
                  Parity           SMALLINT, 
                  CreateLSN        NUMERIC(38, 0));

DECLARE @CMD NVARCHAR(max) = N''

SELECT @CMD = @CMD + N'
INSERT INTO ##T (RecoveryUnitId, FileId, FileSize, StartOffset, FSeqNo, Status, Parity, CreateLSN)
EXEC (''USE [' + name + '];DBCC LOGINFO;'')
EXEC (''USE [' + name + '];UPDATE ##T SET db = DB_NAME() WHERE db IS NULL;'')'
FROM sys.databases 
WHERE source_database_id IS NULL
AND state = 0;

EXEC (@CMD);

SELECT db, COUNT(*) AS NB_VLF
FROM   ##T
GROUP  BY db
ORDER  BY 2 DESC;

DROP TABLE ##T ;

Le code du script nombre de VLF


Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
* Le site sur les SGBD relationnels et le SQL : https://sqlpro.developpez.com *
* le blog SQL, SQL Server, SGBDR... sur : https://blog.developpez.com/sqlpro/ *
* Expert Microsoft SQL Server, MVP (Most valuable Professional) depuis 14 ans *
* Entreprise SQL SPOT : modélisation, conseil, audit, optimisation, formation *
* * * * * Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence * * * * *

Ce contenu a été publié dans DBA, Performances, scripts SQL, SQL Server, avec comme mot(s)-clé(s) , , , , . Vous pouvez le mettre en favoris avec ce permalien.