Discussion :

[papao]

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]

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]

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]

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]

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]

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.