transaction log qui explose

Version imprimable

08/04/2010, 17h19
Monstros Velu

transaction log qui explose

Bonjour,

J'ai une base de donnée d'environ 30Go. Le recovery mode est "simple".

J'ai une requête qui crée 135Go de transaction log... est-ce normal ? Je veux dire... pourquoi loguer la transaction : on restaure un back-up, dans ce cas, non ?
08/04/2010, 17h28
mikedavem

Bonjour,

Il est normal que même en mode simple, l'opération soit enregistré dans le journal ... En cas d'annulation de l'opération cela permet de relire ce journal pour effectuer un rollback.

++
08/04/2010, 17h34
Monstros Velu

Oui, mais une opération de 135Go pour une base de 31Go, n'est-ce pas abusif ?
08/04/2010, 17h36
elsuket

Bonjour,

Je trouve quand même bizarre que la taille de votre journal des transactions soit plus de 4 fois plus gros que votre base de données.
Quelle est donc cette requête ?
08/04/2010, 17h45
mikedavem

Je dirais même plus ... quelle est donc cette requête ? :D

++
08/04/2010, 17h53
SQLpro
Oui, alors... Cette requête ???

Bonne vieille méthode :
Code:

1 2 DBCC OPENTRAN...
A +

A priori, c'est ici que ça explose (@withcheckpoint vaut 1) :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
if @withcheckpoint = 1
		begin
			checkpoint
		end
 
		set @step = 'Lines to create are marked with TO_CREATE = 1'
		if (@verbose = 1) exec dbo.ELL_Insert @ok = 1, @action = @action, @step = @step , @message = ' starting...'
 
		insert
			dbo.PCS_PROVIDERCITYSERVICE
			(PVD_EXTERNAL_ID, SRV_EXTERNAL_ID, INSEE_CODE, TO_CREATE)
			select
				 PVD_EXTERNAL_ID, SRV_EXTERNAL_ID, INSEE_CODE, 1
			from
				dbo.PCS_PROVIDERCITYSERVICE_TEMP tmp
			where
				tmp.PCT_STATUS <> 3 and tmp.PSH_STATUS <> 3 
		except
			select
			 PVD_EXTERNAL_ID, SRV_EXTERNAL_ID, INSEE_CODE, 1
			from
				dbo.PCS_PROVIDERCITYSERVICE pcs
 
 
		if @withcheckpoint = 1
		begin
			checkpoint
		end

Du moins, ce bloc est dans un try catch (avec plein d'autres traitements sur les tables PCS), et, quand je n'ai que 90Go de disque sur le serveur, le catch récupère l'erreur avec le step : 'Lines to create are marked with TO_CREATE = 1'

On a dans la table PCS_PROVIDERCITYSERVICE_TEMP environ 57 millions de lignes, et la table PCS_PROVIDERCITYSERVICE est vide, à ce moment là (c'est la 1ère insertion)

Les scripts de création des tables :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
USE [D_EAF_ETL]
GO
/****** Object:  Table [dbo].[PCS_PROVIDERCITYSERVICE]    Script Date: 04/08/2010 18:02:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[PCS_PROVIDERCITYSERVICE](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[PVD_EXTERNAL_ID] [varchar](36) NOT NULL,
	[SRV_EXTERNAL_ID] [varchar](36) NOT NULL,
	[INSEE_CODE] [varchar](10) NOT NULL,
	[PVD_ID] [int] NULL,
	[SRV_ID] [int] NULL,
	[GEO_ID] [int] NULL,
	[TO_CREATE] [bit] NOT NULL CONSTRAINT [DF_PCS_PROVIDERCITYSERVICE_TO_CREATE]  DEFAULT ((0)),
	[TO_UPDATE] [bit] NOT NULL CONSTRAINT [DF_PCS_PROVIDERCITYSERVICE_TO_UPDATE]  DEFAULT ((0)),
	[TO_DELETE] [bit] NOT NULL CONSTRAINT [DF_PCS_PROVIDERCITYSERVICE_TO_DELETE]  DEFAULT ((0)),
	[CREATION_DATE] [datetime] NOT NULL CONSTRAINT [DF_PCS_PROVIDERCITYSERVICE_CREATION_DATE]  DEFAULT (getdate()),
	[UPDATE_DATE] [datetime] NOT NULL CONSTRAINT [DF_PCS_PROVIDERCITYSERVICE_UPDATE_DATE]  DEFAULT (getdate()),
 CONSTRAINT [PK_PCS_PROVIDERCITYSERVICE] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
 
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[PCS_PROVIDERCITYSERVICE]  WITH CHECK ADD  CONSTRAINT [FK_PCS_PROVIDERCITYSERVICE_GEO_GEOGRAPHY] FOREIGN KEY([GEO_ID])
REFERENCES [dbo].[GEO_GEOGRAPHY] ([ID])
GO
ALTER TABLE [dbo].[PCS_PROVIDERCITYSERVICE] CHECK CONSTRAINT [FK_PCS_PROVIDERCITYSERVICE_GEO_GEOGRAPHY]
GO
ALTER TABLE [dbo].[PCS_PROVIDERCITYSERVICE]  WITH CHECK ADD  CONSTRAINT [FK_PCS_PROVIDERCITYSERVICE_PVD_PROVIDER] FOREIGN KEY([PVD_ID])
REFERENCES [dbo].[PVD_PROVIDER] ([ID])
GO
ALTER TABLE [dbo].[PCS_PROVIDERCITYSERVICE] CHECK CONSTRAINT [FK_PCS_PROVIDERCITYSERVICE_PVD_PROVIDER]
GO
ALTER TABLE [dbo].[PCS_PROVIDERCITYSERVICE]  WITH CHECK ADD  CONSTRAINT [FK_PCS_PROVIDERCITYSERVICE_SRV_SERVICE] FOREIGN KEY([SRV_ID])
REFERENCES [dbo].[SRV_SERVICE] ([ID])
GO
ALTER TABLE [dbo].[PCS_PROVIDERCITYSERVICE] CHECK CONSTRAINT [FK_PCS_PROVIDERCITYSERVICE_SRV_SERVICE]
 
USE [D_EAF_ETL]
GO
/****** Object:  Table [dbo].[PCS_PROVIDERCITYSERVICE_TEMP]    Script Date: 04/08/2010 18:02:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[PCS_PROVIDERCITYSERVICE_TEMP](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[PVD_EXTERNAL_ID] [varchar](36) NOT NULL,
	[SRV_EXTERNAL_ID] [varchar](36) NOT NULL,
	[INSEE_CODE] [varchar](10) NOT NULL,
	[PCT_STATUS] [smallint] NOT NULL,
	[PSH_STATUS] [smallint] NOT NULL,
	[CREATION_DATE] [datetime] NOT NULL CONSTRAINT [DF_PCS_PROVIDERCITYSERVICE_TEMP_CREATION_DATE]  DEFAULT (getdate()),
 CONSTRAINT [PK_PCS_PROVIDERCITYSERVICE_TEMP] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
 
GO
SET ANSI_PADDING OFF

08/04/2010, 19h40
mikedavem

D'autres index sur la table [dbo].[PCS_PROVIDERCITYSERVICE] ?

En faisant des insertions en masse de 57 millions de lignes cela ne me parait pas étonnant .. si vous avez en plus d'autres index, chaque ligne d'index est également journalisé ...

A voir également si l'ensemble de votre code n'est pas encapsulé dans une transaction ... auquel cas viennent s'ajouter également les différents traitements dans le bloc try ...

Je ne pense pas que vous ayez besoin de faire des CHECKPOINT surtout que ceux-ci ne pourront être effectifs qu'après la fin d'une transaction et vous êtes en mode de récupération SIMPLE qui plus est ..

Vous pouvez utiliser l'instruction DBCC SQLPERF(LOGSPACE) pour voir l'évolution de remplissage du journal
++
08/04/2010, 20h18
kagemaru

Si la table dbo.PCS_PROVIDERCITYSERVICE est recréée implicitement à chaque execution du traitement et que tu es en mode de récup SIMPLE ou BULK-LOGGED, je te conseillerais de créer cette table avec la commande SELECT INTO. L'avantage est que l'insertion se fait avec un minimum de journalisation. A la fin de l'insert, il suffit de créer les contraintes sur la table.

C'est très efficace sur ce genre de volumes mais cela ne vaut que si PCS_PROVIDERCITYSERVICE est vide au départ bien entendu.

http://msdn.microsoft.com/en-us/library/ms188029.aspx