How-To: réduire la taille du journal de transactions sur disque

Lundi, juillet 11, 2011
By David Baffaleuf in SQL Server (dbaffaleuf@capdata-osmozium.com) [71 article(s)]

Deuxième how-to vidéo sur SQL Server, sur le sujet N°1 remonté dans les forums d’entraide SQL Server: comment réduire la taille d’un journal de transactions sur disque.

Démo:

Get the Flash Player to see this player.

Pourquoi il explose:

Avant de vouloir le réduire, il faut comprendre pourquoi on en est arrivé là. A l’origine de ce problème, une option par défaut de la base de données qui s’appelle RECOVERY. Elle permet d’indiquer deux choses:

  • Quels types de sauvegarde il sera possible d’effectuer sur la base.
  • De quelle manière on recycle les transactions validées dans le journal de transactions.

Cette option a trois valeurs possibles:

  • FULL:toutes les transactions attendent d’être sauvegardées pour être purgées du fichier LDF. Il n’y a qu’une sauvegarde de transactions qui puisse les purger. Full est le mode par défaut pour toute nouvelle base utilisateur.
  • SIMPLE: les transactions validées sont purgées du journal à intervalles réguliers par un processus d’arrière plan, le CHECKPOINT. Cette option correspond à l’ancien mode ‘trunc log on checkpoint’ qui existait en 7.0.
  • BULK_LOGGED: permet de minimiser la journalisation de certains ordres de modification massive de données, tels que les reconstructions d’indexes par exemple.

Pour plus de détails sur ces différents modes, voir les épisodes 1 et 2 sur les modes de récupération.

On peut lire cette option en utilisant la commande suivante:

select DATABASEPROPERTYEX('CAPDATA','RECOVERY')
GO

------------
FULL

FULL est la valeur utilisée par défaut lors de la création d’une base. Ce qui signifie que si on ne fait jamais de sauvegardes de transactions sur une base dans ce mode, les transactions ne seront jamais purgées du journal et au bout de 6 mois ou d’un an on arrive à saturer l’espace disque, d’où le besoin de réduire la taille de ce fichier. Il existe une technique pour le ramener à sa taille initiale en moins de 2 minutes, comme expliqué dans le paragraphe suivant et dans la démo vidéo ci-dessus. Pour savoir si un backup transactionnel a déjà été exécuté sur une base, on peut interroger la table backupset de la base msdb:

select backup_start_date, backup_finish_date, type, recovery_model
from msdb.dbo.backupset where database_name = 'CAPDATA' and type = 'L'

backup_start_date              backup_finish_date         type          recovery_model
--------------------------     -------------------------- ------------- -------------------
2010-09-07 18:54:42.000           2010-09-07 18:54:52.000            L           FULL

Comment le réduire:

En deux étapes:

  • Etape 1: on vide le journal de son contenu, ce qui revient à recycler simplement les transactions à l’intérieur de l’enveloppe, sans la réduire.
  • Etape 2: on réduit l’enveloppe sur disque: une fois le journal vidé, il peut être réduit avec la commande DBCC SHRINKFILE ().

Si vous n’avez jamais fait de sauvegarde de journal, il y a des chances pour que la taille du fichier LDF soit assez conséquente. S’il s’agit de plusieurs dizaines ou centaines de Gb, et que vous faites des sauvegardes complètes tous les jours par exemple, ça ne sert à rien de sauvegarder toutes ces transactions qui sont entérinées depuis belle lurette. Si on regarde le contenu du journal de transactions avant de faire la manoeuvre;

dbcc sqlperf(logspace)

Database Name       Log Size (Mb)       Log Space Used (%)      Status
------------------  ------------------  ---------------------   ---------
CAPDATA               25889,367                91,7766              0

La technique consiste à passer la base en mode SIMPLE:

ALTER DATABASE [CAPDATA] SET RECOVERY SIMPLE
GO

Un checkpoint est exécutée automatiquement dès la fin de la commande. Toutes les transactions validées sont purgées du journal . Techniquement elles ne sont pas effacées mais leur conteneur physique  est réinitialisé et peut être réutilisé:

dbcc sqlperf(logspace)
Database Name       Log Size (Mb)       Log Space Used (%)      Status
------------------  ------------------  ---------------------   ---------
CAPDATA               25889,367                1,192919              0

Il ne reste plus qu’à réduire le fichier journal de transactions avec un DBCC SHRINKFILE. A cette étape il faut décider quelle est la nouvelle taille à donner au journal de transactions. Si on le réduit à son maximum, il risque de grandir à nouveau pour atteindre sa taille de croisière, et si on souhaite de réagrandir ensuite, la portion allouée sera zéro-initialisée [1]. Idéalement il faudrait un historique d’utilisation de l’espace dans le journal pour savoir combien d’espace consomme la plus grosse transaction. Par exemple dans notre cas, 50Mb:

select name from CAPDATA.sys.database_files where Type_DESC='LOG'
name
---------------
GESTIMMO_log01

dbcc shrinkfile('GESTIMMO_log01',50)

DbId   FileId      CurrentSize MinimumSize UsedPages   EstimatedPages
------ ----------- ----------- ----------- ----------- --------------
5      2           6400        6400        6400        6400

6400 pages de 8192 octets = 50Mb, on a réussi à réduire l’enveloppe sur disque de 25Gb à 50Mb en quelques secondes.

Quoi faire ensuite:

Il faut décider de la stratégie de backup à adopter pour cette base: quelle quantité de données est-il acceptable de perdre: 1 heure, 4 heures, 1 journée, 1 semaine ?

  • Si la réponse est inférieure à la fréquence des backups complets, alors il faut repasser la base en mode FULL, relancer un backup complet pour réinitialiser la chaîne de sauvegarde, puis planifier des backups de journaux via l’agent ou un plan de maintenance par exemple. Les backups de journaux permettent de garantir une restauration à tout point dans le temps en mode de récupération FULL.
  • Si la réponse est au moins égale à la fréquence des backups complets, alors laisser la base en mode SIMPLE, et l’espace sera automatiquement recyclé.

A suivre un autre How-To sur les problèmes d’administration de base de SQL Server.

A+ David B.

Notes:

[1]: l’initialisation instantanée (IFI) ne s’applique qu’aux fichiers de données MDF et NDF, pas aux journaux de transactions.

Continuez votre lecture sur le blog :




Cliquer pour partager cet article sur Viadeo
Cliquer sur "CAPTURER" pour sauvegarder cet article dans Evernote Clip to Evernote

Tags: ,

4 Responses to “How-To: réduire la taille du journal de transactions sur disque”

  1. Rostand Lekama

    Bonjour,
    Très bon article, par contre j’ai un probleme, j’ai mis en place une procédure sql qui me reduit les logs sur une instance, mais ayant plusieurs instance, je voudrais savoir comment d’une instance A je peut reduire les logs sur l’instance B.

    Je trouve pas la syntaxe de Alter DATABASE pour aller sur l’instance B. Peu t’on vraiment le faire?

    #17801
  2. Dans la mesure où il faut être dans le contexte de la base que l’on souhaite réduire, on ne peut pas le faire depuis une instance distante. Il faut mettre en place un job sur chaque instance.

    Merci pour ton commentaire.

    #17802
  3. zerjad

    Bonjour,

    Merci pour cet article.

    Mon fichier log à atteint 15 Go. j’ai suivi donc à la lettre l’article et voici ce que j’obtiens :

    dbcc sqlperf(logspace)

    Ma_base 15849,99 1,551583 0

    le log utilise uniquement 1,55% de son enveloppe, on pourra alors le réduire.

    dbcc shrinkfile(‘Ma_base_Log’,200)

    5 2 2028800 63 2028800 56

    je refait un dbcc sqlperf(logspace)

    Ma_base 15849,99 0,1037252 0

    Mon fichier log reste à 15 Go !!!!

    Y a t-il une explication à cela ?

    Merci

    #21593
  4. Hello zerjad,

    SQL Server tente de réduire la taille du fichier jusqu’au dernier extent alloué. Il y a certainement un enregistrement de journal à la fin qui empêche la réduction. Si ta base est en mode SIMPLE, lance un checkpoint manuel et regarde le contenu du journal:

    use Ma_Base
    GO
    checkpoint
    GO
    select * from fn_dblog(null,null)
    GO

    S’il n’y a plus que deux opérateurs LOP_BEGIN_CKPT et LOP_END_CKPT, alors retente un shrinkfile. S’il y a d’autres opérateurs, ça veut dire qu’il y a en permanence de l’activité et donc des enregistrements qui viennent se placer en fin de journal et qui bloquent la troncature.

    Idéalement il faut lancer le checkpoint et le shrinkfile l’un après l’autre dans la même commande pour ne pas laisser le temps d’autres transactions de continuer à logger en fin de journal :

    use Ma_Base
    GO
    checkpoint
    GO
    dbcc shrinkfile(‘ma_Base’,200)
    GO

    #21782

Leave a Reply