Discussion :

[Rafter]

Bonjour,

j'ai une base de données sqlserver 2008 r2 ayant un fichier mdf de 110 Giga.
la taille réel des données la base est de 70 Giga.

Afin de récupérer les 40 Giga non utilisée, et afin aussi d'éviter de shrinker le fichier mdf ( c'est risqué), je pourrais procéder de cette manière ? :

- sauvegarder la base
- truncate de toutes les tables de la bases
- restaurer la base

aurais -je dans ce cas un mdf de 70 Giga ou toujours 110 Giga.

Sinon pourrais-je procèder de cette manière :

-sauvegarder la base
- generer le script de création de la structure de cette base
- supprimer la base
- lancer le script de création et puis restaurer la nouvelle base depuis la svg de l'etape 1

Merci bcp de votre aide

la bonne soirée.

[GeekMokona]

Le Shrink n'est pas risqué (aucun risque de perte de donnée), il est juste
- conseillé de ne pas en abuser sur les fichiers de donnés (.mdf) sur des bases de données ayant pour habitude de grossir , mais utile dans le cas d'une suppression massive de donnée .
- complètement déconseillé sur les fichiers de log (.ldf) , dans ce cas il est préférable de revoir la méthode de sauvegarde de la base de donnée et en particulier des fichiers log.

[SQLpro]

Afin de récupérer les 40 Giga non utilisée, et afin aussi d'éviter de shrinker le fichier mdf ( c'est risqué), je pourrais procéder de cette manière ? :

- sauvegarder la base
- truncate de toutes les tables de la bases
- restaurer la base

Cela n'aura aucun effet ! Et en plus c'est idiot, car la restauration vous donnera une base strictement identique à celle de départ. Une sauvegarde de la base c'est la copie intégrale et exacte des fichiers de la base à un instant t, t étant le moment ou la sauvegarde s'arrête.

aurais -je dans ce cas un mdf de 70 Giga ou toujours 110 Giga.

Sinon pourrais-je procèder de cette manière :

-sauvegarder la base
- generer le script de création de la structure de cette base
- supprimer la base
- lancer le script de création et puis restaurer la nouvelle base depuis la svg de l'etape 1.

Comme dit une sauvegarde ce n'est pas des ordres SQL INSERT !

Formez vous à l'administration SQL Server... On n'est pas sous MySQmerde !

La seule chose à faire est de défragmenter vos tables. Sans cela aucune réduction effective n'est possible.
Une réduction effective des fichiers n'a pas d'intérêt. Pire elle donnera des performances plus mauvaise à votre base.

Avez-vous un job de défragmentation des tables et index qui tourne en maintenance de manière régulière (planifiée par l'Agent SQL) ?

A +

[Rafter]

Bonjour,

hier, j'ai fais les actions suivantes.

reduire ( shrinker ) la base, j'ai pu gagner 16 Giga en terme d'espace.
et ensuite j'ai lancé ce script :

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
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
#############################################################
USE DBName
 
GO
 
 
 
DECLARE @tsql NVARCHAR(MAX)  
 
DECLARE @fillfactor INT
 
 
 
SET @fillfactor = 70 
 
 
 
DECLARE @FragmentedIndexs TABLE (IndexID INT,
 
                                 IndexName VARCHAR(100),
 
                                 ObjectName VARCHAR(100),
 
                                 AvgFragmentationInPercent DECIMAL(6,2),
 
                                 FragmentCount INT,
 
                                 AvgFragmentSizeInPage DECIMAL(6,2),
 
                                 IndexDepth INT)
 
 
 
--Insert the Details for Fragmented Indexes.
 
INSERT INTO @FragmentedIndexs
 
SELECT 
 
  PS.index_id,
 
  QUOTENAME(I.name) Name,
 
  QUOTENAME(DB_NAME()) +'.'+ QUOTENAME(OBJECT_SCHEMA_NAME(I.[object_id])) + '.' + QUOTENAME(OBJECT_NAME(I.[object_id])) ObjectName,
 
  PS.avg_fragmentation_in_percent,
 
  PS.fragment_count,
 
  PS.avg_fragment_size_in_pages,
 
  PS.index_depth
 
FROM 
 
  sys.dm_db_index_physical_stats (DB_ID(), NULL ,NULL, NULL, NULL) AS PS
 
INNER JOIN sys.indexes AS I 
 
  ON PS.[object_id]= I.[object_id]
 
  AND PS.index_id = I.index_id
 
WHERE
 
  PS.avg_fragmentation_in_percent > 5  
 
ORDER BY 
 
  PS.avg_fragmentation_in_percent DESC
 
 
 
--Select the details.
 
SELECT * FROM @FragmentedIndexs
 
 
 
--Prepare the Query to REORGANIZE the Indexes
 
SET @tsql = ''
 
 
 
SELECT @tsql = 
 
  STUFF(( SELECT DISTINCT 
 
           ';' + 'ALTER INDEX ' + FI.IndexName + ' ON ' + FI.ObjectName + ' REORGANIZE '
 
          FROM 
 
           @FragmentedIndexs FI
 
          WHERE
 
            FI.AvgFragmentationInPercent <= 30
 
          FOR XML PATH('')), 1,1,'')
 
 
 
SELECT @tsql
 
PRINT 'REORGANIZING START'
 
EXEC sp_executesql @tsql 
 
PRINT 'REORGANIZING END'
 
 
 
--Prepare the Query to REBUILD the Indexes
 
SET @tsql = ''
 
 
 
SELECT @tsql = 
 
  STUFF(( SELECT DISTINCT 
 
           ';' + 'ALTER INDEX ' + FI.IndexName + ' ON ' + FI.ObjectName + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ') '
 
          FROM 
 
           @FragmentedIndexs FI
 
          WHERE
 
            FI.AvgFragmentationInPercent > 30
 
          FOR XML PATH('')), 1,1,'')
 
 
 
SELECT @tsql
 
PRINT 'REBUILD START'
 
EXEC sp_executesql @tsql
 
PRINT 'REBUILD END'
#############################################################

merci de votre aide.

[elsuket]

Bonjour,

Faire rétrécir la base de données régulièrement est une mauvaise idée, comme l'a détaillé SQLPro.
Si vous l'avez fait suite à une opération de maintenance des données exceptionnelle et sous contrainte d'espace disque libre, passons.

Votre script n'est pas mal, mais il n'est peut-être pas adapté de mettre le FILLFACTOR à 70 pour tous les index dont la fragmentation était supérieure à 30%.

Pour déterminer un bon FILLFACTOR, il vous faut regarder le nombre de splits de page pour l'index à défragmenter, à l'aide de la DMF sys.dm_db_index_operational_stats() (colonnes (non)leaf_insert_count, (non)leaf_delete_count, (non)leaf_update_count).
Donc, il vous faut sauvegarder ces caractéristiques dans une table, de façon à avoir un point de comparaison.
Ensuite vous défragmentez, et avant de défragmenter une nouvelle fois, vous regardez si la défragmentation a réduit le nombre de splits de page.
Notez que les splits de page peuvent être dus aux types de données utilisé dans les colonnes indexées.

Mais cela ne vaut que pour les requêtes de type DML.
En ce qui concerne les requêtes SELECT, il vous faut vous pencher sur la DMV sys.dm_db_index_usage_stats.
En effet, plus le FILLFACTOR est bas, plus grand sera le nombre de lectures.

Donc la valeur du FILLFACTOR doit refléter l'équilibre entre les lectures et écritures subies par la table sous-jacente.

[Rafter]

Merci bcp pour toutes ces explications.

Ci-dessous les résultats de cette requête :

Code :

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

1
2
3
SELECT TOP 10 OBJECT_NAME(object_id, database_id) object_nm,index_id,partition_number,leaf_allocation_count,nonleaf_allocation_count
FROM sys.dm_db_index_operational_stats(db_id(), NULL, NULL, NULL)
ORDER BY leaf_allocation_count DESC

object_nm index_id partition_number leaf_allocation_count nonleaf_allocation_count
TABLE1 1 1 289346 2179
TABLE2 1 1 197251 638
TABLE3 1 1 70176 628
TABLE4 1 1 69616 434
TABLE5 1 1 69281 78
TABLE6 1 1 50609 344
TABLE7 1 1 39866 90

Merci de votre aide.