Dans un précédent billet nous avons parlé des différentes phases d’une session de récupération avec SQL Server. Maintenant nous regardons plus en détail le fonctionnement interne des pages de données et du journal des transactions d’une base de données lorsqu’une mise à jour est effectuée sur les données d’une table.

Toute page d’une base de données enregistre dans son entête le LSN (Logical Sequence Number)  d’une entrée dans le journal des transactions qui est à l’origine d’une modification d’une des lignes de données qu’elle héberge. Voyons cela dans un petit exemple.

CREATE DATABASE test;
GO

USE test;
GO

CREATE TABLE t_log
(
ID INT IDENTITY PRIMARY KEY,
TEXTE VARCHAR(50)
);
GO

BACKUP DATABASE test TO DISK = ‘E:\test.BAK';
GO

INSERT INTO t_log (TEXTE) VALUES (‘TEST’);
INSERT INTO t_log (TEXTE) VALUES (‘TES2′);

Note : une sauvegarde complète est effectuée. Une insertion de 2 lignes de données est ensuite effectuée. Cela permet de faciliter la lecture du journal d’une part et d’allouer au moins une page de données pour la table dbo.t_log d’autre part.

Relevons donc l’ID de la page allouée à la table dbo.t_log à l’aide de la commande DBCC IND

DBCC IND (‘test’, ‘t_log’, 1);

 

Regardons ensuite les informations de l’entête de la page de données ayant l’ID 15.

DBCC TRACEON(3604);
GO
DBCC PAGE(‘test’, 1, 15, 1);

 

    

L’entête de la page ayant l’ID 15 possède un numéro LSN égale à « 20:166:3″.

Regardons maintenant l’entrée correspondante dans le journal des transactions à l’aide de la commande non documentée DBCC LOG.

CREATE TABLE #DBCCLOG
(
CurrentLSN VARCHAR(500),
Operation VARCHAR(50),
Context VARCHAR(50),
TransactionID VARCHAR(500),
LogBlockGeneration BIT,
TagBits VARCHAR(50),
[Log Record Fixed Length] INT,
[Log Record Length] INT,
[Previous LSN] VARCHAR(500),
[Flag Bits] VARCHAR(10),
[Log Reserve] INT,
AllocUnitId BIGINT,
AllocUnitName VARCHAR(100),
PageID VARCHAR(50),
SlotID INT,
[Previous Page LSN] VARCHAR(500),
[Number of Locks] INT,
[Lock Information] VARCHAR(1000),
Description VARCHAR(8000)
);

INSERT INTO #DBCCLOG
EXEC(‘DBCC LOG( »test », 2)’);

SELECT
    CurrentLSN,
    AllocUnitName,
    Operation,
    Context,
    [Previous LSN],
    PageID,
    SlotID,
    [Previous Page LSN],
    [Description]
FROM #DBCCLOG

Nous ne récupérons que que les informations utiles pour le cadre de ce billet :

On retrouve dans le journal le LSN enregistré dans la page de données (colonne CurrentLSN) et qui est à l’origine d’une modification de cette page. La valeur est en hexadécimale. (00000014:000000a6:0003 correspond à 20:166:3 en décimale). On peut également remarquer que le numéro de la page correspondante est présente avec la valeur hexadécimale 0001:0000000f qui correspond  à 1:15 en décimale. (Fichier 1 et page 15). On remarque également le type d’opération effectué (LOP_INSERT_ROWS qui correspond à une insertion de lignes) et le contexte utilisé (LCX_CLUSTERED qui correspond à un verrou exclusif sur l’index cluster). Enfin on peut vérifier que c’est bien l’index cluster de la table dbo.t_log qui est concerné  (AllocUnitName = dbo.t_log.PK__t..).

SQL Server enregistre également des informations supplémentaires comme le LSN précédent enregistré de la page de données (00000014:000000a2:0013) ainsi que le LSN de l’enregistrement précédent du journal (00000014:000000a6:0002). Si on regarde la ligne précédente (LSN = 00000014:000000a6:0002) on remarque qu’une transaction a été initiée (LOP_BEGIN_XACT) avec une opération d’insertion (Description = INSERT ;0x01.). La dernière ligne du journal stipule ensuite que la transaction a été validée (LOP_COMMIT_XACT). Pour rappel, une transaction marquée comme ouverte et non validée ne possède pas d’enregistrement de validation ou d’annulation.

Cas d’étude : (Les LSN ont ici leur valeur hexadécimal)

Lors d’une session de restauration le LSN « 00000014:000000a6:0003″ est lu depuis le journal. Celui-ci fait référence à la page 15. Hors le LSN enregistré dans cette page est inférieur au LSN courant du journal. Dans ce cas SQL Server doit rejouer les enregistrements du journal sur la page de données. (REDO).

Dans ce deuxième cas le LSN du journal est le même que celui enregistré dans la page de données. Aucune action n’est nécessaire.

David BARBARIN (Mikedavem)
Elève ingénieur CNAM Lyon