SQL Server vu par Christian Robert

SQL Server : Comment forcer le Shrink du journal de transaction?

J'avais déjà abordé ce sujet précédemment :

http://blogs.codes-sources.com/christian/archive/2007/02/12/sql-server-faq-sql-pourquoi-mon-fichier-de-log-ldf-est-il-aussi-gros-comment-diminuer-sa-taille.aspx

Mais je me suis rendu compte au fil du temps que la réduction de la taille du journal de transaction en fonctionnait pas du premier coup.

En regardant de plus près le fonctionnant de celui-ci c'est normal. La figure ci-dessous represente un fichier LDF contenant le journal de transaction d'une base de données SQL Server. Chaque case est appelé VLF (Virtual Log File).

Où :

• X est un VLF rempli
• A est le VLF actif

Un VLF n'est pas un fichier, c'est une zone de stockage dans le ou les fichiers de journaux de transaction d'une base de données (extension généralement LDF), ils sont de taille différentes, même au sein d'un même fichier.

En effet le journal (basiquement le fichier LDF) se rempli de manière séquentiel, c'est donc quand le fichier est rempli la partie finale qui est la portion courante du journal. Or il n'est pas possible d'exécuter de Shrink (opération de réduction du fichier) si justement la portion finale est active.

Alors comment faire ? Il faut « forcer » le moteur à utiliser une autre portion, idéalement située au début du journal. Seulement le fichier est rempli à cet endroit il n'esdt donc pas possible pour SQL Server d'utiliser cette portion.

 

On va donc vider le journal, cette opération se fait grâce à la commande BACKUP LOG :

BACKUP LOG MaBase TO DISK = 'C:\...'

OU de manière moins conseillée, on tronque le journal avec une variante de la même commande :

BACKUP LOG MaBase WITH TRUNCATE_ONLY

Le résultat dans le journal de transaction est le suivant :

 

La portion active est toujours situé à la fin (sauf cas exceptionnel, en effet il est tout à fait possible que la portion active se deplace faute de place et à cause de transactions en cours), mais le début du fichier est maintenant vide et peut être réutilisé.

Il faut donc une méthode pour que le moteur de base de données change la portion active. Cela est possible (à condition qu'aucune transaction ne soit en cours) avec :

CHECKPOINT

Le résultat sera :

Le shrink dans ce cas n'est toujours pas possible, en effet la portion active est au début du fichier, mais la fin est occupé par des informations du journal non sauvegardé ou non tronqué… On repasse une commande de sauvegarde du journal :

BACKUP LOG MaBase TO DISK = 'C:\...'

-- ou --

BACKUP LOG MaBase WITH TRUNCATE_ONLY

 

On obtient :

 

Et cette fois ci il est normalement possible de réaliser l'opération souhaité par :

DBCC SHRINKFILE (2)

Le numéro 2, correspond au journal de transaction (ou du moins le 1^er fichier s'il y en avait plusieurs).

 

Attention le moteur ne supprime jamais tous les VLF du journal de transaction, en fonction de la taille initiale du journal, des incréments de taille de fichier, leur nombre pourra varier, il n'y en aura cependant jamais moins que 4 et vous ne pourrez pas contrôler leur taille à postériori !

Si la succession des opérations précédentes échoue, vous n'aurez qu'à renouveler les opérations à plusieurs reprises.

BACKUP LOG MaBase -- TO DISK = 'C:\...' ou WITH TRUNCATE_ONLY

CHECKPOINT

BACKUP LOG MaBase -- TO DISK = 'C:\...' ou WITH TRUNCATE_ONLY

DBCC SHRINKFILE (2)

-- Si echec on retente...

CHECKPOINT

BACKUP LOG MaBase -- TO DISK = 'C:\...' ou WITH TRUNCATE_ONLY

DBCC SHRINKFILE (2)

 

Cela peut paraitre un peu fastidieux, et limite archaïque, le problème étant qu'il est difficile de prévoir le fait que vous ayez ou non une transaction active qui bloque le recyclage du journal. Si vous n'y arrivez, pas attendez 5 min et réessayer sinon, vérifier les sessions actuelles, et prenez attention à celles exécutant une requête.

Cette méthode fonctionne dans plus de 99% des cas où je l'ai testé et elle est très pratique. Il est cependant possible qu'elle ne fonctionne pas quelques cas rares, hors ceux évoqués plus haut :

• Replication transactionelle bloquant la vidange du journal
• Change Data Capture bloquant la vidange du journal
• Plus d'espace disponible sur le disque où se situe le LDF et/ou MDF (nécessite alors d'ajouter un fichier LDF sur une autre unité)
• Backup Log en cours
• Database Mirrroring en cours de synchronisation

Tous ces cas peuvent bloquer plus ou moins longtemps une portions des VLF du journal, vérifiez bien ce qui est en cours d'execution sur votre base de données et sa configuration !

Après la réduction de la taille du journal, si possible augmentez manuellement la taille de celui-ci.

ALTER DATABASE MaBase MODIFY FILE (name = 'nom logique', SIZE = 2GB)

Cela évitera de fragmenter inutilement le journal de transaction et le moteur de base de données ne s'en trouvera que plus efficace pour toutes écriture et transactions à effectuer dans la base de données.

Bonne réduction…