L’expansion non contrôlée du fichier de log d’une base de données SQL Server est un sujet récurent dans les forums de discussion.
Cet article a pour objectif d’expliquer ce qu’est le fichier de log inhérent au fonctionnement de SQL Server, et les règles de base du paramétrage dont il doit faire l’objet.
Les éléments que je présente ici sont présentés dans l’aide en ligne (voir notamment la page Journal des transactions). Cet article n’a pas vocation à être une redite de cette page, mais simplement une brève présentation du journal, axée sur le contrôle de la taille des fichiers.
Le premier élément à comprendre est le rôle central de ce fichier dans le fonctionnement de SQL Server. Toute opération de modification de données (ajout / suppression / modification) est encadrée par une transaction, et il en est de même pour les modifications de structure. Si aucune transaction n’est explicitement ouverte, une transaction implicite est crée. Cette transaction représente une unité atomique (non sécable) avant et après laquelle les données concernées sont réputées dans un état cohérent et durable.
Tout au long de l’opération, les données sont modifiées au fur et à mesure du traitement. Mais ces modifications ne sont en fait pas réalisées directement sur les fichiers de données. Elles sont en fait réalisées sur des pages (8Ko) de données stockées en mémoire, et elles sont stockées au niveau du support fichier uniquement (dans un premier temps) dans le fichier de journal de transactions. Les informations stockées dans le journal sont suffisantes pour faire et défaire la modification.
Suivant divers déclencheurs (saturation mémoire ou fichier de log, opération de backup ou checkpoint manuel, …), les versions modifiées des pages sont transférées vers les fichiers de données. Les données du journal de transaction permettent notamment de faire et défaire les modifications en cas d’annulation de transaction (Rollback). Notamment, en cas d’arrêt inopiné du système, ces informations présentes sur le disque dur (et donc réputées durables), les informations présentes dans le journal de transaction sont utilisées pour repositionner la base dans un état cohérent.
Dès lors que le Checkpoint a eu lieu, toute les informations contenues dans le journal de transaction et concernant des transactions terminées deviennent inutiles pour cette fonctionnalité de récupération en cas de crash.
Toutefois, elle peuvent rester utiles pour de nombreuses autre fonctionnalités :
- jeu des mêmes modifications sur une autre instance SQL Server, par exemple à travers du Log Shipping ou du Database Mirroring
- restauration d’une base en mode « Point In Time », c’est-à-dire en se repositionnant dans l’état dans lequel se trouvaient les données à une date-heure donnée.
Pour cela, il peut être nécessaire de conserver les données du journal de transactions y compris lorsque les transactions sont terminées et qu’un checkpoint a eu lieu ensuite. La base est dans ce cas configurée en mode de récupération « complet ».
A l’opposé, si l’on décide de s’affranchir des informations fournies pas une historisation des transactions, alors la base est à configurer en mode « simple », et l’espace disque du journal des transactions est de nouveau disponible dès lors que les transactions sont terminées et que le checkpoint a eu lieu.
Dans la pratique, on trouve très souvent des bases de données configurées en mode complet soit de manière non justifiée (pas de besoin de restauration à une date-heure donnée, pas de base miroir, …) ou non suivie (pas de sauvegarde du journal).
Il est en effet important de noter qu’à partir du moment où une base est en mode complet, son journal de transaction mémorise l’ensemble des modifications apportées, et grossit au besoin (suivant les paramètres configurés). L’espace occupé par des transactions révolues n’est libéré que lorsque les informations qu’il contient sont réputées stockées en sécurité ailleurs, c’est-à-dire dans une sauvegarde.
Commençons par créer une base dédiée, dans laquelle nous effectuerons quelques modifications…
-- Initialisation
SET NOCOUNT ON
GO
CREATE DATABASE [Taille Log]
GO
BACKUP DATABASE [Taille Log] TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\Taille Log0.bak' WITH NOFORMAT, INIT, NAME = N'Backup initial', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
-- Mise en place de la table, et remplissage
use [Taille Log]
GO
CREATE TABLE A(Id int IDENTITY, Chaine char(1000))
GO
INSERT INTO A(Chaine) values ('Test')
GO 100000
-- Suppression de la table
DROP TABLE A
GO
Accessoirement, on peut même effectuer un petit Checkpoint histoire de s’assurer que toutes les opérations « en standby » en mémoire sont bien appliquées au fichier de données.
-- Checkpoint pour "stabiliser" CHECKPOINT GO
Dans l’état actuel des choses, on n’a plus aucune donnée applicatives, la seule table créée a même été supprimée.
Et pourtant, on a pas mal de place consommée par le fichier de log…
-- Taille du journal occupée SELECT 'Taille du journal occupée',s.name AS [Name],s.size * CONVERT(float,8) AS [Size],CAST(FILEPROPERTY(s.name, 'SpaceUsed') AS float)* CONVERT(float,8) AS [UsedSpace] FROM sys.master_files AS s WHERE (s.type = 0 and s.database_id = db_id()) GO
Cette place ne sera libérée que lors d’un backup du journal de transaction.
-- Sauvegarde du journal BACKUP LOG [Taille Log] TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\Taille Log.trn' WITH NOFORMAT, INIT, NAME = N'Taille Log-Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10 GO -- Taille du journal occupée après backup du journal SELECT 'après backup du journal',s.name AS [Name],s.size * CONVERT(float,8) AS [Size],CAST(FILEPROPERTY(s.name, 'SpaceUsed') AS float)* CONVERT(float,8) AS [UsedSpace] FROM sys.master_files AS s WHERE (s.type = 0 and s.database_id = db_id())
Maintenant, il est à noter que le même scénario réalisé avec un mode Recovery Simple n’aurait pas vu le journal grossir à ce point, bien loin de là…
-- Initialisation
SET NOCOUNT ON
GO
CREATE DATABASE [Taille Log2]
GO
-- Mise en place du mode Recovery Simple
USE [master]
GO
ALTER DATABASE [Taille Log2] SET RECOVERY SIMPLE WITH NO_WAIT
GO
-- Mise en place de la table, et remplissage
use [Taille Log2]
GO
CREATE TABLE A(Id int IDENTITY, Chaine char(1000))
GO
INSERT INTO A(Chaine) values ('Test')
GO 100000
-- Suppression de la table
DROP TABLE A
GO
-- Checkpoint pour "stabiliser"
CHECKPOINT
GO
Après avoir laissé quelques secondes au système pour se stabiliser, on constate que l’espace occupé dans le fichier de log est minime…
-- Taille du journal occupée SELECT 'Taille du journal occupée',s.name AS [Name],s.size * CONVERT(float,8) AS [Size],CAST(FILEPROPERTY(s.name, 'SpaceUsed') AS float)* CONVERT(float,8) AS [UsedSpace] FROM sys.master_files AS s WHERE (s.type = 0 and s.database_id = db_id()) GO
Au final, il est important de retenir que le mode Recovery complet ne doit être utilisé que s’il y a un besoin particulier (et en général pas dans le contexte d’environnements de développement). De plus, si une base en mode complet subit de nombreuses modifications entre deux sauvegardes complètes, il convient d’adjoindre une planification de sauvegardes de journaux de transactions entre deux sauvegardes complètes, afin de ne pas voir s’envoler la taille du journal de transaction…