Comment réduire la taille des fichiers

**papao** · 08/08/2016, 14h47

Bonjour à tous,

Je suis nouveau à l'administration de base de données SQL-Server, je travaille avec une équipe que je viens d'intégrer, du coup, je viens vers vous pour demander de l'aide car personne ici n'a jamais administrer la base existante!

J'ai lus pas mal d'article de SQLPro, et il dit souvent que shrink n'est pas très recommandé, qui sous entend que réduire la taille des fichiers n'est pas vraiment très recommandé sous SQL-Server. Alors je suis un peut bloqué là dessus car, j'ai absolument besoin de réduire la taille de mes fichiers. Voici ce que j'ai :

J'ai 5 tables partitionnées sur une colonne de type smalldatetime, et le regroupement par fichiers est de mm/yyyy. J'ai des données depuis 2011 jusqu'à aujourd'hui, et chaque fichiers fait 250 Go, les données de ces tables ne sont JAMAIS mises à jour ni supprimées, à un certain moment, un peut lointain, l'une des tables a été supprimée car il n'est plus utilisée, ceci a laisser près de 25Go d'espace logique disponible par fichier de partition, alors je voudrais récupérer ces 25Go par fichiers, et lorsque j'ai essayer le shrink, c'est pas du tout gagné, j'ai laissé tourner le script tout le week end et là, il est toujours à 1.235%. je ne sais plus quoi faire, alors que j'ai besoin de récupérer ces 25Go x 48mois.

Quel moyen est le plus rapide pour récupérer mes espaces.
Par avance
Merci

**elsuket** · 08/08/2016, 21h31

Bonjour,

Effectivement un rétrécissement des fichiers automatisé / systématique est proscrit. Ceci est du au fait qu'une base de données vit, donc les fichiers qui la supportent grossiront inéluctablement dans la plupart des cas.

Si je vous comprends bien, vous passez de 250 à 225Go d'espace occupé dans les fichiers de données. Pouvez-vous nous indiquer comment vous avez vérifié ce taux d'occupation SVP ?
Vous dites que ces 250Go représentent 5 ans de données, ce qui implique qu'on écrit 50Go par an. En ce cens, il n'est pas avantageux de réduire la taille de votre fichier de données, puisque tôt ou tard, et visiblement bientôt, vous allez atteindre à nouveau les 250Go, et même les dépasser. S'il s'agit donc de récupérer un peu d'espace dans l'attente de l'achat d'espace disque supplémentaire parce que la base de données y tient tout juste, comme les fichiers de données ne grossiront visiblement pas, c'est cause perdue.
Comme vous avez des partitions dont les données ne sont ni mises à jour ni supprimées, vous pouvez les passer en lecture seule.

Quelle est l'instruction SHRINK que vous avez utilisé ?
Dans SQL Server, les données sont stockées dans des pages de 8Ko. L'allocation de l'espace est cartographié dans des pages dédiées.
Cette instruction travaille fichier par fichier (un seul à la fois), et recherche dans ces pages de gestion d'allocation les pages de données qui ont le plus grand numéro d'allocation.
Elle les déplace ensuite le plus près logiquement du "début" du fichier en cours de traitement. Ceci explique que la durée de l'opération de rétrécissement est longue. Elle est aussi intensive pour les disques supportant le fichier en cours de maintenance.

Par ailleurs, du fait de ces déplacements, les index se trouvent généralement non pas fragmentés, mais fracassés. Donc il faut enchaîner l'opération de rétrécissement avec une maintenance des index.
Tout cela vaut-il vraiment le coup pour quelques mois ?

@++

**papao** · 09/08/2016, 07h54

Merci bien pour ta réponse,

La requête que j'utilise pour visualiser l'espace logique disponible c'est

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
SELECT  
	ds.name as filegroupname
	, df.name AS 'FileName' 
	, physical_name AS 'PhysicalName'
	, size/128 AS 'TotalSizeinMB'
	, size/128.0 - CAST(FILEPROPERTY(df.name, 'SpaceUsed') AS int)/128.0 AS 'AvailableSpaceInMB' 
	, CAST(FILEPROPERTY(df.name, 'SpaceUsed') AS int)/128.0 AS 'ActualSpaceUsedInMB'
FROM sys.database_files df 
LEFT OUTER JOIN sys.data_spaces ds ON df.data_space_id = ds.data_space_id
Order by 5 desc
use msdb
go

Les fichiers de données qui ont d'espace libre datant d'avant ne bouge plus, les insertions se font uniquement au jour J, donc le remplissage du fichier de données se fait uniquement au fichier de données rattaché à la date du jour, les espaces logiques disponibles des fichiers d'avant ne seront plus réutilisé, d'où, j'ai besoin de réduire ces fichiers pour gagner de la place.

250 Go représente un mois de données, il y a un fichier de données par mois.

J'ai essayé,

Code :

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

1
2
3
4
5
 
DBCC SHRINKFILE (<filename>, emptyfile);  
GO
alter database <maDatabase> remove file <filename>
go

Code :

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

DBCC SHRINKFILE (<filename>, 0)

Code :

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

1
2
3
4
5
 
DBCC SHRINKFILE (<filename>, NOTRUNCATE);  
GO
DBCC SHRINKFILE (<filename>, 0, TRUNCATEONLY)
GO

Mais le procédé est vraiment très lent, je ne sais plus quoi faire.
Si vous avez d'autres question, n'hésitez pas à me les poser

**Bernardos** · 09/08/2016, 09h22

S'il s'agit donc de récupérer un peu d'espace dans l'attente de l'achat d'espace disque supplémentaire parce que la base de données y tient tout juste, comme les fichiers de données ne grossiront visiblement pas, c'est cause perdue.

le calcul n'était visiblement pas juste mais le raisonnement reste plus que jamais valable.
je retente un calcul. tu vas gagner 25 * 48 Go soit 1200 Go. ta base grossit de 250 Go par mois. si je fais 1200 / 250 Go ca fait 4,8 Mois... ce qui n'est pas du court terme mais qui n'est pas très long non plus.

J'ai essayé,

Code :

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

1
2
3
4
DBCC SHRINKFILE (<filename>, emptyfile);  
GO
alter database <maDatabase> remove file <filename>
go

oullaaaaaa, qu'as tu voulu faire?

Pour le shrinkFile, as tu le possibilité de le faire hors de toutes activités ou ta base est elle utilisée en 7/7 24/24?

Pour gagner de la place...et peut être même beaucoup de place. il y a peut être un travail de fond à faire

Ta base n'est elle pas surindexée?
Tes index ne sont il pas trop gourmand ?
Tes char, Nchar, Varchar et Nvarchar sont ils justement calibrés?
as tu des varchar, nvarchar dont la longueur est inférieure à 8 caractères?
Ne peux-tu pas remplacer des nvarchar par des varchar?

Bref, rendons à Dieu ce qui est à dieu et à Sql Pro ce qui est à SQL Pro

Sql Pro a diffusé 20 requêtes pour auditer la structure de votre base de données
Bien lire le début du document.
ensuite, passer ces quelques requêtes va te donner rapidement des info précieuses.

mais comme je l'ai dit précédemment c'est un travail de fond, il faut réfléchir aux conséquences de toutes modifications!

autre question. quand on me parle de lenteur je pense en autre à vérifier les disques. dans quelles configuration es tu? disque physique, baie dédiée, baie partagée avec 1000 autres choses, disques taillés dans la massse, vitesse des disques, SSD?
et surtout, comment sont formaté tes disques ? quelle est la taille de secteur?
quelle version de windows et de sql server utilise tu?

cordialement,

**papao** · 09/08/2016, 09h53

Merci bien Bernardos.

Pour le

Code :

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

shrinkfile(<filename>, emptyfile)

, c'est que j'ai rajouté un fichier au groupe de fichier en espérant déplacé les données vers le nouveaux fichiers de supprimer l'ancien après

Pour les index, à mon étonnement, toutes les tables n'ont aucun index, et c'est l'une des raisons de récupérer de l'espace, je voudrais indexer les tables.

Pour les lenteurs, je constate surtout du côté d'accès disque, voici la configuration du serveur:
J'utilise SQL Server 2008 R2 (un projet de migration vers 2012 vas être planifié)
OS : windows server 2008 R2 Enterprise
PROCESSEUR: Xeon E7-8870 v3 2.1Ghz x 12
RAM : 80 Go
Disk:

9 Disk sata de 500 Go (data)
5 Disk sata de 250 Go (ceux-ci sont dédier pour les fichiers log .ldf et tmp)
3 Disk sata de 750 Go (data)
2 Disk sata de 1000 Go (data)

Aucun disque SSD et tous les disques sont dédiés aux données sauf un qui contient plein de petit fichier que je suis, en ce moment, en-train de nettoyer.

Je vais lire l'article de SQL-Pro et vérifier les types de données des tables et viendrais vers vous

Taille des secteurs des disques dur est 4 Ko

PS: comme je suis encore nouveau, je n'ose pas encore modifier les types de données des tables (nvarchar en varchar par exemple)

Par avance,
Merci

**Bernardos** · 09/08/2016, 10h30

De mémoire en 2008R2 le problème d'alignement au formatage était réglé.
Par contre, j'ai parlé peut-être abusivement de taille de secteur ' je parle bien de la taille de "cluster" ou encore "allocation unit size"
quoique la taille que tu me donnes correspond à ce que j'attendais et... c'est pas bien

je voudrais être sur que tu me donnes la bonne info.
2 possibilités de l'avoir
1.
lance une invite de command en cliquant droit -->exécuter en tant qu'administrateur
ensuite exécute cette commande : fsutil fsinfo ntfsinfo c: (pour chaque disque)

2.
tu peux aussi faire un diskpart ( à partir de l'invit de commande)

Code :

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

1
2
3
4
5
6
Diskpart
list disk
select disk 0 -- sélectionne le disque que tu veux analyser
list partition
select partition 5 - idem 
filesystems

comme ca tu recoupes l'info.

PS: comme je suis encore nouveau, je n'ose pas encore modifier les types de données des tables (nvarchar en varchar par exemple)

c'est une piste de réflexion pour le long terme.
ne rien modifier dans la précipitation!
par contre passer les scripts et réfléchir pour te faire un avis pour le long terme oui.

**papao** · 09/08/2016, 10h49

Il y a 10 disque physique sur le serveur, et seul le disk 0 (contenant le system c

a la valeur 4096 o pour la propriété "Allocation Unit Size", les reste sont tous à 64k.

PS: Pour le sujet "réduire la taille des fichier", est-ce qu'il y a moyen de récupérer les espaces logiques disponible par fichier ?

**Bernardos** · 09/08/2016, 12h07

ah ben du coup, là c'est tout bon !

Pour info, sql server travaille avec des blocs de 8 pages. chaque page faisant 8ko. 8*8 = 64 ko. Voilà pourquoi il est impératif, que les disques soient formatés avec des cluster de taille identique (64ko).
Donc, dans ton cas pas de soucis à ce niveau là.

par contre, je voudrais m'assurer que tu as bien compris comment fonctionne un fichier de base de données sous sql server.
Pour faire simple, lorsqu'il a besoin de 600 Go, le fichier grandit jusque 600 Go. si tu retires 25 Go de données, La taille de ton fichier reste bien de 600 Go MAIS tes 25 Go sont bel et bien disponibles pour de nouvelles données.
Donc je ne vois pas ce que "Shrinké" t'apporterait de plus. SAUF SI tu comptes ré-allouer cet espace à d'autres choses mais là faut que t'expliques ce que tu veux faire parce qu’il risque d'avoir discussions sur le sujet

cordialement,

**papao** · 09/08/2016, 13h30

L'allocation d'espace par fichier peut être dynamique, n'est-ce pas, c'est à dire:[*] On n'alloue pas au préalable un espace au disque dur, et lorsque l'espace réserver est atteint, il faut rajouter de nouveaux fichiers de données pour pouvoir insérer des données[*] Lorsqu'on ne pas fixe de seuil maximal au fichier d'un groupe de fichier, et tant qu'il y a de l'espace sur le disque, on peut toujours rajouter des donner dedans[*] Dans tous les cas, quand il y a suppression ou mise à jours de données, les "lignes mortes" peuvent être réutilisé pour des nouvelles insertions, ici pas besoin de réduire la taille du fichier physique.

Mais pour mon cas, les fichiers de données que je voudrais réduire la taille physiquement ne bougeront plus, aucune insertion possible, et l'espace gagné (en shrinkant par exemple) par disque dur qui n'est vraiment pas négligeable, je voudrais l'utiliser pour des fichiers de données (rajout de fichier dans un groupe de fichier pour d'autres partitions ou d'autres tables).

Je sais pas si c'est clair ce que je demande ou je m'embrouille dans mes explication

**Bernardos** · 09/08/2016, 17h57

Bon avec ta base de je ne sais pas combien de terra sans index, on est pas sorti de la berge

trouve toi un créneau (si c'est possible) le plus long possible, tu mets ta base en single user

Code :

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

1
2
3
4
 
ALTER DATABASE tabase
SET SINGLE USER WITH ROLLBACK IMMEDIATE;
GO

et puis tu lances ton shrink sur un fichier. avec un peu de bol, ça peut marcher un peu mieux.

Code :

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

1
2
3
 
ALTER DATABASE tabase
SET MULTI_USER ;

rassures moi t'as des clés primaires et des clés étrangères ?

Cordialement,

**papao** · 10/08/2016, 07h30

C'est pas gagné du tout pour moi car malheureusement, je ne peux pas alterer la base de donnée en single user car elle est exploitée 24h/24 et 7j/7.

Une idée me vienne à la tête, si je créer 4 nouvelles tables, dans le même groupe de fichier, je switch une à une les partitions de mes 4 tables restantes, pour chaque partition (c'est à dire par fichier car une partition pour moi correspond à un fichier), lorsque toute les données de la partition sont déplacées, je shrink le fichier

Code :

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

DBCC SHRINKFILE (<filename>, 0, TRUNCATEONLY)

et après, je remet les partition à leurs emplacement d'origine.

Cela pourrait aller plus vite à votre avis ?

**Bernardos** · 10/08/2016, 09h26

c'est un débat de tous les jours mais pour moi, dans le cadre d'une application 7jours sur 7/ 24h sur 24, il faut prévoir 1 fois par mois (ou par trimestre selon cas) une tranche de un jour ( ca peut être par exemple la nuit du samedi au dimanche mais ca dépend du business) réservée à la maintenance. à l'approche de cette date(1 semaine avant), si tu vois qu'il n'y a rien à faire tu ne fais rien, sinon tu préviens les utilisateurs qu'il y aura des perturbations en précisant la plage de manière large(il faut prendre en compte les risques)
Ma banque fait ça environ tous les 3 mois pour le pc banking.
Pour moi ta solution est là. ce que tu proposes me parait farfelu...
Cordialement,

**papao** · 10/08/2016, 10h32

t'as raison, il faut que je réserve un journée/nuit tous les mois pour la maintenance.

merci bien Bernardos pour cette discussion, c'était très passionnant et enrichissant.

Une petite question qui n'a rien à voir avec le sujet:
Pourquoi une table de 60Go (Compresser PAGE) stocké dans une table non partitionnée, puis migré vers une table partitionnée mm/yyyy (dispatché sur 12 fichiers par exemple) prend environs 90Go ou plus (compresser Page aussi)

**Bernardos** · 10/08/2016, 10h53

Question intéressante à laquelle d'autres personne sont plus à même que moi de répondre

comme la discussion a été longue sur le premier sujet, il vaut mieux que tu refasses un nouveau sujet.
Par contre, une fois que tu auras testé ton shrink hors activité, reviens poster une réponse et passe à résolu le cas échéant

Cordialement,