Qu'est-ce que le journal de transactions ou (Transaction Log en anglais) ?
Il est souvent confondu avec le fichier LDF de la base de données. En fait, le journal de transactions n'est pas un seul fichier, mais peut être composé de plusieurs fichiers LDF (extension par défaut du fichier). Mais il est vrai que dans l'énorme majorité des cas, seul 1 fichier LDF est présent dans une base de données, et c'est lui seul qui représente le journal (voir ici pour l'utilisation de plusieurs fichiers : http://www.sqlnco.ch/2011/10/quel-est-l-interet-d-avoir-2-fichiers-et-plus-pour-le-journal-de-transactions-tlog-ldf-dans-sql-server/).
Pour connaître le ou les fichiers du journal de transactions dans la base de données courante, utilisez le script suivant.
select
*
from
sys.master_files
where
type
= 1 and database_id =
DB_ID()
Ne pas confondre ces fichiers multiples avec les VLFs (Virtual Log Files) qui contrairement à leur nom ne sont pas des fichiers, mais des blocs qui composent le fichier LDF.
Le journal de transactions contient l'ensemble des transactions exécutées sur la base de données courante. Pour simplifier imaginez que toutes les requêtes réalisant des écritures sont stockées dans ce journal avant même que les données soient inscrites sur le disque. Un certain nombre d'opérations systèmes génère aussi des écritures dans le journal.
La finalité de ce journal est double. Il permet de maintenir les données dans le cache mémoire de SQL Server, le plus longtemps possible sans avoir à les écrire dans les fichiers de données. Mais aussi de permettre la récupération de la base de données en cas d'arrêt non prévu. En effet les données restant longtemps dans le cache, sans journal traçant les opérations d'écritures la base de données serait corrompu en cas de redémarrage du serveur. Il sert donc à améliorer les performances en limitant les écritures sur le disque, mais aussi permet de remettre la base de données dans un état cohérent en cas de redémarrage (et aussi en cas de restauration d'une base de données).
Sans journal, le risque de corruption de la base de données est énorme et le serveur risque de ne pas tolérer le moindre arrêt sans pouvoir vider son cache vers le disque, ce qui est clairement aberrant dans un environnement de production.
Pourquoi le journal de transactions grossi-t-il ?
Toutes les modifications effectuées sur la base de données génèrent des transactions et font donc grossir les fichiers du journal de transactions. Il est donc normal que ce fichier grossisse au fil de l'activité de la base de données.
Seulement sans aucunes actions de la part de l'administrateur de base de données, le journal grossira à l'infinie et cela sans aucuns avantages. En effet périodiquement les données présentes dans le cache sont inscrites sur le disque l'intérêt de la récupération des données est donc nul pour des transactions anciennes. Par contre, en cas de perte des disques où sont situées les données, ces anciennes transactions peuvent se révéler précieuses.
C'est là que rentre en ligne de compte, la sauvegarde du journal de transactions. En effet comme indiqué ci-dessus les informations du journal de transactions anciennes ne servent pas au moteur pour redémarrer le serveur et remonter une base de données, mais peuvent être intéressante en cas de perte de fichier, et par extension en cas de perte disque ou de crash serveur sévère. Sans les transactions seules les sauvegardes complètes sont disponibles, or la restauration à un point fixe dans le temps vous expose à une perte systématique de données.
Donc, utiliser des morceaux du journal de transactions et les combiner avec les sauvegardes complètes, permet de remonter la base de données à n'importe quel point dans le temps y compris le moment exact du crash serveur. De plus, une fois la sauvegarde du journal de transactions réalisée, la portion copié vers le fichier de sauvegarde, n'est plus utile dans le journal et peut être supprimée (à quelques conditions détaillées plus loin).
Sauvegarder régulièrement le journal de transactions, permet de libérer de l'espace à l'intérieur des fichiers LDF. Cela ne changera rien à la taille des fichiers en eux même (attention à ne pas utiliser des options tel que Auto Shrink, voir plus loin), mais permet la réutilisation de l'espace interne des fichiers LDF.
La commande DBCC SQLPERF(LOGSPACE) renvoie des informations sur la taille du journal de transaction et le pourcentage d'occupation de ce dernier. Cela peut se révéler très utile pour savoir où en est l'occupation du ou des fichiers. Attention cette commande requiert des privilèges sysadmin sur l'instance où elle est exécutée.
|
Database name
|
Log Size (MB)
|
Log Space Used (%)
|
Status
|
|
Master
|
1.242188
|
36.79245
|
0
|
|
Tempdb
|
0.4921875
|
75.89286
|
0
|
|
Model
|
0.7421875
|
44.21053
|
0
|
|
Msdb
|
8.179688
|
16.0936
|
0
|
Il est important de créer des fichiers LDF avec une taille correcte dès le départ. L'avantage est multiple, d'une part on évite de tomber sur trop d'opérations d'incrément de fichier (quand le fichier est plein), d'autre part la fréquence de sauvegarde du journal de transactions peut être réduite (passer de toutes les 15 minutes à toutes les 30 minutes par exemple) et finalement, en termes de performance, on diminue la fragmentation interne des fichiers, le traitement des transactions est plus rapide.
On utilise souvent, comme valeur arbitraire de départ, le chiffre de 20%. C'est-à-dire que l'on met 20% de la taille estimée des données, comme taille de départ pour le journal de transactions. Cette valeur est une estimation qui variera grandement avec les différents modes de récupération et l'usage de la base de données. En cas d'écritures intensives, cette valeur peut avoisiner les 30 à 40% facilement. Dans des scénarios de chargement de données de Datawarehouse, y compris avec une base de données en mode de récupération simple, la taille du journal de transactions sera très importante.
Comment diminuer la taille du ou des fichier(s) LDF ?
Le seul moyen est de vider le journal est de sauvegarder ce dernier au travers de la commande suivante.
-- Sauvegarde du journal de transaction de la base de données courante
-- Par convention on donne l'extension de fichier TRN à ce type de sauvegarde
BACKUP
LOG MaBaseDeDonnees
TO
DISK
=
'c:\monrepertoire\monfichier.trn'
A ce moment-là, une partie du journal de transactions est vidé. La plupart du temps, exécuter cette commande régulièrement suffira à maintenir la taille des fichiers du journal de transactions à une taille constante.
Cependant, un certains nombres de situations risques de nécessiter de manuellement réduire la taille du ou des fichier(s) LDF.
Parmi les raisons possibles :
-
Chargement de données, ayant augmenté la taille du journal plus que nécessaire
-
Opérations de maintenance d'index, ayant augmenté la taille du journal plus que nécessaire
-
Pas de sauvegarde du journal de transactions sur un environnement de développement ou de pré-production
Dans tous les cas, cherchez la cause du problème et ne voyez pas le fait de tronquer le journal et de réduire la taille des fichiers comme la solution.
Parmi les solutions possibles :
-
Augmenter la fréquence de sauvegarde du journal de transactions.
-
Manuellement exécuter une sauvegarde du journal lors d'étapes clef de maintenance de base de données ou de chargement.
-
Changer le mode récupération temporairement lors d'étapes clef de maintenance de base de données ou de chargement.
-
Vérifiez qu'un processus, tel que la réplication transactionnelle, le Database Mirroring ne bloquent pas la vidange du journal de transactions.
Une fois la sauvegarde effectuée, il nous reste à réduire la taille du ou des fichiers grâce à la commande DBCC SHRINKFILE. J'insiste bien sur le fait que c'est SHRINKFILE et non SHRINKDATABASE qui est à utiliser. Le second fait réduire la taille de tous les fichiers et pas uniquement le contenu des fichiers du journal de transactions. C'est non seulement inutile et long, mais cela engendre de la fragmentation dans les données, ce qui est un effet de bord inadmissible pour les performances.
De plus, ne passez jamais une base de données en AUTO_SHRINK, faute de quoi vous risquez de graves problèmes de performances, pour toutes les raisons évoqué ci-dessus. Vos fichiers de base de données risquent de passer le temps à jouer au yoyo en termes de taille !
Le SHRINKFILE a de très fortes chances d'échouer à la première exécution. C'est pourquoi je vous conseille ce script.
-- Vide le début du journal
BACKUP
LOG MaBase TO
DISK
=
'C:\...'
-- Tente de convaincre le moteur d'utiliser
-- le début du journal de transactions
CHECKPOINT
-- Si la portition active n'est plus à la fin
-- du journal de transactions, vide cette partie
BACKUP
LOG MaBase TO
DISK
=
'C:\...'
-- Le Shrink est maintenant possible
DBCC SHRINKFILE(2, 10, TRUNCATEONLY)
-- En cas d'échec retentez la liste ci-dessus
Le script force le recyclage interne des portions du journal de transactions. La portion, dite active, du journal de transactions doit se déplacer au début du fichier grâce à la commande CHECKPOINT. La sauvegarde du journal de transactions peut alors vider la fin du fichier, et le SHRINKFILE libérer l'espace libre à la fin de ce dernier.
Au niveau de la syntaxe de SHRINKFILE, le premier argument est le numéro du fichier à réduire (vous l'obtenez avec la requête du début de cet article). Le deuxième argument est la taille cible en Méga Octets. Le 3ème est optionnel dans le cas du journal de transactions, il permet d'indique que l'on souhaite uniquement libérer l'espace disque, sans réarranger le contenu interne du fichier.
Sachez que ce script n'aura pas toujours d'effet, quelques causes possibles :
-
La partie active du journal se trouve actuellement à la fin du fichier du journal de transactions
-
Une sauvegarde du journal de transactions ou de données est actuellement en cours d'exécution
-
Une transaction longue est en cours d'exécution
-
Une réplication transactionnelle existe et les transactions associées n'ont pas encore été envoyées au distributeur
-
Un Database Mirroring est en place sur cette base de données et une ou plusieurs n'ont pas été inscrites dans le journal de transactions du miroir
-
Le Change Data Capture a été mis en place et des transactions n'ont pas encore été traitées par celui-ci.
De plus, jamais la totalité du journal n'est jamais vidé, et donc la taille du fichier n'atteindra jamais 0 octets après un DBCC SHRINKFILE. Attention aussi à ne pas trop diminuer la taille du fichier, Je vous conseille de mettre une taille cible correcte en argument de cette commande pour éviter, à nouveau, des incréments sur les fichiers LDF, qui se révèlent très couteux.
Changer le mode de récupération de la base de données ?
Autre solution, passez votre base de données en mode de récupération simple ou journalisé en bloc. En effet en procédant de cette manière, le volume de transactions enregistré dans le journal diminuera fortement pour certaines opérations.
C'est particulièrement intéressant pour les opérations de maintenance d'index ou de création d'index, les chargements de données (BULK INSERT / SELECT INTO / INSERT avec TF610) et les opérations de traitement de données volumineuses (plus de 8ko binaires ou texte). Celles-ci se retrouvent faiblement journalisées et écrivent beaucoup moins d'information dans le journal de transactions.
Une exception cependant, la maintenance d'index en ligne, qui s'exécute toujours en journalisation complète, quel que soit le mode de récupération de la base de données.
Il existe 3 modes de récupération : simple, journalisé en bloc (BULK LOGGED) et complet… Plus de détails sur ces derniers : http://blogs.codes-sources.com/christian/archive/2010/01/05/sql-server-les-modes-de-r-cup-ration-de-bases-de-donn-es-et-les-mythes-autour-du-mode-simple.aspx
Un autre avantage non négligeable du mode de récupération Simple en plus de la journalisation simple de certaines opérations, est le fait qu'il est tronqué automatiquement. Dès que le moteur réalise un CHECKPOINT, le journal est vidé de sa portion inactive. Plus besoin de réaliser de sauvegarde du journal de transaction dans ce mode.
-- Modifie le mode de récupération de la base de données
-- Dans ce mode les BACKUP LOG ne peuvent se faire
-- Le journal est tronqué automatiquement mais peut quand même grossir
ALTER
DATABASE MaBaseDeDonnees SET RECOVERY SIMPLE
En contrepartie il est impossible de restaurer la base de données autrement que par une sauvegarde compète ou différentielle. On ne peut pas profiter du journal pour récupérer une base de données, avec un RESTORE LOG. Pas de restauration fine dans le temps, pas de restauration jusqu'à la période du crash serveur en cas panne sévère.
Tronquer le journal sans le sauvegarder
Il était possible jusqu'à SQL Server 2008 de tronquer le journal de transactions. C'est-à-dire de le vider sans sauvegarder son contenu. Cette option est assez dangereuse et je ne saurais que la déconseiller, particulièrement sur un environnement de production.
Dans certains cas particulier, elle est néanmoins nécessaire (sur des environnements de développement par exemple). Elle doit être impérativement suivie d'une sauvegarde complète, sinon votre base de données restera implicitement en mode de récupération simple (quel que soit la méthode utilisée ci-dessous).
Sous SQL Server 2000 ou 2005, vous pouvez utiliser les commandes suivantes.
-- Ces 2 méthode ne sont plus supportées
-- depuis SQL Server 2008
BACKUP
LOG MaBase WITH NO_LOG
BACKUP
LOG MaBase WITH TRUNCATE_ONLY
Cependant je vous conseille la méthode suivante qui fonctionne sur toutes les versions du moteur de base de données de 2000 à 2008 R2.
-- Remplace NO_LOG et TRUNCATE_ONLY
-- Passe en mode simple --> réalise un TRUNCATE jusqu'au point de contrôle
ALTER
DATABASE MaBaseDeDonnees SET RECOVERY SIMPLE
-- Repasse en mode complet
ALTER
DATABASE MaBaseDeDonnees SET RECOVERY FULL
Vous pouvez faire de même avec le mode journalisé en bloc, en remplaçant FULL par BULK LOGGED. Après ces commandes, exécutez la sauvegarde complète pour rétablir le mode de récupération. La base de données reste en mode de récupération simple tant que la sauvegarde n'est pas exécutée, même si les paramètres de base de données prétendent le contraire.
-- Impérativement faire une sauvegarde complète après
BACKUP
DATABASE MaBaseDeDonnees TO
DISK
=
'monFichier.BAK'
Une alternative existe pour sauvegarder le journal de transactions sans conserver les fichiers de sauvegarde : http://blogs.codes-sources.com/christian/archive/2008/05/06/sql-server-envoyer-une-sauvegarde-vers-le-p-riph-rique-nul.aspx
Je conseille cette technique sur les serveurs de développement, là où la base de données doit conserver le même mode de récupération qu'en production sans d'embarrasser avec les sauvegardes.
En conclusion
-
Sauvegardez régulièrement le journal de transaction
-
Sinon pensez à passer en mode de récupération simple
-
Ne réduisez la taille que des fichiers LDF, via SHRINKFILE, pour le remettre à une taille initiale correcte
-
N'utilisez jamais de SHRINKDATABASE
-
N'utilisez jamais l'option AUTO_SHRINK sur une base de données
Versions applicables
SQL Server 2000 (avec certaines restrictions sur certains scripts utilisant des vues systèmes ou des DMV)
SQL Server 2005
SQL Server 2008
SQL Server 2008 R2
SQL Server nom de code Denali
Bon shrink…
