![\"\"](\"https:\/\/www.sqlserver.fr\/blog\/wp-content\/uploads\/2012\/06\/RecoveryModel.png\" "\\"RecoveryModel\\"")
<\/a><\/p>\nLes \u00e9l\u00e9ments stock\u00e9s dans le journal de transaction sont suffisants pour faire et d\u00e9faire la modification.<\/p>\n
Une instruction est consid\u00e9r\u00e9e comme ex\u00e9cut\u00e9e d\u00e8s lors que les \u00e9l\u00e9ments correspondants ont \u00e9t\u00e9 \u00e9crit dans le fichier du journal de transactions. Les informations ne seront \u00e9crites dans le ou les autres fichiers de la base de donn\u00e9es (fichiers traditionnellement suffix\u00e9s MDF ou NDF) que plus tard, parfois apr\u00e8s plusieurs minutes, lors d’op\u00e9rations appell\u00e9es Checkpoint.<\/p>\n
Les fichiers de journal de transactions sont donc vraiment au coeur du processus de modification des donn\u00e9es pour SQL Server.<\/p>\n
La modification des donn\u00e9es fonctionne toujours dans un contexte de Transactions, qui permettent de maintenir la coh\u00e9rence des donn\u00e9es. Toute modification est effectu\u00e9e via une transaction; notamment une instruction de modification qui n’est pas encadr\u00e9e explicitement par une transaction est automatiquement encadr\u00e9e par une transaction implicite.<\/p>\n
L’espace utilis\u00e9 dans le journal de transactions va de l’ouverture \u00e0 la fin de la transaction (encadrante dans le cas de transactions imbriqu\u00e9es). Les informations stock\u00e9es dans cet espace peuvent notamment \u00eatre utilis\u00e9es si la transaction est annul\u00e9e au lieu d’\u00eatre valid\u00e9e.<\/p>\n
Une fois la transaction termin\u00e9e (valid\u00e9e ou annul\u00e9e), l’espace consomm\u00e9 dans le journal de transaction est marqu\u00e9 comme \u00ab\u00a0lib\u00e9r\u00e9 et utilisable pour un autre stockage\u00a0\u00bb si une op\u00e9ration de Checkpoint a couvert cet espace et :<\/p>\n
- \n
- d\u00e8s la fin de la transaction si le mode de r\u00e9cup\u00e9ration est \u00ab\u00a0simple\u00a0\u00bb<\/li>\n
- si une sauvegarde de journal de transaction (fichiers traditionnellement suffix\u00e9s TRN) a couvert cet espace<\/li>\n<\/ul>\n
Cette br\u00e8ve pr\u00e9sentation \u00e9tant r\u00e9alis\u00e9e, attachons-nous maintenant \u00e0 comprendre comment limiter la croissance du journal de transaction lors d’op\u00e9rations massives d’insertion ou de mise \u00e0 jour de donn\u00e9es.<\/span><\/p>\n
Si nous sommes dans un mode de r\u00e9cup\u00e9ration \u00ab\u00a0complet\u00a0\u00bb, les donn\u00e9es seront conserv\u00e9es dans le journal tant que celui-ci n’aura pas fait l’objet d’une sauvegarde. Il est donc envisageable de faire l’insertion \u00ab\u00a0petit bout par petit bout\u00a0\u00bb, et de lancer une sauvegarde du journal entre 2 blocs. Mais ceci ne ferait que d\u00e9placer le probl\u00e8me d’espace de stockage depuis la zone utilis\u00e9e pour le journal vers la zone utilis\u00e9e pour son backup. De plus, cela ralentirait consid\u00e9rablement la proc\u00e9dure d’insertion. Pour ces raisons, ce proc\u00e9d\u00e9 n’est pas utilis\u00e9 et ne sera donc pas d\u00e9taill\u00e9 ici.<\/p>\n
Dans la pratique, il est fr\u00e9quent de voir des bases de donn\u00e9es positionn\u00e9es en mode de r\u00e9cup\u00e9ration \u00ab\u00a0simple\u00a0\u00bb, sur lesquelles le besoin est d’int\u00e9grer un grand nombre de donn\u00e9es tout en limitant l’impact sur la taille du journal de transactions.<\/p>\n
M\u00eame si SQL Server g\u00e8re parfaitement bien les traitements massifs, il est dans ce cas d\u00e9conseill\u00e9 d’effectuer le traitement en une instruction unique, car elle a un impact cons\u00e9quent sur la taille du journal de transactions, y compris en mode de r\u00e9cup\u00e9ration simple.<\/p>\n
S’il est fonctionnellement possible de d\u00e9couper 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 \u00eatre tr\u00e8s nettement am\u00e9lior\u00e9.<\/p>\n
Voici un petit exemple de d\u00e9monstration. Tout d’abord la pr\u00e9paration des donn\u00e9es :<\/p>\n
-- Cr\u00e9ation de la table source\r\nUSE [Source]\r\nGO\r\nCREATE TABLE [dbo].[TableSource](\r\n\t[Id] [int] IDENTITY(1,1) NOT NULL,\r\n\t[GUID] [uniqueidentifier] NULL,\r\n\t[Espaces] [char](200) NULL,\r\n CONSTRAINT [PK_TableSource] PRIMARY KEY CLUSTERED\r\n(\r\n\t[Id] ASC\r\n) ON [PRIMARY]\r\n) ON [PRIMARY]\r\nGO\r\nALTER TABLE [dbo].[TableSource] ADD CONSTRAINT [DF_TableSource_GUID] DEFAULT (newid()) FOR [GUID]\r\nGO\r\n\r\n-- Cr\u00e9ation de la table cible\r\nUSE [Cible]\r\nGO\r\nCREATE TABLE [dbo].[TableTransfert](\r\n\t[Id] [int] NOT NULL,\r\n\t[GUID] [uniqueidentifier] NULL,\r\n\t[Espaces] [char](200) NULL\r\n) ON [PRIMARY]\r\nGO\r\n\r\n-- Remplissage de la table source\r\nUSE [Source]\r\nGO\r\nset nocount off\r\ngo\r\ninsert into TableSource(Espaces) values ('')\r\ngo\r\ninsert into TableSource(Espaces) select Espaces from TableSource\r\ngo 20\r\nselect COUNT(*) from TableSource\r\n--> 1048576 enregistrements<\/pre>\nLorsque l’on utilise la copie en une seule instruction, on obtient :<\/p>\n
insert into Cible.dbo.TableTransfert (Id,GUID,Espaces)\r\nselect Id,GUID,Espaces\r\nfrom Source.dbo.TableSource\r\n\r\nselect recovery_model_desc from sys.databases where database_id=DB_ID()\r\nselect type_desc,size from sys.database_files<\/pre>\n
![\"\"](\"https:\/\/www.sqlserver.fr\/blog\/wp-content\/uploads\/2012\/06\/Monobloc.png\" "\\"Monobloc\\"")
<\/a><\/p>\nUn petit coup de nettoyage …<\/p>\n
SET NOCOUNT ON\r\nGO\r\nUSE [Cible]\r\nGO\r\ntruncate table dbo.TableTransfert\r\nGO\r\nCHECKPOINT\r\nGO\r\nDBCC SHRINKFILE (N'Cible_log' , 1)\r\nDBCC SHRINKFILE (N'Cible' , 1)\r\nGO\r\nselect recovery_model_desc from sys.databases where database_id=DB_ID()\r\nselect type_desc,size from sys.database_files<\/pre>\n
![\"\"](\"https:\/\/www.sqlserver.fr\/blog\/wp-content\/uploads\/2012\/06\/Vidage.png\" "\\"Vidage\\"")
<\/a><\/p>\nEt on peut relancer le traitement par petits morceaux. Je pousse ici un peu \u00e0 l’extr\u00eame, en faisant des blocs de 1000 enregistrements sur un lot de 1 million, mais c’est juste pour la d\u00e9monstration.<\/p>\n
declare @Groupe int\r\n\r\ncreate table #Cles (Id int, Ordre int identity, Groupe AS (Ordre\/1000)+1 PERSISTED)\r\ncreate unique clustered index IX_Cle ON #Cles(Groupe, Ordre)\r\ninsert into #Cles (Id)\r\nselect Id\r\nfrom Source.dbo.TableSource\r\n\r\nset @Groupe=0\r\n\r\nwhile (@@ROWCOUNT>0)\r\nBEGIN\r\n\tset @Groupe=@Groupe+1\r\n\r\n\tinsert into Cible.dbo.TableTransfert (Id,GUID,Espaces)\r\n\tselect ts.Id,ts.GUID,ts.Espaces\r\n\tfrom #Cles c\r\n\tjoin Source.dbo.TableSource ts on ts.Id=c.Id\r\n\twhere c.Groupe=@Groupe\r\nEND\r\n\r\ndrop table #Cles<\/pre>\n
Et l\u00e0, on s’aper\u00e7oit que le fichier journal de transaction a beaucoup moins besoin de grossir…<\/p>\n
select type_desc,size from sys.database_files<\/pre>\n
![\"\"](\"https:\/\/www.sqlserver.fr\/blog\/wp-content\/uploads\/2012\/06\/Multiblocs.png\" "\\"Multiblocs\\"")
<\/a><\/p>\nEt lorsque l’on cherche \u00e0 diminuer l’expansion du journal de transaction, ce n’est pas seulement pour des consid\u00e9rations d’espace disque, mais aussi pour des raisons de performance. En effet, le grossissement automatique du journal a un co\u00fbt (car l’espace disque correspondant doit \u00eatre purg\u00e9 avant utilisation par SQL Server). De plus, \u00e9tant donn\u00e9 que toute instruction de modification de donn\u00e9es passage n\u00e9cessairement par une \u00e9criture 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.<\/p>\n
Au final, s’il est envisageable d’avoir une base de donn\u00e9es en mode de r\u00e9cup\u00e9ration simple et si le d\u00e9coupage est possible sans compromettre l’int\u00e9grit\u00e9 des donn\u00e9es, il est vivement conseill\u00e9 de travailler par lots de taille contr\u00f4l\u00e9e plut\u00f4t qu’en utilisant des traitements massifs.<\/p>\n","protected":false},"excerpt":{"rendered":"
Un point souvent remont\u00e9 lors de gros traitements d’insertion ou de mise \u00e0 jour de donn\u00e9es r\u00e9side dans une expansion tr\u00e8s (trop) importante de l’espace disque utilis\u00e9 par le journal de transactions de la base de donn\u00e9es. Nous allons voir … Continuer la lecture