Dans la plupart des cas, une base de données comporte un seul fichier journal. Il peut arriver qu’il soit nécessaire de rajouter un ou plusieurs fichiers au journal des transactions à cause d’un manque d’espace libre sur une partition par exemple. Comment se remplit le journal dans ce cas ? Comment s’effectue l’allocation de nouveaux VLF dans plusieurs fichiers ? C’est ce que nous verrons dans ce billet.

Revenons un bref instant sur les VLF (Virtual Log Files). Un VLF est l’unité de travail pour le journal des transactions. Sa taille est déterminée par SQL Server en fonction de la taille totale du journal des transactions et de sa valeur d’incrément. A la création d’une base de données, le nombre de VLF d’un fichier journal est toujours compris entre 2 et 16. Nous pouvons le vérifier en créant une base de données nommée test_jnl avec le script suivant :

USE [master];
GO

IF  EXISTS (SELECT name FROM sys.databases WHERE name = N’test_jnl’)
DROP DATABASE test_jnl;
GO

CREATE DATABASE test_jnl
ON  PRIMARY
( NAME = N’test’, FILENAME = N’E:\MSSQLSERVER\MSSQL10.MSSQLSERVER\MSSQL\DATA\test.mdf’ ,
  SIZE = 10MB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB)
LOG ON
( NAME = N’test_log’, FILENAME = N’F:\MSSQLSERVER\test_log.LDF’ ,
  SIZE = 576KB , FILEGROWTH = 0),
( NAME = N’test_log_2′, FILENAME = N’F:\MSSQLSERVER\test_log_2.ldf’ ,
  SIZE = 5MB , MAXSIZE = 2048GB , FILEGROWTH = 0)
GO

Le script précédent créé une base de données composée d’un fichier de données de 10MB et de deux fichiers journaux. Le premier possède une taille de 576KB et l’accroissement automatique est désactivé. Le deuxième fichier possède une taille de 5MB avec un accroissement automatique également désactivé.

Nous pouvons voir le nombre de VLF créé pour chaque fichier journal à l’aide de la commande DBCC LOGINFO :

DBCC LOGINFO(‘test_jnl’);

qui donne le résultat suivant :

 

Le 1er fichier (FileId=2) est composé de 2 VLF tandis que le 2ème fichier est composé, quant à lui, de 4 VLF. Une colonne intéressante est la colonne status. Celle-ci nous indique si un VLF est libre (valeur 0) ou utilisé (valeur 2).

Créons à présent une table nommée test :

USE test_jnl;

CREATE TABLE test
(
id INT NOT NULL,
texte VARCHAR(50) NOT NULL
);

Effectuons une sauvegarde de la base de données test_jnl pour avoir un point de référence servant aux LSN des transactions du journal. La base de données est en mode de récupération complet.

BACKUP DATABASE test_jnl TO DISK = ‘F:\MSSQLSERVER\BACKUP\TEST_JNL.BAK';
GO

Insérons une première ligne de données dans la table test :

INSERT test VALUES (1, REPLICATE(‘T’, 50));

Le jeu de données suivant pourra être joué plusieurs fois afin de pouvoir observer le remplissage du journal et l’utilisation des VLF :

INSERT test
SELECT * FROM test;

Voici le résultat de la commande DBCC LOG après avoir joué le script d’insertion de données avec un total de 1024 lignes :

Le 2ème VLF du premier fichier est maintenant utilisé (status = 2). Continuons le remplissage de la table en jouant à nouveau le script d’insertion. Voici maintenant le résultat de la commande DBCC LOG après l’insertion de 2048 lignes de données :

Continuons encore à remplir la table test avec notre script d’insertion. Après l’insertion de 8192 lignes de données le message d’erreur suivant apparaît pour indiquer que le journal des transactions est plein.

Msg 9002, Niveau 17, État 2, Ligne 1
Le journal des transactions de la base de données ‘test_jnl’ est plein. Pour savoir pourquoi il est impossible de réutiliser de l’espace dans le journal, consultez la colonne log_reuse_wait_desc dans sys.databases.

Nous avons limité la croissance des 2 fichiers journaux en désactivant leur expansion automatique. Ceci explique pourquoi notre journal des transactions est plein. Regardons l’état des VLF :

On constate que tous les VLF sont utilisés (status=2), ce qui confirme une nouvelle fois le message d’erreur précédent.

On constate après cette série de test que le remplissage du journal s’effectue toujours de manière séquentielle et dans un seul fichier à la fois. SQL Server considère le journal des transactions comme un conteneur unique même lorsque celui-ci est composé de plusieurs fichiers. Un seul VLF est utilisé à la fois. Il en va de même pour les fichiers : le 1er fichier journal est d’abord utilisé. Lorsque celui-ci est rempli, SQL Server passe au 2ème fichier et ainsi de suite si d’autres fichiers existent. Nous n’avons pas d’écritures parallèles. C’est la raison pour laquelle qu’il est inutile de multiplier les fichiers journaux pour des raisons de performances.

Nous avons vu précédemment le cas où la taille de chaque fichier était limitée, sans possibilité d’accroissement. Mais que se passe t’il maintenant lorsque nous autorisons l’expansion automatique de ces fichiers ? Le script suivant initialise le paramètre FILEGROWTH de chaque fichier journal à 10%.

ALTER DATABASE test_jnl
MODIFY FILE (NAME = test_log, FILEGROWTH = 10%);
GO
ALTER DATABASE test_jnl
MODIFY FILE (NAME = test_log_2, FILEGROWTH = 10%);
GO

Continuons à remplir la table test avec la requête suivante :

INSERT test
SELECT TOP 10 * FROM test;

Voici maintenant le résultat de la commande DBCC LOG après l’insertion des 10 lignes de données dans la table test :

Un nouveau VLF a été alloué pour le 1er fichier (FileId=2). Après une autre série d’insertion de données dans la table test, on peut remarquer ceci :

Le VLF précédemment alloué au 1er fichier est utilisé et plein. SQL Server doit donc à nouveau allouer d’autres VLF et il le fait maintenant sur le deuxième fichier. 2 nouveaux VLF sont alloués dans le 2ème fichier.

On continue toujours une série d’insertion de données dans la table test. L’état des VLF est maintenant le suivant :

Les VLF alloués au deuxième fichier ont été utilisés et une nouvelle allocation de VLF est effectuée mais dans le premier fichier.

Vous l’aurez compris, lorsqu’aucune limitation d’expansion de fichier n’est paramétrée, SQL Server alloue et utilise les VLF des fichiers tour à tour. Encore une fois, il n’y a pas d’écriture parallèle dans les fichiers journaux. Chaque fichier est utilisé l’un après l’autre.

Avec la requête suivante on peut voir l’ordre d’utilisation des fichiers et de leur VLF :

USE test_jnl;

IF EXISTS (SELECT * FROM tempdb.sys.objects AS o WHERE o.object_id = OBJECT_ID(‘tempdb.dbo.#t’))
DROP TABLE #t;

CREATE TABLE #t
(
FileId INT,
FileSize BIGINT,
StartOffset INT,
FSeqNo INT,
STATUS TINYINT,
Parity INT,
CreateLSN NUMERIC(38)
);
INSERT INTO #t
EXEC(‘DBCC LOGINFO( »test_jnl »)’);
SELECT
*
FROM #t
ORDER BY FSeqNo;

Le résultat est le suivant :

On retrouve ce que l’on a vu précédemment. Le premier fichier journal (FileId = 2) a été utilisé suivi du 2ème fichier journal. Après avoir autorisé l’auto-expansion des fichiers journaux, un nouveau VLF a été alloué et utilisé pour le premier fichier. Enfin, une nouvelle allocation de VLF a été réalisée pour le second fichier. Vous pouvez faire le tester pour voir si la future allocation s’effectue bien dans le premier fichier journal.

David BARBARIN (Mikedavem)
Elève ingénieur CNAM
MVP SQL Server