Base de données qui n'arrête pas d'augmenter malgré l'espace libre

**olivtone** · 04/01/2016, 11h43

Bonjour a Tous

Un truc que j'arrive a piger

j'ai une BDD qui fait 270 Go avec 4 Go d'espace libre

logiquement SQL server devrait prendre cette place disponible pour eviter et arreter de faire son autogrow, mais ce n'est pas le cas,j'arrive pas a piger pourquoi ...

Autre question :

On a une operation prochainement de la purge de cette BDD, on devrait gagner 70 Go

Si je fais un SHRINK de cette BDD, il y aura juste de la fragmentation au niveau des index ? Cela va t il creer de la fragmentation disque?

Si je fais l'autoshrink de la BDD avant les REBUILD INDEX cela devrait etre ok ?

merci a tous pour votre aide

**rudib** · 04/01/2016, 11h48

Bonjour,

Que donne le résultat de cette requête ?

Code :

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

DBCC SQLPERF (LOGSPACE)

pour voir si le problème vient de ton journal de transactions... Ou tu es sûr que c'est le fichier de données qui te pose problème ?

**olivtone** · 04/01/2016, 14h03

Merci de ton retour

Oui j'y avais pense deja le logspace est tres faible (0,5 % )

**mikedavem** · 04/01/2016, 16h09

Envoyé par olivtone

logiquement SQL server devrait prendre cette place disponible pour eviter et arreter de faire son autogrow, mais ce n'est pas le cas,j'arrive pas a piger pourquoi ...

On parle d'espace libre ou d'espace réservé non utilisé? Qu'utilises-tu pour dire que c'est de l'espace libre que tu as?

++

**olivtone** · 04/01/2016, 17h26

Je parle d'espace libre dans la BDD :

Dans les proprietes de la BDD :

Taille : 270778,00*Mo
Espace disponible : 3881,38*Mo

**mikedavem** · 04/01/2016, 18h11

Ok et côté autogrow :

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
DECLARE @path NVARCHAR(260);
 
SELECT 
   @path = REVERSE(SUBSTRING(REVERSE([path]), 
   CHARINDEX('\', REVERSE([path])), 260)) + N'log.trc'
FROM    sys.traces
WHERE   is_default = 1;
 
SELECT 
   DatabaseName,
   [FileName],
   SPID,
   Duration,
   StartTime,
   EndTime,
   FileType = CASE EventClass 
       WHEN 92 THEN 'Data'
       WHEN 93 THEN 'Log'
   END
FROM sys.fn_trace_gettable(@path, DEFAULT)
WHERE
   EventClass IN (92,93)
ORDER BY
   StartTime DESC;

++

Invité · 04/01/2016, 19h16

Quel est le modèle de recouvrement de ta base de données ?
Si ce n'est pas SIMPLE, est-ce que la sauvegarde du journal de transaction est faite régulièrement ?

**rudib** · 05/01/2016, 09h34

Envoyé par olivtone

Merci de ton retour

Oui j'y avais pense deja le logspace est tres faible (0,5 % )

Hello. 0,5 % de combien ? As-tu lancé la commande proposée ? Quel est le résultat ?

**olivtone** · 05/01/2016, 10h03

LA BDD est en mode FULL, il y a une sauvegarde des logs toutes les heures

le DBCC sqlperf(logspace) donne =>

Log Size (MB) =>28768,74 (MB)
Log space User (%) =>0,5688004

**SQLpro** · 05/01/2016, 12h08

Donc vous utilisiez 0,5 % d'un journal de transaction de 29 Go, soit environ 150 Mo au moment ou vous avez lancé la requête. Votre base faisant 270 Go le fichier du JT devant se situer à environ 20 % de la taille globale de la base, même ce chiffre de 29 Go est assez faible...

Quels sont les paramètres de croissance de vos fichiers ? (taille d'incrément et mode)

Que donne la commande

Code :

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

EXEC sp_spaceused

dans le contexte de la base...

A +

**olivtone** · 05/01/2016, 14h35

merci fred de ton retour

sp_spaceused donne :

database_size =>272826.00 MB
unallocated space => 2854.57 MB

**SQLpro** · 06/01/2016, 08h42

Donnez nous TOUT ce que donne sp_spaceused !

**olivtone** · 06/01/2016, 10h22

Nom : Capture.JPG
Affichages : 530
Taille : 23,2 Ko

**SQLpro** · 06/01/2016, 11h40

Vous n'avez globalement que 3 Go d'espace libre récupérable sur 274 Go, soit environ 1%... Que voulez-vous faire ???

C'est déjà une mauvaise pratique d'avoir aussi peu d'espace libre.
Quand j'audite des bases je considère que l'espace libre minimal des données devrait être d'environ 30%... Et celui du JT bien plus !

Je vous conseille de vous former à l'utilisation de SQL Server et dans votre cas de suivre un cours Admin et de lire le livre que nous avons écrit :
Nom : Couverture livre SQL server Eyrolles.jpg
Affichages : 460
Taille : 105,0 Ko

Nom : Couverture livre SQL server Eyrolles.jpg
Affichages : 460
Taille : 105,0 Ko

A +

**rudib** · 07/01/2016, 13h45

Ok, on a plus d'infos maintenant.

Envoyé par olivtone

logiquement SQL server devrait prendre cette place disponible pour eviter et arreter de faire son autogrow, mais ce n'est pas le cas,j'arrive pas a piger pourquoi ...

A quel moment fait-il son autogrow, et comment cet autogrow est-il configuré ? En quoi cela te pose-t-il une problème. Le compte de service de SQL Server a-t-il le droit dde faire une initialisation instantanée ?
https://technet.microsoft.com/fr-fr/...ql.105%29.aspx

Envoyé par olivtone

Si je fais un SHRINK de cette BDD, il y aura juste de la fragmentation au niveau des index ? Cela va t il creer de la fragmentation disque?

Au moins fragmentation des index, oui. Un SHRINK administratif contrôlé est ok, ce qui pose problème c'est l'autoshrink ou des shrink planifiés régulièrement.

Envoyé par olivtone

Si je fais l'autoshrink de la BDD avant les REBUILD INDEX cela devrait etre ok ?

Justement pas un autoshrink, mais une commande DBCC SHRINKFILE scriptée et ensuite un rebuild des index fragmentés sélectivement avec par exemple le script d'Ola Hallengren : https://ola.hallengren.com/sql-serve...intenance.html

**olivtone** · 07/01/2016, 17h39

Merci beaucoup de vos retours rudib et SQLpro c'est cool vraiment

Rudib :

J'ai un autogrow de 1024 Mo en pas d'incrementation, et j'ai bien activé l'IFI en local sur le serveur et le compte de service a bien les droits

Le Shrink sera vraiment execptionnel, on ne l'a jamais fait avant.

Mon but etait de faire un SHRINK de la BDD et enchainer avec les rebuild Index derriere

Ma grande crainte etait au niveau Fragmentation disque, mais je ne trouve pas trop d'informations la dessus...

**mikedavem** · 08/01/2016, 08h33

Envoyé par rudib

Ok, on a plus d'infos maintenant.
A quel moment fait-il son autogrow, et comment cet autogrow est-il configuré ? En quoi cela te pose-t-il une problème. Le compte de service de SQL Server a-t-il le droit dde faire une initialisation instantanée ?
https://technet.microsoft.com/fr-fr/...ql.105%29.aspx

Je pense que la question est vraiment pertinente ici. Je remets la requête à disposition au cas où. Tu pourras essayer de faire un recoupement avec les heures concernées

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
DECLARE @path NVARCHAR(260);
 
SELECT 
   @path = REVERSE(SUBSTRING(REVERSE([path]), 
   CHARINDEX('\', REVERSE([path])), 260)) + N'log.trc'
FROM    sys.traces
WHERE   is_default = 1;
 
SELECT 
   DatabaseName,
   [FileName],
   SPID,
   Duration,
   StartTime,
   EndTime,
   FileType = CASE EventClass 
       WHEN 92 THEN 'Data'
       WHEN 93 THEN 'Log'
   END,
   ApplicationName,
   HostName,
   LoginName
FROM sys.fn_trace_gettable(@path, DEFAULT)
WHERE
   EventClass IN (92,93)
ORDER BY
   StartTime DESC;

Ma grande crainte etait au niveau Fragmentation disque, mais je ne trouve pas trop d'informations la dessus...

Si tu parles de fragmentation NFTS alors oui les extensions de fichiers sporadiques peuvent effectivement engendrer une fragmentation à ce niveau.
Maintenant au niveau disque tout dépend le stockage que tu as .. en général sur du stockage de type SAN ou de type JBOD avec cartes contrôleurs, ce n'est plus l'OS qui gère le placement des blocs sur disque mais l'intelligence du stockage lui même.
Si tu es dans ce cas, la fragmentation disque du point de vue OS ne veut plus vraiment significative.

++

**janlouk** · 08/01/2016, 08h57

Bonjour David,

Ta query donne l'heure, et le fichier, sur lequel s'est passé un autogrow pour toutes les DB sur une instance c'est bien cela ? Car sur une de mes instances, je suis étonné d'avoir autant de lignes et de voir plusieurs lignes pour la même DB (sur le log par exemple).

En cherchant, je remarque que j'avais un peu prêt la même, mais je ne sais plus chez qui je l'ai choppé

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
DECLARE @path NVARCHAR(1000)
SELECT @path = Substring(PATH, 1, Len(PATH) - Charindex('\', Reverse(PATH))) +
                      '\log.trc'
FROM   sys.traces
WHERE  id = 1
SELECT databasename,
       e.name   AS eventname,
       cat.name AS [CategoryName],
       starttime,
       e.category_id,
       loginname,
       loginsid,
       spid,
       hostname,
       applicationname,
       servername,
       textdata,
       objectname,
       eventclass,
       eventsubclass,
          (IntegerData*8.0/1024) as ChangeInSizeMb,
          Duration/1000 as DurationInMs
FROM   ::fn_trace_gettable(@path, 0)
       INNER JOIN sys.trace_events e
         ON eventclass = trace_event_id
       INNER JOIN sys.trace_categories AS cat
         ON e.category_id = cat.category_id
WHERE  e.name IN( 'Data File Auto Grow', 'Log File Auto Grow' )
ORDER  BY starttime DESC

L'idéal ce serait donc d'avoir aucun résultat ? Ou alors ça donne les fichiers qui ont augmenté mais pas forcément eu un autogrow ? (je ne sais pas si je suis clair

)

**mikedavem** · 08/01/2016, 14h20

Hello,

Ta query donne l'heure, et le fichier, sur lequel s'est passé un autogrow pour toutes les DB sur une instance c'est bien cela ?

C'est bien cela. En fait, elle va simplement chercher dans la trace par défaut les eventclass 92 et 93 qui correspondent à des autogrow de fichiers de données et de journaux de transactions.
C'est d'aileurs ce que fait SSMS lorsqu'on lance le rapport "Disk Usage".

L'idéal ce serait donc d'avoir aucun résultat ? Ou alors ça donne les fichiers qui ont augmenté mais pas forcément eu un autogrow ? (je ne sais pas si je suis clair )

Parfaitement clair

. Cela ne concerne que les autogrow. Dans l'absolu oui c'est mieux car cela voudrait dire que l'espace disque réservé pour les fichiers de bases de données est bien maitrisée. Mais bon pour être peut être plus réaliste, avoir des événements n'est pas forcément gênant à partir du moment où les autogrow ne soient pas la règle mais une exception.

++