Introduction :
A quoi sert ce fameux LOG et le (ou les fichiers) qui lui sont associé(s) ?
A permettre une reprise sur panne, par exemple en cas d'arrêt inopiné du serveur … en cet été, les orages peuvent tout faire sauter, et les onduleurs arriver au bout de leur capacité par exemple.
Que contient-il ?
Des données sur les images avant modification, après, et autres instructions permettant de 'rejouer' les mises à jour en cas de besoin.
Le principe général de reprise étant :
- Repartir d'une sauvegarde complète
- Faire rejouer le ou les log(s) nécéssaire (s)
Pourquoi le sauvegarder ?
Parce que c'est le point de passage obligé pour vider son contenu (c'est un fichier circulaire)… à défaut le fichier ne fera que grossir (sauf si sa taille a été limitée ) et fera donc 'exploser' le disque dur…
A noter que cette façon de se vider ne concerne que les modes complet (full) et journalisé en bloc (bulk_logged), en mode simple, ce log se vide au checkpoint (environ toutes les minutes)
Voici un bout de code pour remplir un log assez vite :
create
table
ConsommeLog
(Cle
int
identity (1,1)
primary
key,
Valeur
nchar(4000))
go
insert
into
ConsommeLog
(Valeur)
values (REPLICATE('X',4000)),(REPLICATE('Y',4000)),(REPLICATE('Z',4000))
go
declare
@iBoucle
int
set
nocount
on
set
@iBoucle
= 1
while
@iBoucle
< 10000
begin
set
@iBoucle
=
@iBoucle
+ 1
update
ConsommeLog
set
Valeur
=
REPLICATE
('A',4000)
update
ConsommeLog
set
Valeur
=
REPLICATE
('B',4000)
--waitfor delay '00:00:02'
End
Explications : les images avant sont assez grosses (8000 octets environ) et de ce fait, on va consommer pas mal de log.
Voir l'utilisation du LOG :
dbcc
sqlperf
(logspace)
Ou, en mode graphique :
On peut aussi en surveiller l'évolution avec PERFMON :
Je suis ici sous Windows Server 2008...
On voit bien ici l'effet du mode SIMPLE . : régulièrement, le pourcentage d'utilisation repasse à 0.
Surveillons ici le mode COMPLET :
Pourquoi le poucentage diminue-t-il de temps en temps ?
Parce que SQLServer réalloue de l'espace disque pour le fichier , et comme indiqué plus haut, le fichier ne fait donc que grossir jusqu'à son backup (et non au backup de la base !)
Comment réduire la taille de ce fichier ?
Réponse professionnelle :
backup
log
DemoBackupLog
to
disk
=
'c:\DemoBackupLog.bak'
puis
dbcc
shrinkfile
(DemoBackupLog_log,100)
pour le réduire à 100 mégas
Réponse du 'bidouilleur' :
backup
log
DemoBackupLog
with
truncate_only
Simuler un backup pour repartir tranquillement …
Tranquillement … est-ce bien sûr ?
A partir de là vous n'avez plus de sauvegarde qui tienne (sauf la complète précédente) vous avez donc détruit votre propre système de sauvegarde par les logs !
Bien sûr, c'est dans les heures qui suivent que vous aurez besoin de faire un RESTORE ….
Loi de Murphy : toute base sans sauvegarde nécessite un restore dans les heures qui suivent !
Position de Microsoft :
Tentons cela sur SQLServer 2008
backup
log
DemoBackupLog
with
truncate_only
L'option truncate_only n'est plus reconnue sur SQLServer2008 !
Oups ! et comment s'en sortir ?
En repassant la base en mode SIMPLE puis en revenant au mode COMPLET :
alter
database
DemoBackupLog
set
recovery
simple
puis
alter
database
DemoBackupLog
set
recovery
full
Surveillons notre remplissage comme tout à l'heure :
Bizarre, on se croirait en mode simple ?
dbcc
sqlperf(logspace)
Effectivement, le log ne décolle pas des 100 mégas… on est bien en mode simple !
Passons une complète :
backup
database
DemoBackupLog
to
disk
=
'c:\DemoBackupLog.bak'
with
init
Nous sommes bien revenus dans un mode de gestion COMPLET du LOG
Conclusion :
Les stratégies de sauvegardes (et donc du choix du mode de recovery) sont un des points clés du travail du DBA.
Bien réfléchir avant d'agir !
Ce n'est pas parce que le mode COMPLET pose des problèmes d'allocation disque qu'il fait se précipiter vers le mode SIMPLE…
(Je n'aime pas le mode BULK, j'en reparlerai peut être plus tard)
Le changement de mode doit être réalisé avec soin…
Il FAUT des automatismes de gestion des BACKUP LOG et complète : je considère que , sur ce point , les outils standard sont encore insuffisants car un peu simplistes dans la gestion de la volumétrie et des durées de rétention.
C'est pourquoi je mets toujours en place chez mes clients sérieux (et ils le sont sont tous !) des solutions de sauvegardes par scripts pour gérer les différents cas de figure.
Si vous avez ce besoin, n'héistez pas à me faire appel, comme les assurances, c'est toujours avant l'accident que l'on trouve la prime élevée…