Discussion :

[janlouk]

Bonjour à tous,

Je ne me suis jamais retrouvé dans une telle situation, avoir 2 fichiers .ldf dans une DB. Ils se trouvent sur 2 disques différent.

Il y en a un qui fait 472 GB et lui "bouge", mais l'autre je n'ai pas l'impression car il fait 20 GB pil poil. L'autogrowth est unrestricted tous les deux.

Je sais que c'est le cas pour les fichiers data. Mais pour les LOG, si j'empêche un de grandir (le plus gros car le disque est full), est-ce que l'autre va être automatiquement utilisé?

Ce n'est qu'une situation temporaire, le temps d'avoir l'accord d'agrandir le disque, de l'agrandir... Je voudrais ne plus avoir les messages comme quoi le disque est full.

Merci
Jean-Luc

[SQLpro]

Il utilise successivement l'un puis l'autre. Si le premier est verrouillé à 20 Go il faut d'abord lui permettre de grandir :
1) voir le max_size
2) voir le FILEGROWTH
Ensuite, fixer le second.

M'est avis que 450 Go sur un JT c'est beaucoup... Trop sans doute !

Quelles est la taille des données de la base ?

Un tel fichier ne peut se concevoir que si la base est une VLDB d'au moins 1,4 To...

A +

[Invité]

Question subsidiaire : quelle est le mode de recouvrement de cette bd ?
Question bonus : Si c'est FULL, est-ce qu'une stratégie de sauvegarde régulière du JT est en place ?

[janlouk]

Bonjour Frédéric,

J'ai fait une petite erreur, le fichier de 472 Go est en illimité pour le MAX_SIZE. Le fichier de 20 GB est limité à 100 GB.
Pour la croissance, c'est par 1024 MB.

La taille de tous les fichiers DATA totalise 7.5 TB.

Je pencherais pour supprimer le fichier de 20 GB se trouvant sur un disque (où il y a des fichiers de data), augmenter le disque prévu que pour les LOG où se trouve le gros log de 472 GB.

Je fais bien?

PS : il n'y quand même aucun intérêt à créer plusieurs .LDF, si?

[janlouk]

Question subsidiaire : quelle est le mode de recouvrement de cette bd ?
Question bonus : Si c'est FULL, est-ce qu'une stratégie de sauvegarde régulière du JT est en place ?

C'est du full avec un backup des log toutes les 2 heures.

Merci pour ces questions, c'est parfois par le plus simple qu'on ne pense pas. Je vais voir pour augmenter la fréquence des backup log, ça règlera déjà un soucis de l'espace.

[SQLpro]

Bonjour Frédéric,

J'ai fait une petite erreur, le fichier de 472 Go est en illimité pour le MAX_SIZE. Le fichier de 20 GB est limité à 100 GB.
Pour la croissance, c'est par 1024 MB.

La taille de tous les fichiers DATA totalise 7.5 TB.

Je pencherais pour supprimer le fichier de 20 GB se trouvant sur un disque (où il y a des fichiers de data), augmenter le disque prévu que pour les LOG où se trouve le gros log de 472 GB.

Je fais bien?

PS : il n'y quand même aucun intérêt à créer plusieurs .LDF, si?

500 Go de JT pour 7,5 To, c'est très correct. Personnellement je donnerais même un peu de mou à ce journal en allant vers les 750 Go...
Un pas de croissance de 1 Go c'est quand même beaucoup... S'agit-il de disques SSD ?

Oui tu fais bien.

L'intérêt d'avoir plusieurs fichier est limité, mais peu servir en secours, justement quand un des disque est saturé !

A +

[janlouk]

Un pas de croissance de 1 Go c'est quand même beaucoup... S'agit-il de disques SSD ?

Je ne sais pas pour les disques, je devrais voir avec l'équipe Storage.

1 Go c'est beaucoup? Je pensais qu'il fallait minimiser le nombre de VLF, et donc le nombre de fois où le fichier augmente de taille. C'est pour ça que je mets 1 Go.

[SQLpro]

En principe l'autogrowth devrait être exceptionnel. Tu ne devrais jamais en avoir. Donc, le système va allouer des VLF en fonction de la taille dynamique du JT (nouveau depuis 2014)
https://www.sqlskills.com/blogs/paul...l-server-2014/

A +

[hmira]

Je ne sais pas pour les disques, je devrais voir avec l'équipe Storage.

1 Go c'est beaucoup? Je pensais qu'il fallait minimiser le nombre de VLF, et donc le nombre de fois où le fichier augmente de taille. C'est pour ça que je mets 1 Go.

Bonjour,
1 Go pour l'autogrow, me parait personnellement une valeur correcte.
Sachant que comme l'a rappelé SQLPro "autogrowth devrait être une opération exceptionnelle".
Cependant, la création initiale du fichier log doit être effectuée par pallier (généralement par pallier de 1 Go à 6 Go par grow, voire jusqu'à 8 Go par grow), et ce, pour éviter d'avoir des fichier VLF de très grandes tailles.
La taille des fichiers individuels VLF, lorsqu'elle est excessive, peut avoir un impact négatif sur les performances.
Personnellement, j'essaie, autant que possible, de faire en sorte que, lors d'un autogrow (ou d'un grow), la taille de chacun des fichiers individuels VLF créé, soit comprise entre 64 Mo et 512 Mo (maximum). J'essaie donc de ne jamais dépasser 512 Mo comme taille de chacun des fichiers individuels VLF créé après un autogrow (ou d'un grow).
Je me suis amusé à répertorier dans un tableau Excel (voir image ci-dessous) quelques exemple de valeurs d'autogrow (ou de grow) combinés à la taille individuelle des fichier VLF et du nombres de fichiers VLF générés.
Personnellement, (et cela n'engage que moi !), j'essaie de rester dans le vert foncé (entre 64 Mo et 256 Mo), à la limite, dans la zone vert clair (entre 256 Mo et 512 Mo), mais jamais au delà.



A+

[elsuket]

Salut Jean-Luc,

Pour avoir vu quelques bases de données ayant la taille de données que tu donnes, j'ai rarement vu des fichiers du journal des transactions aussi volumineux.
La raison est simple : une sauvegarde du fichier du journal des transactions était prise relativement fréquemment, variant entre 15 et 30 minutes.

Ceci dit, s'il fait cette taille, c'est que quelque chose a causé son grossissement. Il est possible que ce soit une requête de maboule qui soit passée manuellement sur la base, ou la reconstruction d'un index très volumineux.
Les grossissements automatiques de fichiers sont écrits dans la trace SQL Trace par défaut. On peut retrouver les événements avec l'application et le nom de login sous lequel ils se sont produits ... mais pas le texte de la requête.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
SET NOCOUNT ON
GO
 
DECLARE @database_name sysname = 'maBase'
	, @event_name sysname = 'File Auto Grow' -- Uses LIKE fuzzy predicate
	, @start_date datetime --= '2015-11-06 00:00:00.000'
	, @end_date datetime --= '2015-11-07 00:00:00.000'
------------------------------------------------------------
DECLARE @events TABLE
(
	event_id tinyint
	, event_name sysname
)
 
INSERT @events VALUES (0, 'Reserved')
, (1, 'Reserved')
, (2, 'Reserved')
, (3, 'Reserved')
, (4, 'Reserved')
, (5, 'Reserved')
, (6, 'Reserved')
, (7, 'Reserved')
, (8, 'Reserved')
, (9, 'Reserved')
, (10, 'RPC:Completed')
, (11, 'RPC:Starting')
, (12, 'SQL:BatchCompleted')
, (13, 'SQL:BatchStarting')
, (14, 'Audit Login')
, (15, 'Audit Logout')
, (16, 'Attention')
, (17, 'ExistingConnection')
, (18, 'Audit Server Starts and Stops')
, (19, 'DTCTransaction')
, (20, 'Audit Login Failed')
, (21, 'EventLog')
, (22, 'ErrorLog')
, (23, 'Lock:Released')
, (24, 'Lock:Acquired')
, (25, 'Lock:Deadlock')
, (26, 'Lock:Cancel')
, (27, 'Lock:Timeout')
, (28, 'Degree of Parallelism Event (7.0 Insert)')
, (29, 'Reserved')
, (30, 'Reserved')
, (31, 'Reserved')
, (32, 'Reserved')
, (33, 'Exception')
, (34, 'SP:CacheMiss')
, (35, 'SP:CacheInsert')
, (36, 'SP:CacheRemove')
, (37, 'SP:Recompile')
, (38, 'SP:CacheHit')
, (39, 'Deprecated')
, (40, 'SQL:StmtStarting')
, (41, 'SQL:StmtCompleted')
, (42, 'SP:Starting')
, (43, 'SP:Completed')
, (44, 'SP:StmtStarting')
, (45, 'SP:StmtCompleted')
, (46, 'Object:Created')
, (47, 'Object:Deleted')
, (48, 'Reserved')
, (49, 'Reserved')
, (50, 'SQL Transaction')
, (51, 'Scan:Started')
, (52, 'Scan:Stopped')
, (53, 'CursorOpen')
, (54, 'TransactionLog')
, (55, 'Hash Warning')
, (56, 'Reserved')
, (57, 'Reserved')
, (58, 'Auto Stats')
, (59, 'Lock:Deadlock Chain')
, (60, 'Lock:Escalation')
, (61, 'OLE DB Errors')
, (62, 'Reserved')
, (63, 'Reserved')
, (64, 'Reserved')
, (65, 'Reserved')
, (66, 'Reserved')
, (67, 'Execution Warnings')
, (68, 'Showplan Text (Unencoded)')
, (69, 'Sort Warnings')
, (70, 'CursorPrepare')
, (71, 'Prepare SQL')
, (72, 'Exec Prepared SQL')
, (73, 'Unprepare SQL')
, (74, 'CursorExecute')
, (75, 'CursorRecompile')
, (76, 'CursorImplicitConversion')
, (77, 'CursorUnprepare')
, (78, 'CursorClose')
, (79, 'Missing Column Statistics')
, (80, 'Missing Join Predicate')
, (81, 'Server Memory Change')
, (82, 'User Configurable (0-9)')
, (83, 'User Configurable (0-9)')
, (84, 'User Configurable (0-9)')
, (85, 'User Configurable (0-9)')
, (86, 'User Configurable (0-9)')
, (87, 'User Configurable (0-9)')
, (88, 'User Configurable (0-9)')
, (89, 'User Configurable (0-9)')
, (90, 'User Configurable (0-9)')
, (91, 'User Configurable (0-9)')
, (92, 'Data File Auto Grow')
, (93, 'Log File Auto Grow')
, (94, 'Data File Auto Shrink')
, (95, 'Log File Auto Shrink')
, (96, 'Showplan Text')
, (97, 'Showplan All')
, (98, 'Showplan Statistics Profile')
, (99, 'Reserved')
, (100, 'RPC Output Parameter')
, (101, 'Reserved')
, (102, 'Audit Statement GDR Event')
, (103, 'Audit Object GDR Event')
, (104, 'Audit AddLogin Event')
, (105, 'Audit Login GDR Event')
, (106, 'Audit Login Change Property Event')
, (107, 'Audit Login Change Password Event')
, (108, 'Audit Add Login to Server Role Event')
, (109, 'Audit Add DB User Event')
, (110, 'Audit Add Member to DB Role Event')
, (111, 'Audit Add Role Event')
, (112, 'Audit App Role Change Password Event')
, (113, 'Audit Statement Permission Event')
, (114, 'Audit Schema Object Access Event')
, (115, 'Audit Backup/Restore Event')
, (116, 'Audit DBCC Event')
, (117, 'Audit Change Audit Event')
, (118, 'Audit Object Derived Permission Event')
, (119, 'OLEDB Call Event')
, (120, 'OLEDB QueryInterface Event')
, (121, 'OLEDB DataRead Event')
, (122, 'Showplan XML')
, (123, 'SQL:FullTextQuery')
, (124, 'Broker:Conversation')
, (125, 'Deprecation Announcement')
, (126, 'Deprecation Final Support')
, (127, 'Exchange Spill Event')
, (128, 'Audit Database Management Event')
, (129, 'Audit Database Object Management Event')
, (130, 'Audit Database Principal Management Event')
, (131, 'Audit Schema Object Management Event')
, (132, 'Audit Server Principal Impersonation Event')
, (133, 'Audit Database Principal Impersonation Event')
, (134, 'Audit Server Object Take Ownership Event')
, (135, 'Audit Database Object Take Ownership Event')
, (136, 'Broker:Conversation Group')
, (137, 'Blocked Process Report')
, (138, 'Broker:Connection')
, (139, 'Broker:Forwarded Message Sent')
, (140, 'Broker:Forwarded Message Dropped')
, (141, 'Broker:Message Classify')
, (142, 'Broker:Transmission')
, (143, 'Broker:Queue Disabled')
, (144, 'Reserved')
, (145, 'Reserved')
, (146, 'Showplan XML Statistics Profile')
, (148, 'Deadlock Graph')
, (149, 'Broker:Remote Message Acknowledgement')
, (150, 'Trace File Close')
, (151, 'Reserved')
, (152, 'Audit Change Database Owner')
, (153, 'Audit Schema Object Take Ownership Event')
, (154, 'Reserved')
, (155, 'FT:Crawl Started')
, (156, 'FT:Crawl Stopped')
, (157, 'FT:Crawl Aborted')
, (158, 'Audit Broker Conversation')
, (159, 'Audit Broker Login')
, (160, 'Broker:Message Undeliverable')
, (161, 'Broker:Corrupted Message')
, (162, 'User Error Message')
, (163, 'Broker:Activation')
, (164, 'Object:Altered')
, (165, 'Performance statistics')
, (166, 'SQL:StmtRecompile')
, (167, 'Database Mirroring State Change')
, (168, 'Showplan XML For Query Compile')
, (169, 'Showplan All For Query Compile')
, (170, 'Audit Server Scope GDR Event')
, (171, 'Audit Server Object GDR Event')
, (172, 'Audit Database Object GDR Event')
, (173, 'Audit Server Operation Event')
, (175, 'Audit Server Alter Trace Event')
, (176, 'Audit Server Object Management Event')
, (177, 'Audit Server Principal Management Event')
, (178, 'Audit Database Operation Event')
, (180, 'Audit Database Object Access Event')
, (181, 'TM: Begin Tran starting')
, (182, 'TM: Begin Tran completed')
, (183, 'TM: Promote Tran starting')
, (184, 'TM: Promote Tran completed')
, (185, 'TM: Commit Tran starting')
, (186, 'TM: Commit Tran completed')
, (187, 'TM: Rollback Tran starting')
, (188, 'TM: Rollback Tran completed')
, (189, 'Lock:Timeout (timeout > 0)')
, (190, 'Progress Report: Online Index Operation')
, (191, 'TM: Save Tran starting')
, (192, 'TM: Save Tran completed')
, (193, 'Background Job Error')
, (194, 'OLEDB Provider Information')
, (195, 'Mount Tape')
, (196, 'Assembly Load')
, (197, 'Reserved')
, (198, 'XQuery Static Type')
, (199, 'QN: subscription')
, (200, 'QN: parameter table')
, (201, 'QN: template')
, (202, 'QN: dynamics')
 
SELECT		FTG.DatabaseName
		, E.event_name
		, FTG.ApplicationName
		, COUNT(*) AS occurences
		, MIN(FTG.StartTime) AS first_time
		, MAX(FTG.StartTime) AS last_time
		, FTG.SessionLoginName
		, E.event_id
FROM		sys.fn_trace_gettable
		(
			(
				SELECT	LEFT(path, LEN(path) - CHARINDEX('\', REVERSE(path))) + '\Log.trc'
				FROM	sys.traces -- read all five TR files
				WHERE   is_default = 1
			)
			, 6
		) AS FTG
LEFT JOIN	@events AS E
			ON E.event_id = FTG.EventClass
WHERE		FTG.EventClass NOT BETWEEN 65527 AND 65528
AND		(
			@database_name IS NULL
			OR FTG.DatabaseName = @database_name
		)
AND		(
			@event_name IS NULL
			OR E.event_name LIKE '%' + @event_name + '%'
		)
AND		(
			@start_date IS NULL
			OR FTG.StartTime >= @start_date
		)
AND		(
			@end_date IS NULL
			OR FTG.StartTime <= @end_date
		)
GROUP BY	FTG.DatabaseName, E.event_name, FTG.ApplicationName, E.event_id, FTG.SessionLoginName
ORDER BY	first_time
OPTION		(RECOMPILE)

Si tu recherches la requête, tu peux démarrer une session d'événements étendus spécifiant l'événement database_file_size_change, ou plus spécifiquement databases_log_file_size_changed.

Il est aussi possible qu'il ait atteint cette taille parce que, malgré les sauvegardes du fichier du journal des transactions, quelque chose l'empêche de marquer l'espace pour réutilisation.
Ce peut être le mirroring ou la réplication, etc ... Tu peux commencer par regarder la colonne log_reuse_wait_desc de la vue sys.databases.

Est-ce que tu as regardé l'historique des sauvegardes du fichier du journal des transactions ?
Peut-être que voir la durée et le volume qu'ils prennent te donnera d'autre indices

@++

[janlouk]

Hello,

Si je comprends bien ton tableau, si je fais un grow de 256 MB, il va créer 8 VLF de 32 MB et pas 1?

On commence à avoir des problèmes à partir de 100.000 VLF si je ne me trompe pas?

[janlouk]

Salut Jean-Luc,

Pour avoir vu quelques bases de données ayant la taille de données que tu donnes, j'ai rarement vu des fichiers du journal des transactions aussi volumineux.
La raison est simple : une sauvegarde du fichier du journal des transactions était prise relativement fréquemment, variant entre 15 et 30 minutes.

Ceci dit, s'il fait cette taille, c'est que quelque chose a causé son grossissement. Il est possible que ce soit une requête de maboule qui soit passée manuellement sur la base, ou la reconstruction d'un index très volumineux.
Les grossissements automatiques de fichiers sont écrits dans la trace SQL Trace par défaut. On peut retrouver les événements avec l'application et le nom de login sous lequel ils se sont produits ... mais pas le texte de la requête.

Salut Nicolas,

Après investigations hier de mon côté et avec l'équipe backup, nous avons donc vu que les 2 derniers backup LOG avaient plantés à 13h10 et 15h10. Et que le client avait relancé plusieurs réindexations également, les 2 ensembles ont fait explosés le log. Ils ont même redémarré l'instance pour vider le log (si si, je n'invente rien...). J'ai vu une table de +- 150 GB dont la taille de tous les indexes faisaient au total 300 Gb.

J'ai donc proposé, comme tu dis, d'augmenter la fréquence des backup log ou augmenter le disque.

Merci pour ta requête, je vais tester ça.

[hmira]

Hello,
Si je comprends bien ton tableau, si je fais un grow de 256 MB, il va créer 8 VLF de 32 MB et pas 1?

OUI, Sous SQL Server 2014, et versions d'avant et selon l'algorithme annoncé des auto-grow :
256 Mo étant comprise entre 64 Mo et 1024 Mo (1 Go), SQL Server créé 8 fichiers VLF de taille homogène, soit chacun de 32 Mo (32 Mo = 256 Mo/8).

On commence à avoir des problèmes à partir de 100.000 VLF si je ne me trompe pas?

On parle plutôt de 10000 VLF. MAIS, il ne faut pas attendre d'avoir 10000 VLF pour agir ! il faut s'occuper et régler le problème des VLF bien en amont.
Lorsque le nombre de VLF atteint 10000, la situation est déjà très préoccupante, voire même catastrophique. Lorsque le nombre de VLF atteint 10000, des messages d'avertissement sont même consignés dans le journal des erreurs après chaque (re)démarrage de l'instance ou après chaque mise en ligne de la base.
Personnellement je surveille et analyse le détails des fichiers Log dès que le nombre de VLF dépasse 50. Cela ne veut nullement dire que 50 fichiers VLF est une valeur limite à ne pas dépasser !
Tout dépend de la taille totale du fichier log, du nombre de fichiers VLF, etc.
Par exemple
Fichier log : 200 Go
Nombre de VLF : 600
Peut être considéré comme configuration correcte en ce qui concerne les VLF, à supposer bien sûr que les fichiers VLF sont dans l'ensemble équilibrés.

A+

[janlouk]

Et pour les fichiers Data, le nombre de grow a-t-il aussi une "grosse" influence?

[hmira]

Et pour les fichiers Data, le nombre de grow a-t-il aussi une "grosse" influence?

Les auto-grow ont un impact néfaste sur les performances.
Idéalement, il faut qu'il n'y ait aucun auto-grow pour les fichier data.
La taille initiale des fichiers data doit être conçue pour une assez longue période d'exploitation (par exemple pour une période entre 3 ans et 5 ans d'activité),
D'autres considérations importantes sont à prendre en compte ; pour cela, je vous conseille vivement la lecture du livre :
SQL Server 2014 - Développer et administrer pour la performance. (Auteurs : Christian Soutou, Frédéric Brouard, Nicolas Souquet, David Barbarin)
http://www.eyrolles.com/Informatique...-9782212135923
https://www.amazon.fr/SQL-Server-201.../dp/2212135920

A+

[janlouk]

Oui oui, ça je sais pour tout ça. Mais je me demandais s'il y avait les mêmes impactes sur les fichiers Data du genre quand il grandissait de 256 MB, il créait aussi plusieurs fichiers d'un coup (même si ce ne sont pas des VLF)
Mais je prendrai le temps dès que possible (même si j'en ai pas vraiment) pour trouver dans le livre (que je possède déjà).
Mais donc pour moi, si les agrandissements ont un mauvais impact. Qu'il n'y a aucun capacity planning, avec un historique de l'augmentation, autant mettre un grow "important" plutôt que de 64 MB pour éviter un maximum le nombre d'agrandissement. Enfin ça me parait logique.

[elsuket]

je me demandais s'il y avait les mêmes impactes sur les fichiers Data du genre quand il grandissait de 256 MB, il créait aussi plusieurs fichiers d'un coup (même si ce ne sont pas des VLF)

Non, les fichiers de données ne sont pas découpés en morceaux logiques.

Supposons un cas très simple et idéal : une seule base de données avec un seul fichier de données et un seul fichier du journal des transactions, chacun sur un disque dédié, et TempDB configurée de même.
Il faudra veiller au nombre de VLF, que l'on peut régler à la création de la base.
On peut tailler le fichier de données et mettre un incrément convenable : comme le disque est dédié, il ne se fragmentera pas physiquement pas les morceaux d'autres fichiers qui se retrouveraient du coup intercalés.

Dans le cas d'un instance SQL Server hébergeant plusieurs bases de données, ce que je pense être plutôt courant, il devient important de tailler le fichiers à l'avance. L'idéal est aussi :

• d'avoir des disques dédiés ...
• en RAID 10 ou 0 + 1
• Le droit Perform Volume Maintenance Tasks octroyé au compte de service SQL Server

Le dernier élément permet d'allouer les incréments de fichiers de données immédiatement (pas de mise à zéro de l'incrément fraîchement alloué).
Voir la littérature sur le sujet : Instant File Initialization.

@++