Un point souvent remonté lors de gros traitements d’insertion ou de mise à jour de données réside dans une expansion très (trop) importante de l’espace disque utilisé par le journal de transactions de la base de données.

Nous allons voir ici comment limiter l’expansion de ce fichier, dans le contexte du mode de restauration simple.

Tout d’abord, il convient de savoir que chaque modification de données dans une base SQL Server fait tout d’abord l’objet d’une écriture dans le journal de transactions de la base de données, et ce quelle que soit la configuration de la base de données en terme de mode de récupération.

Les éléments stockés dans le journal de transaction sont suffisants pour faire et défaire la modification.

Une instruction est considérée comme exécutée dès lors que les éléments correspondants ont été écrit dans le fichier du journal de transactions. Les informations ne seront écrites dans le ou les autres fichiers de la base de données (fichiers traditionnellement suffixés MDF ou NDF) que plus tard, parfois après plusieurs minutes, lors d’opérations appellées Checkpoint.

Les fichiers de journal de transactions sont donc vraiment au coeur du processus de modification des données pour SQL Server.

La modification des données fonctionne toujours dans un contexte de Transactions, qui permettent de maintenir la cohérence des données. Toute modification est effectuée via une transaction; notamment une instruction de modification qui n’est pas encadrée explicitement par une transaction est automatiquement encadrée par une transaction implicite.

L’espace utilisé dans le journal de transactions va de l’ouverture à la fin de la transaction (encadrante dans le cas de transactions imbriquées). Les informations stockées dans cet espace peuvent notamment être utilisées si la transaction est annulée au lieu d’être validée.

Une fois la transaction terminée (validée ou annulée), l’espace consommé dans le journal de transaction est marqué comme « libéré et utilisable pour un autre stockage » si une opération de Checkpoint a couvert cet espace et :

• dès la fin de la transaction si le mode de récupération est « simple »
• si une sauvegarde de journal de transaction (fichiers traditionnellement suffixés TRN) a couvert cet espace

Cette brève présentation étant réalisée, attachons-nous maintenant à comprendre comment limiter la croissance du journal de transaction lors d’opérations massives d’insertion ou de mise à jour de données.

Si nous sommes dans un mode de récupération « complet », les données seront conservées dans le journal tant que celui-ci n’aura pas fait l’objet d’une sauvegarde. Il est donc envisageable de faire l’insertion « petit bout par petit bout », et de lancer une sauvegarde du journal entre 2 blocs. Mais ceci ne ferait que déplacer le problème d’espace de stockage depuis la zone utilisée pour le journal vers la zone utilisée pour son backup. De plus, cela ralentirait considérablement la procédure d’insertion. Pour ces raisons, ce procédé n’est pas utilisé et ne sera donc pas détaillé ici.

Dans la pratique, il est fréquent de voir des bases de données positionnées en mode de récupération « simple », sur lesquelles le besoin est d’intégrer un grand nombre de données tout en limitant l’impact sur la taille du journal de transactions.

Même si SQL Server gère parfaitement bien les traitements massifs, il est dans ce cas déconseillé d’effectuer le traitement en une instruction unique, car elle a un impact conséquent sur la taille du journal de transactions, y compris en mode de récupération simple.

S’il est fonctionnellement possible de découper l’insertion en plusieurs morceaux (et donc si l’ensemble n’a pas besoin de faire l’objet d’une transaction unique), l’impact sur le journal de transaction peut en être très nettement amélioré.

Voici un petit exemple de démonstration. Tout d’abord la préparation des données :

-- Création de la table source
USE [Source]
GO
CREATE TABLE [dbo].[TableSource](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[GUID] [uniqueidentifier] NULL,
	[Espaces] [char](200) NULL,
 CONSTRAINT [PK_TableSource] PRIMARY KEY CLUSTERED
(
	[Id] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[TableSource] ADD  CONSTRAINT [DF_TableSource_GUID]  DEFAULT (newid()) FOR [GUID]
GO

-- Création de la table cible
USE [Cible]
GO
CREATE TABLE [dbo].[TableTransfert](
	[Id] [int] NOT NULL,
	[GUID] [uniqueidentifier] NULL,
	[Espaces] [char](200) NULL
) ON [PRIMARY]
GO

-- Remplissage de la table source
USE [Source]
GO
set nocount off
go
insert into TableSource(Espaces) values ('')
go
insert into TableSource(Espaces) select Espaces from TableSource
go 20
select COUNT(*) from TableSource
--> 1048576 enregistrements

Lorsque l’on utilise la copie en une seule instruction, on obtient :

insert into Cible.dbo.TableTransfert (Id,GUID,Espaces)
select Id,GUID,Espaces
from Source.dbo.TableSource

select recovery_model_desc from sys.databases where database_id=DB_ID()
select type_desc,size from sys.database_files

Un petit coup de nettoyage …

SET NOCOUNT ON
GO
USE [Cible]
GO
truncate table dbo.TableTransfert
GO
CHECKPOINT
GO
DBCC SHRINKFILE (N'Cible_log' , 1)
DBCC SHRINKFILE (N'Cible' , 1)
GO
select recovery_model_desc from sys.databases where database_id=DB_ID()
select type_desc,size from sys.database_files

Et on peut relancer le traitement par petits morceaux. Je pousse ici un peu à l’extrême, en faisant des blocs de 1000 enregistrements sur un lot de 1 million, mais c’est juste pour la démonstration.

declare @Groupe int

create table #Cles (Id int, Ordre int identity, Groupe AS (Ordre/1000)+1 PERSISTED)
create unique clustered index IX_Cle ON #Cles(Groupe, Ordre)
insert into #Cles (Id)
select Id
from Source.dbo.TableSource

set @Groupe=0

while (@@ROWCOUNT>0)
BEGIN
	set @Groupe=@Groupe+1

	insert into Cible.dbo.TableTransfert (Id,GUID,Espaces)
	select ts.Id,ts.GUID,ts.Espaces
	from #Cles c
	join Source.dbo.TableSource ts on ts.Id=c.Id
	where c.Groupe=@Groupe
END

drop table #Cles

Et là, on s’aperçoit que le fichier journal de transaction a beaucoup moins besoin de grossir…

select type_desc,size from sys.database_files

Et lorsque l’on cherche à diminuer l’expansion du journal de transaction, ce n’est pas seulement pour des considérations d’espace disque, mais aussi pour des raisons de performance. En effet, le grossissement automatique du journal a un coût (car l’espace disque correspondant doit être purgé avant utilisation par SQL Server). De plus, étant donné que toute instruction de modification de données passage nécessairement par une écriture dans le journal, le fait qu’il soit en cours d’expansion bloque toutes les transactions, et pas seulement la transaction massive qui nous concerne ici.

Au final, s’il est envisageable d’avoir une base de données en mode de récupération simple et si le découpage est possible sans compromettre l’intégrité des données, il est vivement conseillé de travailler par lots de taille contrôlée plutôt qu’en utilisant des traitements massifs.