Récemment un internaute s’est étonné de voir que lorsqu’il réalisait une opération de réduction de fichier à l’aide de la commande DBCC SHRINFILE après une sauvegarde du journal des transactions, celui-ci ne se réduisait pas à la 1ère tentative. Une seconde opération de journal était nécessaire avant de pouvoir réduire le fichier à la taille désirée. Quelle en est la cause ? Nous allons pouvoir y répondre en utilisation la commande DBCC LOGINFO.

Tout d’abord un bref rappel sur les journaux de transactions et de leur architecture. Le journal des transactions est composé d’un certain nombre de fichiers journaux virtuels. La taille et le nombre de ces fichiers sont variables et ces paramètres sont contrôlés par le moteur SQL. Selon les besoins SQL Server alloue crée ou étend ces fichiers virtuels.

Fichier journal avec physique avec ses fichiers journaux virtuels

Au fur et à mesure que le journal des transactions se remplit, les fichiers journaux virtuels le sont également et sont marqués comme actifs et utilisés. Pour illustrer notre propos prenons le cas d’un fichier journal rempli à 100%. Nous allons exécuter la série de script suivante qui permet de créer une base de données avec un journal des transactions limité à 1Go et une table dans laquelle nous y insérerons des données pour ce journal.

USE master
GO
/****** Database test ******/
CREATE DATABASE test
ON  PRIMARY
(
  NAME = N’test’,
  FILENAME = N’D:\MSSQL\DATA\test.mdf’,
  SIZE = 1830656KB ,
  MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB
)
LOG ON
/* Bien penser à fixer une taille limite pour
   le journal des transactions –> ici 1Go */
(
  NAME = N’test_log’,
  FILENAME = N’E:\MSSQL\LOG\test_log.LDF’ ,
  SIZE = 104000KB ,
  MAXSIZE = 1048576KB,
  FILEGROWTH = 10%
);
GO

/* Pour les tests on s’assure que le mode de récupération
   est FULL */
ALTER DATABASE test SET RECOVERY FULL;
GO

et

USE test;
GO

/* Table de remplissage pour remplir le
   fichier journal */
CREATE TABLE dbo.t1
(
c1 INT IDENTITY(1,1) NOT NULL,
c2 VARCHAR(8000) NOT NULL,
c3 VARCHAR(8000) NOT NULL
);

Ensuite nous allons effectuer une sauvegarde complète de la base qui permettra de remplir le journal des transactions lorsque nous insérerons les données dans la table prévue à cet effet. Pour rappel en mode de récupération FULL, sans une sauvegarde complète le journal se remplira et se videra au fur et à mesure car il n’existe aucun point de séquençage pour permettre une restauration cohérente de la base..

/* Sauvegarde FULL pour permettre le remplissage
   du journal des tran */
BACKUP DATABASE test TO DISK = ‘E:\test.BAK’ WITH INIT;

Et enfin exécutions le script permettant de remplir notre journal des transactions. On peut surveiller en parallèle le taux de remplissage du journal en exécutant à intervalle régulier la commande DBCC SQLPERF(LOGSPACE). Une fois le journal des transactions plein, le script s’arrête et génère une erreur.

WHILE 1=1
BEGIN
INSERT INTO dbo.t1 (c2,c3) VALUES (REPLICATE(‘T’,8000),REPLICATE(‘T’,8000));
END;

Notre journal des transactions est maintenant plein et tous les fichiers virtuels sont marqués comme actifs comme l’illustre la figure ci-dessous.

 

La commande DBCC LOGINFO permet de le vérifier car elle donne des informations intéressantes à propos des fichiers virtuels à l’intérieur de notre fichier journal.

DBCC LOGINFO(‘test’);
GO

et le résultat :

Chaque numéro de ligne correspond à un fichier journal virtuel. Dans notre cas, le fichier journal est composé de 120 fichiers virtuels. La colonne FileSize correspond à la taille d’un fichier journal. On peut, avec ces informations, facilement vérifier que la somme globale de taille des fichiers journaux correspond bien à celle du fichier journal physique.

Le script suivant permet de le vérifier :

DECLARE @t TABLE
(
FileId INT,
FileSize BIGINT,
StartOffset INT,
FSeqNo INT,
Status TINYINT,
Parity INT,
CreateLSN NUMERIC(38)
);

INSERT INTO @t
EXEC(‘DBCC LOGINFO( »test »)’);

SELECT CAST(SUM(FileSize) / (1024.0 * 1024.0) AS DECIMAL(8,2))  AS size_journal_in_Mo
FROM @t;
GO

DBCC SQLPERF(LOGSPACE);
GO

et le résultat :

 

C’est la colonne Status qui nous intéresse le plus ici. La valeur 2 signifie que le fichier journal virtuel est actif et utilisé et la valeur 0 que celui-ci est inactif et libre. Pour le moment on constate que tous les lignes de la table ont la colonne Status avec une valeur égale à 2 ce qui signifie que les 120 fichiers virtuels sont actifs et utilisés.

Maintenant pour pouvoir vider le journal des transactions il faut d’abord effectuer une sauvegarde de celui-ci. Cette opération permet de tronquer le journal et de transformer l’espace utilisé en espace libre. Les fichiers journaux virtuels sont également marqués comme inactifs en théorie.

Procédons à une sauvegarde du journal des transactions :

BACKUP LOG test TO DISK = ‘E:\test.TRN’ WITH INIT;
GO

et vérifions maintenant l’espace libre du journal à l’aide de la commande DBCC SQLPERF(LOGSPACE)

 

Tout s’est passé comme prévu car le journal des transactions est maintenant occupé à seulement 1,9%. Tentons de réduire maintenant notre fichier journal à 100 Mo à l’aide du script suivant :

DBCC SHRINKFILE(test_log,100);
GO

et le résultat :

 

On constate que notre réduction de fichiers n’a pas fonctionné car la taille actuelle du journal est toujours d’environ 1Go (128248 * 8 / 1024). Comment cela est il possible alors que l’espace libre dans le journal est d’environ 98% ?

Lançons la commande suivante :

DBCC LOGINFO(‘test’);
GO

et le résultat :

A l’évidence nous avons toujours les 120 journaux virtuels. Il faut savoir que la commande DBCC SHRINKFILE opère toujours en commençant par la fin du fichier. Hors ici on constate que le dernier fichier journal virtuel est encore actif (Status = 2). Cela explique que la réduction de fichiers n’a pas fonctionné pour le moment.

Pour résoudre ce problème lançons une deuxième la sauvegarde du journal :

BACKUP LOG test TO DISK = ‘E:\test.TRN';
GO

et de nouveau le script :

DBCC LOGINFO(‘test’);
GO

avec le résultat :

Plus aucun fichier journal virtuel n’est marqué comme actif. La commande DBCC SHRINKFILE devrait maintenant pouvoir réduire le journal des transactions à la taille voulue.

DBCC SHRINKFILE(test_log,100);
GO

et le résultat :

Cette fois-ci la réduction du journal des transactions a bien fonctionné. Sa taille est de maintenant environ 100 Mo (13000 * 8 / 1024).

DBCC LOGINFO(‘test’);
GO

et le résultat :

Le nombre de fichiers virtuels a également diminué.

Voilà j’espère avoir pu vous démontrer de façon concrète pourquoi il arrive que certains fois la réduction de fichiers ne donne pas le résultat escompté à la 1ère tentative !!!

David BARBARIN (Mikedavem)
Elève ingénieur CNAM Lyon