Base en récupération

Version imprimable

06/03/2017, 12h22
cristophe0071983

2 pièce(s) jointe(s)

Base en récupération

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
06/03/2017, 14h37
SQLpro

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 +
06/03/2017, 14h53
cristophe0071983

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
06/03/2017, 15h10
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 +
06/03/2017, 19h12
Boubou2020

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
08/03/2017, 10h01
cristophe0071983

Citation:

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
08/03/2017, 10h04
cristophe0071983

1 pièce(s) jointe(s)

Citation:

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

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:

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

@++ ;)

09/03/2017, 10h14
janlouk

Citation:

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?
25/03/2017, 11h17
cristophe0071983

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
25/03/2017, 22h43
Boubou2020

Citation:

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/
30/03/2017, 18h29
elsuket
Citation:

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.

@++ ;)
31/03/2017, 14h27
cristophe0071983

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

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

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
-- 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;

31/03/2017, 15h05
elsuket
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
@++ ;)
31/03/2017, 15h27
SQLpro

Citation:

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 +
31/03/2017, 15h52
janlouk

Citation:

Envoyé par SQLpro

NON ! Il n'est pas conseillé d'avoir plus de 200 VLF en pratique

Bon, et bien je ne sais plus où j'ai lu ça :(.
Sorry pour l'erreur.