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 % )

**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 ?

**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 ?

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

**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.

++