Base en récupération

**cristophe0071983** · 06/03/2017, 11h22

Bonjour a tous,

J'ai une base de données qui est toujours au statut de récupération.
La base est inaccessible. Tout a débuté après le redémarrage du serveur.

Le moteur entre dans une dernière phase de récupération recovery et il estime 12 h pour rendre la base accessible
Il a pris 5 heures pour faire une opération d'analyse. Qui a une idée sur la cause de cet incident ?

Merci

**SQLpro** · 06/03/2017, 13h37

Cela est sans doute du à une transaction ouverte depuis très longtemps ou une transaction ayant fait beaucoup de travail.

En sus je dirais que vous ne devez pas souvent faire de sauvegarde du journal de transaction et que vous gérez votre serveur pas dessus la jambe !

Y a t-il un DBA dans votre entreprise ?
Le minimum de maintenance a t-il été effectué ? (je parle pas des plans de maintenance graphique qui sont une aberration...)

A +

**cristophe0071983** · 06/03/2017, 13h53

le redémarrage a était fait après l’exécution du plan de maintenance et ce ci pour augmenter le nbre de CPU il s'agit d'une VM

j'ai remarqué que le fichier log a eu un taille de 40GO

Est ce que le faite d'avoir ce taille de Fichier log peuvent provoquer ce type de problème

POUR info je suis en mode de récupération simple

**SQLpro** · 06/03/2017, 14h10

Quelle taille fait la base ?
--> EXC sp_spaceused...
Quand vous parlez d'un plan de maintenance vous parlez je suppose des plans de maintenance graphique ?
Savez vous ce qu'il fait ?

A +

**Boubou2020** · 06/03/2017, 18h12

Tu doit voir si votre Fichier log est fragmenté (Grand nombre de fichiers virtuels)

Avez vous redémarrer SQL pendant une opération très longue et que cette dernière n'a pas été terminé

https://support.microsoft.com/en-us/...server-2008-r2

**cristophe0071983** · 08/03/2017, 09h01

Envoyé par SQLpro

Quelle taille fait la base ?
--> EXC sp_spaceused...
Quand vous parlez d'un plan de maintenance vous parlez je suppose des plans de maintenance graphique ?
Savez vous ce qu'il fait ?

A +

Résultat EXC sp_spaceused

database_name database_size unallocated space
Vproduction 71056.81 MB 6741.13 MB

reserved data index_size unused
55064376 KB 41309328 KB 13559592 KB 195456 KB

oui il s'agit d'un plan de maintenance graphique

**cristophe0071983** · 08/03/2017, 09h04

Envoyé par abdallah_mehdoini

Tu doit voir si votre Fichier log est fragmenté (Grand nombre de fichiers virtuels)

Avez vous redémarrer SQL pendant une opération très longue et que cette dernière n'a pas été terminé

https://support.microsoft.com/en-us/...server-2008-r2

j'ai utiliser la requéte de ELsuket

https://blog.developpez.com/elsuket/...rs_journaux_vi

voici la résultat

**elsuket** · 08/03/2017, 13h41

274 VLF pour 10Go, c'est bon.
La lenteur peut s'expliquer aussi par la lenteur du disque qui supporte le fichier du journal des transactions.
Si la base est maintenant disponible, vous pouvez exécuter la requête suivante : les colonnes avg_read_latency_ms, avg_write_latency_ms et avg_total_latency_ms vous les indiqueront.
En-dessous de 5 c'est très bon, entre 5 et 10 c'est pas trop mal, au-dessus ça l'est beaucoup moins.

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
USE maBase
GO
 
;WITH
	CTE AS
	(
		SELECT		DB_ID() AS database_id
				, DS.name AS filegroup_name
 
				, DF.file_id
				, DF.name AS logical_name
				, DF.physical_name AS physical_name
				, DF.type_desc AS file_type_desc
				, CASE DF.is_percent_growth
						WHEN 0 THEN CASE DF.growth WHEN 0 THEN 'DISABLED' ELSE CAST(DF.growth / 128 AS varchar(20)) + ' MB' END
						ELSE CAST(DF.growth AS varchar(3)) + ' %'
				END AS growth
				, CAST(DF.size / 128.0 AS decimal(14, 2)) AS file_size_MB
				, CAST(FILEPROPERTY(DF.name, 'SpaceUsed') / 128.0 AS decimal(14, 2)) AS occupied_size_MB
				, CASE DF.max_size
					WHEN -1 THEN 'UNLIMITED'
					WHEN 0 THEN 'DISABLED'
					ELSE CAST(CAST(DF.max_size / 128.0 AS bigint) AS varchar(20)) 
				END AS max_size_MB
		FROM		sys.database_files AS DF
		LEFT JOIN	sys.data_spaces AS DS
					ON DF.data_space_id = DS.data_space_id
	)
	, COMPUTATIONS AS
	(
		SELECT		COALESCE(C.filegroup_name, 'TRANSACTION_LOG') AS filegroup_name
				, C.logical_name
				, C.physical_name
				, C.file_type_desc
				, C.growth
				, C.max_size_MB
				, C.file_size_MB
				, C.occupied_size_MB
				, C.file_size_MB - C.occupied_size_MB AS free_space_MB
				, CAST((CAST(C.occupied_size_MB AS numeric(14, 2)) / C.file_size_MB) * 100 AS numeric(14, 2)) AS [%occupied]
				, CAST((CAST(C.file_size_MB - C.occupied_size_MB AS numeric(14, 2)) / C.file_size_MB) * 100 AS numeric(14, 2)) AS [%free]
				, VFS.io_stall_read_ms / VFS.num_of_reads AS avg_read_latency_ms
				, VFS.io_stall_write_ms / CASE VFS.num_of_writes WHEN 0 THEN 1 ELSE VFS.num_of_writes END AS avg_write_latency_ms
				, VFS.io_stall / CASE VFS.io_stall_read_ms + VFS.io_stall_write_ms WHEN 0 THEN 1 ELSE VFS.io_stall_read_ms + VFS.io_stall_write_ms END AS avg_total_latency_ms
				, VFS.num_of_bytes_read / VFS.num_of_reads AS avg_bytes_per_read
				, VFS.num_of_bytes_written / CASE VFS.num_of_writes WHEN 0 THEN 1 ELSE VFS.num_of_writes END AS avg_bytes_per_write
				---
				, VFS.io_stall_read_ms
				, VFS.io_stall_write_ms
				, VFS.num_of_reads
				, VFS.num_of_writes
		FROM		CTE AS C
		INNER JOIN	sys.dm_io_virtual_file_stats(NULL, NULL) AS VFS
					ON C.database_id = VFS.database_id
					AND C.file_id = VFS.file_id
	)
SELECT	*
	, avg_bytes_per_read / NULLIF(avg_bytes_per_write, 0) AS bytes_per_read_over_bytes_per_write_ratio
	, io_stall_read_ms / NULLIF(io_stall_write_ms, 0) AS io_stall_read_over_io_stall_write_ratio
FROM	COMPUTATIONS
ORDER	BY logical_name

@++

**janlouk** · 09/03/2017, 09h14

Envoyé par elsuket

Si la base est maintenant disponible, vous pouvez exécuter la requête suivante : les colonnes avg_read_latency_ms, avg_write_latency_ms et avg_total_latency_ms vous les indiqueront.
En-dessous de 5 c'est très bon, entre 5 et 10 c'est pas trop mal, au-dessus ça l'est beaucoup moins.

Salut Nicolas,

Merci pour ta query, je l'ai testé aussi et je vois sur une base de dev que l'avg_read et à 57 (data) et 36 (log) et pour le write 8 et 12.

Je demande ça plus pour savoir comment "réagir" car j'ai testé sur une grosse base de prod (6.5 TB) et le read est entre 4 et 9 en fonction des fichiers et 1 pour le write pour les 9 fichiers.

Comment doit-on réagir à ce genre de valeur?

**cristophe0071983** · 25/03/2017, 10h17

j'arrive pas a comprendre c'est quoi la bonne pratique pour diminuer le nbre des Fichier VLF

la premier conseil dit que pour diminuer le nombre de VLF il faut faire un shrink complet du journal

alors que l'opération du shrink entraîne la fragmentation du fichier log donc va entraîner systématiquement l’augmentation du nbre des Fichier VLf

une explication de nos expert serai bienvenu

merci

**Boubou2020** · 25/03/2017, 21h43

Envoyé par cristophe0071983

j'arrive pas a comprendre c'est quoi la bonne pratique pour diminuer le nbre des Fichier VLF

1-Sauvegardez périodiquement votre journal afin d'éviter toute opération de croissance

2-dimensionner correctement votre Fichier log (20 % de la taille des données)

3-Définissez la taille de la croissance du journal des transactions fixer le par exemple a 1 Go

4-voir la possibilité de mettre un raid 10 ou 0 + 1 pour les disk log

un excellent article sur le sujet http://www.sqlskills.com/blogs/kimbe...og-throughput/

**elsuket** · 30/03/2017, 17h29

a premier conseil dit que pour diminuer le nombre de VLF il faut faire un shrink complet du journal
alors que l'opération du shrink entraîne la fragmentation du fichier log donc va entraîner systématiquement l’augmentation du nbre des Fichier VLf

Bonjour,

Attention à ne pas faire de confusions :

Quel que soit le type de fichier, un rétrécissement doit rester une opération exceptionnelle
Le rétrécissement du fichier du journal des transactions n'entraîne pas sa fragmentation, sauf peut-être lorsqu'on le fait grossir à nouveau ensuite

L'idée lorsqu'on fait un rétrécissement du fichier du journal des transactions pour réduire le nombre de VLF, c'est de forcer le moteur a découper logiquement ce fichier à nouveau.
Si on a trop peu de VLF, alors pour qu'il soit tronqué, il faut un grand nombre de transactions, ce qui pose problème en cas de crash : les transactions entre le moment du dernier backup du journal des transactions et le crash peuvent être perdues. Et si on en a trop, alors certaines opérations d'écriture, et les opérations de récupération de la base de données peuvent s'avérer être laborieuses.

Je trouve que lorsqu'on rétrécit le fichier du journal des transactions à fond, puis qu'on le fait immédiatement grossir ensuite, par incréments d'un gigaoctet, on a une bonne balance entre ces deux contraintes.

@++

**cristophe0071983** · 31/03/2017, 13h27

bonjour elsuket et merci pour votre réponse

en lisant attentivement votre réponse j'était devant cette jolie phrase 274 VLF pour 10Go, c'est bon

je veux s'avoir comment tu a arriver a me confirmer que c'est bon aurai t'il une recommandation mis par microsoft

exit't 'il une formule mathématique pour faire ce type de mesure nbre du Fichier VLf par rapport a la taille du Fichier log

merci

**janlouk** · 31/03/2017, 13h40

On commence à avoir des problèmes si on a plus de 10.000 VLF

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
-- Pour connaitre le nombre de VLF sur toutes les DB:
-- Script pour 2012. Avant 2012, il faut retirer la colonne "RecoveryUnitId"
CREATE TABLE #VLF (DATABASE_NAME     nvarchar(128), 
                    RecoveryUnitId    INT, 
                    FileId            smallint, 
                    FileSize          float, 
                    StartOffset       bigint,
                    FSeqNo            int, 
                    Status            tinyint,
                    Parity            smallint,
                    CreateLSN         BINARY(16));
DECLARE @DBN NVARCHAR(128), @SQL NVARCHAR(200);
DECLARE C CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY
FOR
   SELECT name FROM sys.databases WHERE source_database_id IS NULL;
OPEN C;
FETCH C INTO @DBN;
WHILE @@FETCH_STATUS = 0
BEGIN
   SET @SQL = N'USE [' + @DBN + N']; DBCC LOGINFO';
   INSERT INTO #VLF (RecoveryUnitId, FileId, FileSize, StartOffset, FSeqNo, Status, Parity, CreateLSN)
   EXEC (@SQL);
   UPDATE #VLF SET DATABASE_NAME = @DBN WHERE DATABASE_NAME IS NULL;
   FETCH C INTO @DBN;
END 
CLOSE C;
DEALLOCATE C;
 
SELECT DATABASE_NAME, COUNT(*) AS NB_VLF, 
       MIN(FileSize) AS VLF_MIN_SIZE_MB, 
       FLOOR(AVG(FileSize)) AS VLF_AVG_SIZE_MB, 
       MAX(FileSize) AS VLF_MAX_SIZE_MB,
       CASE Status WHEN 0 THEN 'PAST' ELSE 'ACTIVE' END
FROM   #VLF
GROUP  BY DATABASE_NAME, CASE Status WHEN 0 THEN 'PAST' ELSE 'ACTIVE' END
ORDER  BY DATABASE_NAME, NB_VLF DESC;
 
DROP TABLE #VLF;

**elsuket** · 31/03/2017, 14h05

Jusquà SQL Server 2012 inclus, la règle est la suivante, pour le découpage de l'incrément du fichier, ou lors de sa création :

Entre 2 et 64Mo : 4 VLFs
Entre 64Mo et 1Go : 8 VLFs
Plus d'1Go : 16 VLFs

A partir de SQL Server 2014 inclus, la règle est la suivante :

Est-ce que la taille du grossissement est supérieure au 1/8e de la taille du fichier actuelle ?
Si oui, l'incrément n'a qu'un seul VLF
Si non, utilisation de la règle ci-dessus

@++

**SQLpro** · 31/03/2017, 14h27

Envoyé par janlouk

On commence à avoir des problèmes si on a plus de 10.000 VLF

NON ! Il n'est pas conseillé d'avoir plus de 200 VLF en pratique si l'on veut avoir de bonnes performances.
Donc à 274 c'est acceptable, sans plus.
À 10 000 c'est une catastrophe => 10 000 descripteurs de fichiers en mémoire, 10 000 chainages... etc... !

A +