Modes de récupération et journal de transactions, épisode 1

Une petite série de posts sur les différents modes de récupération des bases sous SQL Server 2005/2008, et leur impact sur le journal de transactions.

Dans ce premier épisode:

1. Qu’implique le passage de SIMPLE à COMPLET.
2. Que devient mon journal en mode COMPLET si je ne le sauvegarde pas régulièrement.

Le mode de récupération (recovery model en anglais) est une propriété de base de données qui va préciser pour celle-ci deux choses essentielles:

1. Le recyclage de l’espace consommé par les transactions validées dans son journal de transactions.
2. La possibilité ou non de pouvoir sauvegarder ces transactions validées.

Il existe trois modes de récupération: FULL, SIMPLE et BULK-LOGGED.

FULL: c’est le mode par défaut. Dans ce mode, toutes les transactions sont journalisées, même les opérations de modifications de masse (CREATE INDEX, BULK INSERT ou bcp.exe, SELECT INTO, et les opérations sur les champs TEXT). Dès l’instant où il y a eu au moins une sauvegarde complète sur la base, les transactions validées restent dans le journal. La seule façon de recycler l’espace occupé par ces transactions est de lancer régulièrement une sauvegarde du journal. Les transactions validées sont sauvegardées dans un fichier et sont alors supprimées du journal * .

SIMPLE: dans ce mode, le recyclage des transactions validées est la responsabilité du checkpoint. En plus de flusher sur disque les pages de données modifiées dans le cache de données, le checkpoint supprime les transactions validées du journal sans les sauvegarder. Conséquence de celà, je n’ai pas la possibilité de sauvegarder mes transactions en mode SIMPLE. Je ne pourrai exécuter que des sauvegardes complètes ou différentielles (ou de fichier en lecture seule).

BULK-LOGGED: c’est un mode à combiner avec le mode FULL. Dans ce mode, les opérations de modification de masse (citées plus haut) ne sont pas complètement journalisées, et l’impact de telles opérations sur le journal est limité. Le fait de changer de FULL à BULK-LOGGED ne modifie en rien ma façon de sauvegarder mes bases. En mode BULK-LOGGED, mes sauvegardes transactionnelles horaires continuent de s’exécuter sans problème. Très utile lorsqu’on lance notre réorganisation des indexes hebdomadaire car on gagne un temps fou.

*: il existe au moins deux exceptions à cette règle: si la base est principale dans une session de miroir synchrone et que le site miroir est injoignable, où si la base est publiée dans le cadre d’une réplication transactionnelle et que la distribution ou l’agent de lecture du journal sont injoignables / inactifs. Dans ces deux cas, les transactions en attente (non envoyées au miroir, non écrites dans la base distribution) restent dans le journal et ne sont pas vidées par les sauvegardes de transactions.

Note: Attention à ne pas confondre mode de récupération FULL et sauvegarde FULL.

Pour retrouver le mode de récupération d’une base:

SELECT DATABASEPROPERTYEX('maBase','RECOVERY') as RECOVERYMODE

Pour changer le mode d’une base:

ALTER DATABASE maBase SET RECOVERY [ FULL | SIMPLE | BULK_LOGGED ]

1) Première remarque: il faudra obligatoirement (re)lancer une sauvegarde complète avant de sauvegarder le journal:
– Quand je créé ma base.
– Quand je passe de SIMPLE à FULL.

La sauvegarde de transaction a besoin d’un numéro de transaction (LSN pour Log Sequence Number) de référence pour savoir à partir de quelle transaction commencer sa sauvegarde. Lorsqu’il n’y a pas eu d’autre sauvegarde de transactions avant elle, comme c’est le cas quand je passe de SIMPLE à FULL ou quand je viens de créer ma base, elle se base sur le LSN de fin de la dernière sauvegarde complète dans le même mode. Le fonctionnement précis de la sauvegarde à chaud fera l’objet d’un autre article prochainement.

Pour le mettre en évidence, créons-nous une petite base CRASHTEST:

CREATE DATABASE CRASHTEST

on PRIMARY

(NAME='CRASHTEST_data',

FILENAME='F:\KATMAI\MSSQL10.KATMAI\MSSQL\DATA\CRASHTEST_data.mdf',

SIZE=100MB, MAXSIZE=500MB)

LOG ON

(NAME='CRASHTEST_log',

FILENAME='F:\KATMAI\MSSQL10.KATMAI\MSSQL\DATA\CRASHTEST_log.ldf',

SIZE=50MB,
MAXSIZE=500MB)

Vérifions son mode de récupération:

SELECT DATABASEPROPERTYEX('CRASHTEST','RECOVERY') as RECOVERYMODE

RECOVERYMODE
---------------------------------------------------------
FULL

Créons une petite transaction et essayons de la sauvegarder:

use CRASHTEST

create table TABLE1 (A numeric identity, B varchar(5000))

insert into TABLE1 values (replicate('b',5000))

backup log CRASHTEST to disk='C:\CRASHTEST.log1.trn' with init, stats

Msg 4214, Level 16, State 1, Line 1

BACKUP LOG cannot be performed because there is no current database backup.

J’ai donc effectivement besoin d’avoir une sauvegarde complète pour initialiser ma chaîne:

backup database CRASHTEST to disk='C:\CRASHTEST.1.bak' with init, stats

Processed 152 pages for database 'CRASHTEST', file 'CRASHTEST_data' on file 1.
100 percent processed.
Processed 1 pages for database 'CRASHTEST', file 'CRASHTEST_log' on file 1.
BACKUP DATABASE successfully processed 153 pages in 0.266 seconds (4.711 MB/sec).

OK, je retente de sauvegarder mon log:

backup log CRASHTEST to disk='C:\CRASHTEST.log1.trn' with init, stats

100 percent processed.

Processed 6 pages for database 'CRASHTEST', file 'CRASHTEST_log' on file 1.

BACKUP LOG successfully processed 6 pages in 0.161 seconds (0.283 MB/sec).

Même problème lorsque je passe de SIMPLE à FULL:

ALTER DATABASE CRASHTEST set RECOVERY SIMPLE

ALTER DATABASE CRASHTEST set RECOVERY FULL

backup log CRASHTEST to disk='C:\CRASHTEST.log2.trn' with init, stats

Msg 4214, Level 16, State 1, Line 1
BACKUP LOG cannot be performed because there is no current database backup.

2) SAUVEGARDEZ VOS TRANSACTIONS !

C’est un problème que l’on retrouve chez beaucoup de clients: grands comptes, PME… Il y a toujours chez eux au moins une base de données en mode de récupération FULL dont le journal n’a JAMAIS été sauvegardé. Ce n’est pas étonnant en un sens puisque FULL est le mode par défaut pour toutes les bases créées par l’utilisateur. Le résultat est un fichier LDF qui fait 80 Gb parce qu’il contient une année ou deux de transactions validées, sauvegardées dans des backup FULL depuis belle lurette, mais on le répète, en mode FULL, il n’y a qu’une sauvegarde de journal qui permet de recycler l’espace occupé par des transactions validées.

Reprenons notre base CRASHTEST, et créons un peu de volumétrie…

USE CRASHTEST

declare @cpt int

set @cpt=1

while(@cpt<=50000)

begin

insert into TABLE1 values (replicate('b',5000))

set @cpt=@cpt+1

end

go

…et vérifions quelle est la situation du journal à l’issue:

DBCC SQLPERF(LOGSPACE)

Database Name       Log Size (Mb)    Log Space Used (%)      Status
------------------  ---------------  ----------------------  --------
(...)               (...)            (...)                   (...)
CRASHTEST           253,3047         97,07731                0

Il a plus que quadruplé de volume et il est plein.

Pour récupérer l’espace sur le disque, il faudra faire un DBCC SHRINKFILE en mode TRUNCATEONLY.
Si je lance le DBCC SHRINKFILE tout de suite:

DBCC SHRINKFILE(’CRASHTEST_log’,'TRUNCATEONLY’)

Dbid        Fileid        CurrentSize        MinimumSize    UsedPages         EstimatedPages
---------   ----------- ----------------  ---------------- ---------------   ------------------
8           2            31680            6400             31680             6400

31680*8192Kb = 247,5 Mb, autant dire que presque rien n’a été fait. Celà vient du fait que le TRUNCATEONLY tronque la taille du fichier jusqu’au dernier extent alloué.
Comme mon journal est plein, le dernier extent alloué est précisément à la fin du fichier.

Donc il faut commencer par vider ce journal: je sauvegarde ma transaction :

backup log CRASHTEST to disk='C:\CRASHTEST.log3.trn' with init, stats, compression

go

100 percent processed.

Processed 31461 pages for database 'CRASHTEST', file 'CRASHTEST_log' on file 3.

BACKUP LOG successfully processed 31461 pages in 16.391 seconds (15.723 MB/sec).

Dans mon journal…

DBCC SQLPERF(LOGSPACE)

Database Name        Log Size (Mb)    Log Space Used (%)      Status
------------------   ---------------  ----------------------  --------
(...)                (...)            (...)                   (...)
CRASHTEST            247,4922         4,201521                 0

Je retrouve mon enveloppe à 247,5Mb, et cette fois elle est bien vide. Je vais pouvoir ramener mon log à sa taille d’origine:

DBCC SHRINKFILE(’CRASHTEST_log’,'TRUNCATEONLY’)

Dbid        Fileid        CurrentSize        MinimumSize    UsedPages         EstimatedPages
---------   ----------- ----------------  ---------------- ---------------   ------------------
8           2            6400               6400            6400              6400

Attention, si je choisis de rester en mode FULL, je devrai programmer des sauvegardes de transactions (à travers le SQL Agent par exemple).

Dans le cas où ce journal fait plusieurs dizaines de Gb, je choisis de le vider en passant la base en mode SIMPLE:

ALTER DATABASE CRASHTEST SET RECOVERY SIMPLE

Un checkpoint initié dès la fin de l’ALTER se chargera de vider le contenu du journal, et il ne restera plus qu’à faire le SHRINKFILE.

A suivre l’effet du BULK-LOGGED sur la taille du journal…

[ David B. ]

Continuez votre lecture sur le blog :

• Modes de récupération et journal de transactions, épisode 2 (David Baffaleuf) [SQL ServerVintage]
• How-To: réduire la taille du journal de transactions sur disque (David Baffaleuf) [SQL Server]
• Principes d’une sauvegarde à chaud (David Baffaleuf) [SQL ServerVintage]
• Point-in-time recovery et fn_dump_dblog() (David Baffaleuf) [SQL Server]
• How-To : réduire l’enveloppe de tempdb (David Baffaleuf) [SQL Server]