Purger le fichier de transaction d'une base en AlwaysOn ou en Miroir

Purger le fichier de transaction d’une base en AlwaysOn ou en Miroir

23 mars 2016

Commentaires fermés

Une problématique récurrente est comment purger le fichier de transaction d’une base en AlwaysOn ou en Miroir, dont on ne maîtrise plus la croissance en raison du fait qu’il soit peu sauvegardé, voir pas sauvegardé du tout ? Quand le problème survient, il se pourrait bien que la taille du fichier de transaction dépasse celle des données et/ou que vous ne disposiez pas de l’espace et du temps nécessaire pour le sauvegarder. Une mesure d’urgence consiste à sauvegarder le fichier de transaction (dans le vide, d’où l’emplacement à NUL) jusqu’à ce que le nombre de fichiers journaux virtuels (VLF) actifs soit réduit à néant, donc à 0. Une fois cette opération réalisée, la réduction du fichier de transaction sera possible. A noter que cette opération peut être plus ou moins longue suivant le nombre d’entrée présentes. Cela étant, c’est potentiellement moins long et moins laborieux que sortir la base du groupe de disponibilité pour la repasser en mode SIMPLE, etc (taille du tuyau insuffisante entre le site principal et le site secondaire et/ou volumétrie de la base importante).

USE [<MirorDbName, sysname, value>]
GO

DECLARE @LogInfo AS TABLE (
                RecoveryUnitId BIGINT,
                FileID BIGINT,
                FileSize BIGINT,
                StartOffset BIGINT,
                FSeqNo BIGINT,
                Status BIGINT,
                Parity BIGINT,
                CreateLSN VARCHAR(50)
)

INSERT INTO @LogInfo EXECUTE ('DBCC LOGINFO');

WHILE (SELECT COUNT(*) AS VLFs
FROM @LogInfo
WHERE [Status] = 2
) > 1
BEGIN

	BACKUP LOG [<MirorDbName, sysname, value>] TO DISK = 'NUL'; 

	DELETE FROM @LogInfo
	INSERT INTO @LogInfo EXECUTE ('DBCC LOGINFO');

END

SELECT *
FROM @LogInfo
WHERE [Status] = 2
DBCC SHRINKFILE (N'<DbLogName, sysname, value>' , 0, TRUNCATEONLY)

USE [<MirorDbName, sysname, value>]

DECLARE @LogInfo AS TABLE (

RecoveryUnitId BIGINT,

FileID BIGINT,

FileSize BIGINT,

StartOffset BIGINT,

FSeqNo BIGINT,

Status BIGINT,

Parity BIGINT,

CreateLSN VARCHAR(50)

)

INSERT INTO @LogInfo EXECUTE ('DBCC LOGINFO');

WHILE (SELECT COUNT(*) AS VLFs

FROM @LogInfo

WHERE [Status] = 2

) > 1

BEGIN

BACKUP LOG [<MirorDbName, sysname, value>] TO DISK = 'NUL';

DELETE FROM @LogInfo

INSERT INTO @LogInfo EXECUTE ('DBCC LOGINFO');

END

SELECT *

FROM @LogInfo

WHERE [Status] = 2

DBCC SHRINKFILE (N'<DbLogName, sysname, value>' , 0, TRUNCATEONLY)

Avant d’en arriver là il serait bon de mettre un plan de sauvegarde adapté qui inclut bien évidement le fichier de transaction ou encore de surveiller sur sa croissante. Bref, n’attendez pas qu’il soit trop tard.

Mais rendons à César ce qui est à César, voir cet excellent article dont je me suis largement inspirée si vous voulez avoir plus de détails sur le fichier de transaction et les VLF :

http://blog.developpez.com/mikedavem/p8044/sql-server-2005/architecture/pourquoi_la_commande_shrinkfile_ne_redui