Discussion :

[nesswaw]

Bonjour,

J'ai une base qui fait 45Go quand j’exécute la requête suivante:

Code :

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

1
2
3
 
USE MaBase
EXEC sp_spaceused

Taille des fichiers:

- Base: 30Go
- Log: 16Go

Dans cette base j'ai fait une grande purge des enregistrements, via l'application qui exploite cette base

Cette nuit les backups de la base ainsi que des logs se sont fait.

Ce matin quand je vérifie les tailles, rien n'a bouger...même pas d'un KO...

Il y a juste "unallocated space" qui est passé de 8728 MB à 11928 MB

Que dois-je faire pour réduire la taille de ma base et des fichiers?
On m'as toujours dit qu'un shrink était déconseillé?

Merci d'avance

[SQLpro]

Dans cette base j'ai fait une grande purge des enregistrements, via l'application qui exploite cette base

Cette nuit les backups de la base ainsi que des logs se sont fait.

Ce matin quand je vérifie les tailles, rien n'a bouger...même pas d'un KO...

C'est parfaitement normal. Quand vous purgez un radiateur diminue t-il de volume ?

Il y a juste "unallocated space" qui est passé de 8728 MB à 11928 MB

Que dois-je faire pour réduire la taille de ma base et des fichiers?

Supprimez les espaces vide des fichiers via une commande DBCC SHRINK...

On m'as toujours dit qu'un shrink était déconseillé?

Oui, c'est stupide et contre performant....

Lisez ceci :
http://blog.developpez.com/sqlpro/p5..._fichiers_et_t
et en particulier le paragraphe intitulé "Pire ! C’est possible…"

Et pour parfaire votre culture :

[nesswaw]

Merci pour votre aide.

Donc faire DBCC SHRINK est égal à un simple shrink? Donc pas faire si je comprend bien?

Dans votre article, vous dites qu'il faut faire une base de taille fixe, si par exemple je fait une base de 1Go (aussi log à 1Go), et que dans 2 ans ma base fera 2Go, que va-t--il se passer?

Par contre pour le fichier de log, si atteint les 1 Go, il va se vider? Ou des problèmes de perf à cause que pas assez grand?

Merci pour ces explications

PS: J'ai déjà hésité plusieurs fois à acheter ce bouquin, je vais sauter le pas, ça va pas me faire de mal

[janlouk]

Dans votre article, vous dites qu'il faut faire une base de taille fixe, si par exemple je fait une base de 1Go (aussi log à 1Go), et que dans 2 ans ma base fera 2Go, que va-t--il se passer?

Si vous savez que dans 10 ans, votre DB fera 10 GB, alors il est conseillé de mettre cette taille afin qu'il y ait un minimum d'autogrowth. Mais rien ne vous empêche de mettre 2 Go avec un autogrowth de 500 MB.

Par contre pour le fichier de log, si atteint les 1 Go, il va se vider? Ou des problèmes de perf à cause que pas assez grand?

Il va se vider si vous faites un backup log, mais se vider ne veut pas dire se réduire (comme l'exemple du radiateur).

Ps : C'est un très bon livre, je l'ai, mais je dois prendre le temps de le lire . Mais les vidéos lors des JSS et techdays sont vraiment bien aussi. En voici une petite partie : http://www.datacrossroad.be/best-pra...l-server-2012/

[janlouk]

Donc faire DBCC SHRINK est égal à un simple shrink? Donc pas faire si je comprend bien?

DBCC Shrink est la commande pour faire le shrink, c'est une autre manière que de le faire via l'interface de SSMS.

Il ne faut pas le faire si vous savez que votre DB grandira un jour ou l'autre. C'est ce que veut dire Frédéric, pourquoi diminuer la taille d'un fichier que l'on sait qu'un jour il reviendra à cette taille là.

[nesswaw]

Si vous savez que dans 10 ans, votre DB fera 10 GB, alors il est conseillé de mettre cette taille afin qu'il y ait un minimum d'autogrowth. Mais rien ne vous empêche de mettre 2 Go avec un autogrowth de 500 MB.

Comment savoir la taille dans 10ans pour un nouveau projet? C'est pratiquement impossible, le logiciel évolue, le nombre de personne qui l'utilise aussi, l'institution évolue, on ne peut savoir l'état dans 10ans, non? On peut estimer, mais tellement de paramètres rentrent en compte, que le risque d'erreurs est énorme, les surprises en informatique c'est la base du métier

Ma question est comment gérer cette erreur? Peut-on faire une base de 1Go et l'augmenter à 2Go si on voit qu'on va bientôt manquer de place?

Merci

[janlouk]

Oui bien sûr, difficile de savoir, mais en sachant ce qu'insert l'application qui rempli la DB, on peut faire une petite estimation. Rien n'empêche de commencer à 2 GB, mettre un autogrowth de 250 MB et voir dans 1 mois ou 2, l'évolution de la DB.

Si vous voyez qu'en 2 mois, l'espace utilisée est de 500 MB, vous savez que 2 GB ne sera pas assez. Encore une fois, c'est du best practice. Car lorsque la table grandit, cela peut provoquer des ralentissements, des verroux et donc des blocked process.

[nesswaw]

Mais donc, si je 2Gb avec autogrowth de 250mb, cela veux dire que une fois arrivé à 2Go, mon fichier va grossir à 2250mb puis 2500mb puis 2750mb?

Par contre est-ce possible de mettre 2Gb SANS autogrowth, et de le passer à 4Go manuellement une fois arrivé proche des 2Go?

Au final comme dans un hdd dans Windows, on met 60Go, une fois remplit on fait un extend de 20Go pour passer à 80Go

Merci

[SQLpro]

Chaque fois que vous allez faire une opération d'extension de fichier vous fragmentez de manière irrémédiable le fichier. Que ce soit manuellement ou de manière automatique...

Qu'est ce qui vous gène dans le fait de devoir tailler la base pour 5 ans ?
Vous avez peur de prendre trop d'octets ??
Votre comptable va vous tomber dessus et vous les facturer ???

Le capacity planning est la chose la plus importante pour les performances...

A +

[elsuket]

Chaque fois que vous allez faire une opération d'extension de fichier vous fragmentez de manière irrémédiable le fichier

Sauf si le volume qui supporte le fichiers de données de la base de données est dédié, ce que d'ailleurs tu recommandes ici

@++

[SQLpro]

Sauf si le volume qui supporte le fichiers de données de la base de données est dédié, ce que d'ailleurs tu recommandes ici

@++

Et même dans ce cas, car comme il prends des cylindres sur les disques, si tue dédie un disque et que tu fais de multiples accroissements, il y a fragmentation !!!!

C'est l'inconvénient de l'optimisation qui consiste à prendre des cylindres sur les disques.....

A +

[nesswaw]

Chaque fois que vous allez faire une opération d'extension de fichier vous fragmentez de manière irrémédiable le fichier. Que ce soit manuellement ou de manière automatique...

Qu'est ce qui vous gène dans le fait de devoir tailler la base pour 5 ans ?
Vous avez peur de prendre trop d'octets ??
Votre comptable va vous tomber dessus et vous les facturer ???

Le capacity planning est la chose la plus importante pour les performances...

A +

Et y a pas de solution à ces problèmes de fragmentation? Genre une opération de maintenance qui fait un "defrag"?

Ça me dérange pas de prévoir une taille de base de 20,50,270Go, ce qui me dérange c'est de pas avoir de solution une fois qu'on aura atteint ces tailles?

[SQLpro]

Et y a pas de solution à ces problèmes de fragmentation? Genre une opération de maintenance qui fait un "defrag"?

Non, car c'est une fragmentation de fichier et SQL Server ne peut pas la gérer c'est à l'OS de le faire ! Or l'OS peut faire de la defrag de fichier, mais hélas son algo de defrag est linéaire et il ne sait pas créer des fichiers en utilisant les cylindres du disque. Donc à moins que vous ne soyez en SSD, pas de solution simple (je crois qu'il existe des outils de defrag intégrant la gestion des cylindres des plateaux des disques, mais j'ai pas de ref et j'ai jamais testé la chose.... de toute façon ça risque d'être risqué et long donc incompatible avec une prod 24/24 7/7...

Ça me dérange pas de prévoir une taille de base de 20,50,270Go, ce qui me dérange c'est de pas avoir de solution une fois qu'on aura atteint ces tailles?

Si il existe toujours la solution de migration.
Tu créé un nouveau groupe de fichier avec la dimensions souhaité et tu migre les tables dedans en utilisant CREATE INDEX .... WITH (DROP_EXISTSING = ON)

Voir notre livre sur SQL Server qui en parle dans le chapitre 10 réservé au stockage



A +

[janlouk]

Non, car c'est une fragmentation de fichier et SQL Server ne peut pas la gérer c'est à l'OS de le faire !

Si par exemple on a eu souvent des agrandissements et donc de la fragmentation. Et est-ce que cela résoud le problème si on augmente la taille des fichiers pour sa durée de vie, de copier les fichiers et les mettre sur un autre disque ? Il n'y aura plus de fragmentation ? Je parle de ça en cas de migration.

Copier les fichiers, ou alors faire ensuite un backup et puis restore sur un autre serveur ?

[SQLpro]

Si par exemple on a eu souvent des agrandissements et donc de la fragmentation. Et est-ce que cela résoud le problème si on augmente la taille des fichiers pour sa durée de vie

Non...

, de copier les fichiers et les mettre sur un autre disque ?

Non, pas mieux

Il n'y aura plus de fragmentation ?

Si !

Je parle de ça en cas de migration.

Copier les fichiers, ou alors faire ensuite un backup et puis restore sur un autre serveur ?

Toujours pas mieux !

Lisez mon livre au chapitre du stockage et vous comprendrez que SQL Server utilise des routines particulières pour structurer ses fichiers en fonction de l'organisation des disques et des plateaux des disque, ce que ne fait pas Windows par défaut (car ce serait très pénalisant pour de petites fichiers).

Donc le seul moyen d'avoir l'absolue certitude d'une non fragmentation desdits fichiers est de les créer de la bonne taille dès le départ !

Cela dit avec quelques sauf de fragmentation physique de vos fichiers n'est pas un gros problème ! Le tout c'est d'en avoir le moins possible...

A +

[nesswaw]

Bonjour,

Pour en revenir sur le sujet, je dois créer une base de données, je vais donc essayer de suivre vos bonnes pratiques, en mettant une taille fixe.

La taille de la base devrait atteindre 300mb d'ici 3 ans.

Je vais donc créer une base de 500mb

Pour le fichier des logs, je dois faire quelle taille?

Et pour les paramètres de l'autogrowth je dois mettre quoi? Pour la base et les logs

Merci d'avance

[StringBuilder]

Vu le prix du Go, si la base doit faire 300 Mo "au final" (donc j'imagine après une activité de plusieurs mois seulement), autant miser plus large, genre 2 Go minimum.

Pour les logs, c'est plus délicats. Tout dépend :
- De l'interval entre deux backups
- Du volume de données modifiées entre deux backups
- De la présence de traitements de masse

D'une base à l'autre,j'ai parfois un LOG jusqu'à 20 fois plus que que DATA, alors que généralement il est autour de 2 Go (quelle que soit la taille du DATA) avec des backups toutes les 10 minutes.

[nesswaw]

Les backups se font tout les 12 heures.

J'ai vu une bonne pratique qui dit de mettre la moitié de la taille de la Db pour les logs.
Ainsi que aussi la moitié pour l'autogrowth, et ne pas mettre en %

Exemple:

Fichier Base: 2Go, autogrowth de 1Go
Fichier Log: 1Go, autogrowth de 500Mo

Qu'en pensez-vous?

Dans ce cas ici pour une base, dont les données ne vont pratiquement pas bouger, une fois entrée les données de base, il y aura très peu de transactions.

Merci

[StringBuilder]

J'en pense que je répète ce que je viens de dire (enfin, hier)

Tout dépend de vos données et de votre outil.

Imaginons une base de données type "pages blanches".

Dedans, on y retrouve tous les noms, adresse et téléphone fixe de tous les français.
Ca représente donc un certain volume.

En moyenne, je dirais que 1% de la population change de numéro de téléphone et/ou d'adresse en 1 an.
Ce qui donne, par jour, 1% / 365 de la taille des données qui sont effectivement modifiées.

Donc pour une base de 100 Go, on peut estimer que journalièrement, on a un fichier de log d'environ 0,0027 Go, soit de 3 Mo.

Donc même si les données sont mises à jour non pas au jour le jour, mais à la petite semaine, on va difficilement dépasser les quelques dizaines de LOG entre deux backups. Aucun intérêt d'allouer 100 Go, 1 Go sera déjà un gaspillage énorme.

En revanche, mettons qu'on déci d'ajouter les numéros de téléphone et fax, là, 100% des données vont être modifiées, potentiellement plusieurs fois (1 fois pour mettre le mobile, une seconde fois pour mettre le fax, etc.)
=> potentiellement, lors de cette maintenance exceptionnelle, on aura besoin d'un LOG qui va faire non pas 3 Mo, ni 1 Go, mais peut être 500 Go ! Hors de question dans ce cas là de laisser SQL Server rajouter 10% + 10% pour arriver à 500 Go en partant de 3 Mo !
On créera donc un fichier spécialement pour l'occasion, qu'on détruira une fois la maintenance terminée.


Second exemple. On est à Well Street, et les traders doivent stocker les changements de valeurs des actions du marché en temps réel.
Soit des dizaines de millions de lignes par minute.
Ils ne gardent pas d'historique : tout ce qui leur importe, c'est la donnée au moment où ils passent leur ordre.
=> Donc on a une base de mettons 10 Go, donc 100% des données est mise à jour toutes les 60 secondes.
Avec un backup des logs toutes les 24 heures, on a un LOG qui va arriver à la jolie taille de 24 * 60 * 10 = 14 400 Go, soit 14 To !
On est donc loin de la moitié de 10 Go...

Bref, analysez la taille de votre base de données, la taille des mouvements qui sont fait dessus, et prenez une marge (genre x2) pour déterminer la taille de vos LOG.

Si accroissement il y a, augmentez au moins de 100% leur taille à la fois : à l'image des pages blanches, si d'un coup vous faites plus du double des modifications de d'habitude, il est fort à parier que vous allez largement dépasser 10% au final. Donc ne pénalisez pas le serveur à redimensionner sans arrêt.
Après la charge inhabituelle, il sera toujours temps de faire un shrink pour retrouver une taille "normale". Seul cas où le shrink est utile.

[nesswaw]

Bonjour,

Merci pour votre réponse, en effet pas facile...

Une autre question, si je veux modifier les paramètres de cet autogrowth, je peux le faire direct depuis l'interface? (Voir image)

- Augmenter le fichier initial size à plusieurs go
- Augmenter l'autogrowth

Il faut faire des opérations spéciales, une fois "le resize" fait?

Merci d'avance