Gestion des logs SQL Server

 

SQL Server et les logs ! Un vaste sujet, déjà traité de nombreuses fois mais on me pose souvent les questions suivantes :

• Ma base de données est en FULL Recovery Mode, j’effectue des FULL Backup mais mon fichier de Log grossit
• Ma base de données est en BULK Recovery Mode, j’effectue des Bulk opérations mais mon fichier de Log grossit
• Ma base de données est en SIMPLE Recovery Mode mais mon fichier de Log grossit
• Un Shrink de mon fichier de log n’a aucun impact sur la taille de celui-ci

C’est à travers ces différentes questions, de différents niveaux techniques que je vais orienter mon article. Et parce qu’une démonstration vaut mieux qu’une théorie, je vous présenterai quelques exemples. Ainsi j’espère qu’à la fin, vous pourrez vous-même comprendre et résoudre ces problèmes (si vous ne savez pas déjà le faire ;-)).

 

Transaction dans SQL Server

Une transaction est une unité de travail : Wiki.

Lorsqu’une transaction s’achève, les modifications apportées sont validées et intégrées de façon permanente à la base de données. Si une transaction rencontre des erreurs lors de son exécution, les modifications seront supprimées (Rollback).

Il existe différents modes de transaction disponibles dans SQL Server : Technet.

Lors de la création d’une base de données, un fichier de transaction est automatiquement créé : .ldf.

En fonctionnement normal, SQL Server écrit séquentiellement dans le journal de transaction et enregistre les détails des opérations DDL et DML sur la base de données à laquelle le journal est associé. Chaque enregistrement possède un identifiant, son numéro de séquence (Log Sequence Number : LSN).

 

Architecture du journal de transaction

En interne, SQL Server divise le journal des transactions en plusieurs sections appelées VLFs (Virtual Log Files). Les VLFs peuvent être actives ou inactives selon qu’elles contiennent ou non une partie des logs actifs. Chaque log relatif à une transaction ouverte est nécessaire pour une éventuelle restauration et doit donc faire partie des Logs actifs. Il existe d’autres activités dans la base de données, comme la Réplication, le Mirroring et le CDC (Change Data Capture) qui nécessite que les logs restent actifs jusqu’à ce qu’ils aient été traités.

Le MinLSN est le LSN de l’enregistrement du journal relatif à la plus ancienne transaction encore active.

Un enregistrement du journal ne fait plus partie du log actif s’il est plus ancien (LSN inférieur) que l’enregistrement MinLSN, qu’il concerne une transaction déjà traitée (Commited) et qu’il n’est pas nécessaire à une restauration (mode BULK ou FULL).

Réduire (SHRINK) le journal de transaction signifie marquer les VLFs comme étant disponible pour la réutilisation, c’est donc le fait de rendre ses enregistrements inactifs.

Lors de l’initialisation du fichier de transaction, une taille lui est affectée (1 Mo par défaut) ainsi qu’un pas de grossissement (10 % par défaut).

SQL Server décide de la taille et du nombre de VLFs en fonction de la taille initiale du journal de transaction. Il ajoutera des VLF supplémentaires lorsque le journal grossira.

Vous êtes maintenant capable de répondre à la question, pourquoi le journal de log grossit ?

S’il grossit, c’est que les VLFs présentes dans le journal de logs sont actives (donc pas réutilisable) alors que de nouvelles opérations ont besoin d’être stockées.

Parce que la gestion des logs est spécifique a chaque mode de restauration dans, analysons mode par mode le journal de transaction.

 

Full Recovery Mode

Dans le modèle de récupération FULL, SQL Server enregistre toutes les opérations permettant de restaurer la base de données a un moment T. Rentrons dans le vif du sujet en créant une nouvelle base de données :

CREATE DATABASE [MaBaseDeTest] ON ( 
	NAME = N'MaBaseDeTest_data'
	, FILENAME = N'C:\MaBaseDeTest.mdf'
	, SIZE = 5000KB
	, FILEGROWTH = 1024KB 
) LOG ON ( 
	NAME = N'MaBaseDeTest_log'
	, FILENAME = N'C:\MaBaseDeTest.ldf'
	, SIZE = 1024KB
	, FILEGROWTH = 10%
);

La commande « DBCC SQLPERF(LOGSPACE) » permet de connaitre la taille des logs et leurs pourcentages d’utilisation :

La commande « DBCC LogInfo » retourne une ligne par VLF présent dans le fichier de log, la colonne statut indique leurs états, 2 si actif et 0 si inactif :

Effectuons maintenant un Backup FULL, insérons des lignes dans une table et analysons de nouveau les VLFs :

USE [MaBaseDeTest]; 
GO 
BACKUP DATABASE [MaBaseDeTest] 
TO DISK ='C:\MaBaseDeTest.bak' 
WITH INIT; 
GO
IF OBJECT_ID('dbo.MaTableDeTest', 'U') IS NOT NULL
    DROP TABLE dbo.MaTableDeTest; 
SELECT TOP 100000	-- N row
	SomeID = IDENTITY(INT,1,1)
	, SomeInt = ABS(CHECKSUM(NEWID())) % 50000 + 1
	, SomeLetters2 = CHAR(ABS(CHECKSUM(NEWID())) % 26 + 65) + CHAR(ABS(CHECKSUM(NEWID())) % 26 + 65)
	, SomeMoney = CAST(ABS(CHECKSUM(NEWID())) % 10000 / 100.0 AS MONEY)
	, SomeDate = CAST(RAND(CHECKSUM(NEWID())) * 3653.0 + 36524.0 AS DATETIME) 
	, SomeHex12 = RIGHT(NEWID(), 12)
INTO    dbo.MaTableDeTest
FROM    sys.all_columns ac1
        CROSS JOIN sys.all_columns ac2;

Requête T-SQL créée par Jeff Moden permettant de créé et remplir une table de N lignes.

Les logs ont grossi ainsi que le nombre de VLFs :

• Quel effet sur le statut des VLFs aura un Backup Full ? Aucun 🙂
• Quel effet sur le statut des VLFs aura un Shrink du fichier de log ? Aucun 🙂
• Quel effet sur le statut des VLFs aura un Backup de log ? Voyez-vous même :

La taille du fichier de transaction a un peu diminué (VLF contenant des transactions dont le LSN est supérieur au MaxLSN et étant inactive) mais sont pourcentage d’utilisation a quand a lui baisser :

 

 

Si nous insérons des données, les nouvelles transactions vont écraser les premières VLF (First In, First Out)

 

Pour diminuer la taille du fichier après avoir effectué un backup de transaction il faut effectuer un Shrink. Il est fortement déconseillé de planifier un Shrink dans un plan de maintenance, une configuration des backups afin de renouveler les VLF est plus que conseillée 😉

DBCC SHRINKFILE (N'MaBaseDeTest_log' , 0, TRUNCATEONLY)

Il est possible qu’après un backup de logs et un Shrink la taille du fichier de log ait peu diminué. Cela est dû a des transactions activent dans les dernières VLF :

La commande COMMIT TRAN et un CHECKPOINT manuels force SQL Server a analyser le cache et écrire sur le disque les pages modifiées dans la mémoire. Un Backup des transactions et une re-exécution de la commande de Shrink diminueront vraiment la taille du fichier de transaction.

Remarque : Lorsque l’option COPY_ONLY est utilisée pour la sauvegarde du journal de transactions, les VLFs ne sont pas inactivés.

 

 

Bulk Recovery Mode

Quand une base de données est en BULK_LOGGED Recovery Mode, les transactions telles que les reconstructions d’index, les Bulk opération sont minimalement enregistrées ce qui permet de gagner de l’espace dans le journal de transaction. Il ne permet pas un Restore de la base de données à un instant donné contrairement au modèle FULL. Une fausse idée est de penser que le journal de log ne grossit pas en Bulk, nous allons démontrer le contraire.

Vous trouverez sur le site suivant les opérations pouvant être minimalement loggées : http://msdn.microsoft.com/en-us/library/ms191244.aspx

La commande suivante change la base de données [MaBaseDeTest] en Bulk Recovery Mode et affiche les causes pouvant retarder la diminution du journal de transaction : sys.databases

ALTER DATABASE [MaBaseDeTest] SET RECOVERY BULK_LOGGED;
GO
SELECT
    name
   , recovery_model_desc
   , log_reuse_wait_desc
FROM
    sys.databases
WHERE
    name = 'MaBaseDeTest'

L’opération SELECT … INTO en mode Bulk minimise les écritures dans le journal de transaction. Après exécution de la requête d’insertion précédente, voici les résultats :

La taille du fichier de log fait actuellement 4,5 Mb contrairement au 6,74 Mb en mode Full.

 

Simple Recovery Mode

Le modèle de récupération SIMPLE, contrairement à ce que l’on pourrait penser enregistre toutes les transactions mais limite les enregistrements des opérations de types Bulk. En fait, le niveau de journalisation est le même que celui appliqué en Bulk Recovery !

Tout enregistrement du journal étant plus récent (supérieur) au MinLSN restera dans le journal actif. La principale différence avec les autres modes de restauration est la désactivation automatique des transactions, après un Checkpoint les VLFs inactives sont automatiquement réutilisables.

Remarque : toutes les bases de données n’ayant jamais été sauvegardées (FULL backup) sont aussi en mode « autotruncate ».

Le but du journal de transaction en mode simple est de garantir la cohérence et l’intégrité des données. Le journal des transactions joue donc encore un rôle vital dans le fonctionnement de la base et il est nécessaire de bien dimensionner la taille du fichier de logs. En fonction de la nature et de la fréquence des transactions, la taille du fichier de log peut augmenter.

Chez un client, une base de données était en mode de récupération simple et pourtant la taille du fichier de transaction grossissait, la solution a été de bien redimensionner correctement la taille du fichier et d’effectuer des Checkpoints plus réguliers.

 

Conclusion

À titre de conclusion, il est IMPORTANT de bien dimensionner la taille du journal de transaction et de suivre leurs évolutions pour plusieurs raisons :

• Le nombre et la taille des VLFs (sqlskills)
• Les fichiers de logs ne peuvent pas être automatiquement initialisés contrairement au fichier de données (sqlskills). Lors de l’allocation d’espace le fichier est rempli de zéro ce qui dégrade les performances

Pour résumer, une croissance du journal des transactions est due à un nombre de transactions élevé et/ou à des facteurs empêchant l’espace du journal de transaction d’être réutilisé 😉

 

Bonus

La DMV sys.dm_tran_database_transactions fournit des informations concernant l’activité des transactions.

Les fonctions fn_dblog et fn_dump_dblog permettent d’interroger le contenu du fichier de log :

SELECT
    Operation
   ,Context
   ,AllocUnitName
   ,Description
   ,[Log Record Length]
   ,[Log Record]
FROM
    fn_dblog(NULL, NULL)

La commande suivante affiche les transactions en cours :

DBCC OPENTRAN (MaBaseDeTest);

 
Il n’est pas recommandé d’effectuer des Shrinks sur les fichiers de données, cela engendre de la fragmentation et implique une re-indexation.