Réduire l'espace occupé par une BDD

**nadjimsad** · 29/03/2019, 13h08

Bonjour,

Alors voici mon problème : ma bdd occupe environ 150 go, pour libérer de l'espace disque j'ai supprimé plusieurs tables. Ces dernières n'occupent plus qu'environ 100 Mo. Sauf que je constate que ma base fait toujours 150 go. Et que j'ai 99% d'espace disponible pour cette bdd.

J'aimerais donc savoir comment réduire cet espace disque déjà alloué à la bdd.

j'ai déjà essayé de réduire la bdd ainsi que les fichiers.

C'est un windows server 2012 R2

Je vous remercie par avance pour votre aide

Cordialement,

Nadjim

**Michel.Priori** · 29/03/2019, 18h39

Envoyé par nadjimsad

j'ai déjà essayé de réduire la bdd ainsi que les fichiers

Qu'est ce qui a été fait exactement ?

**nadjimsad** · 31/03/2019, 20h18

Envoyé par Michel.Priori

Qu'est ce qui a été fait exactement ?

Réduire-> base de données
Réduire -> fichiers

**SQLpro** · 01/04/2019, 19h48

1 - Il faut commencer par défragmenter les tables et index, par exemple avec le script suivant, exécuté dans le contexte de votre base :

Code :

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

1
2
3
4
5
6
7
8
DECLARE @SQL NVARCHAR(max) = N'';
SELECT @SQL = @SQL +'ALTER ' + CASE WHEN i.name IS NULL THEN 'TABLE' ELSE 'INDEX [' + i.name + '] ON' END 
       + N' [' + o.name + '] REBUILD;'
FROM   sys.objects AS o
       JOIN sys.schemas AS s ON o.schema_id = s.schema_id
       JOIN sys.indexes AS i ON o.object_id = i.object_id
WHERE o.type IN ('U', 'V');
EXEC (@SQL);

2 - Ensuite vous pouvez tenter de réduire l'espace de stockage, à l'aide du script suivant (toujours dans le contexte de la base) :

Code :

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

1
2
3
4
DECLARE @SQL2 NVARCHAR(max) = N'';
SELECT @SQL2 = @SQL2 + N'DBCC SHRINKFILE('''+ name +''', 1);'
FROM  sys.database_files;
EXEC (@SQL2);

Si cela ne suffit pas...
3 - vous pouvez passer votre base en mode de récupération SIMPLE et refaire la réduction (toujours dans le contexte de la base) :

Code :

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

ALTER DATABASE CURRENT SET RECOVERY SIMPLE;

Si versions antérieure à 2012, mettez le nom de la base à la pace de CURRENT

Si cela n'est toujours pas suffisant et que vous êtes en édition Enterprise ou en version 2016 Sp1 et postérieure…

4 - vous pouvez compressez toutes vos données. Pour cela exécutez le script suivant, exécuté dans le contexte de votre base :

Code :

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

1
2
3
4
5
6
7
8
DECLARE @SQL3 NVARCHAR(max) = N''
SELECT @SQL3 = @SQL3 + 'ALTER ' + CASE WHEN i.name IS NULL THEN 'TABLE' ELSE 'INDEX [' + i.name + '] ON' END 
       + N' [' + o.name + '] REBUILD WITH (DATA_COMPRESSION = PAGE);'
FROM   sys.objects AS o
       JOIN sys.schemas AS s ON o.schema_id = s.schema_id
       JOIN sys.indexes AS i ON o.object_id = i.object_id
WHERE o.type IN ('U', 'V');
EXEC (@SQL3);

Et pour en savoir plus, lisez notre livre :
Nom : Couverture livre SQL server Eyrolles.jpg
Affichages : 1536
Taille : 105,0 Ko

A +

**nadjimsad** · 03/04/2019, 14h57

Je te remercie pour ta réponse. Je vais tester cela et je te fais un retour au plus vite

**nadjimsad** · 11/04/2019, 16h00

Envoyé par SQLpro

1 - Il faut commencer par défragmenter les tables et index, par exemple avec le script suivant, exécuté dans le contexte de votre base :

Code :

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

1
2
3
4
5
6
7
8
DECLARE @SQL NVARCHAR(max) = N'';
SELECT @SQL = @SQL +'ALTER ' + CASE WHEN i.name IS NULL THEN 'TABLE' ELSE 'INDEX [' + i.name + '] ON' END 
       + N' [' + o.name + '] REBUILD;'
FROM   sys.objects AS o
       JOIN sys.schemas AS s ON o.schema_id = s.schema_id
       JOIN sys.indexes AS i ON o.object_id = i.object_id
WHERE o.type IN ('U', 'V');
EXEC (@SQL);

2 - Ensuite vous pouvez tenter de réduire l'espace de stockage, à l'aide du script suivant (toujours dans le contexte de la base) :

Code :

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

1
2
3
4
DECLARE @SQL2 NVARCHAR(max) = N'';
SELECT @SQL2 = @SQL2 + N'DBCC SHRINKFILE('''+ name +''', 1);'
FROM  sys.database_files;
EXEC (@SQL2);

Si cela ne suffit pas...
3 - vous pouvez passer votre base en mode de récupération SIMPLE et refaire la réduction (toujours dans le contexte de la base) :

Code :

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

ALTER DATABASE CURRENT SET RECOVERY SIMPLE;

Si versions antérieure à 2012, mettez le nom de la base à la pace de CURRENT

A +

Bonjour,

Je viens de tester mais malheureusement la bdd prend toujours 150go...

**SQLpro** · 15/04/2019, 10h19

Il vous reste à tenter la compression si votre édition la supporte.... Sinon, c'est que les données ne peuvent être réduites sans pertes.....

A +

**Michel.Priori** · 15/04/2019, 17h40

Envoyé par nadjimsad

Alors voici mon problème : ma bdd occupe environ 150 go, pour libérer de l'espace disque j'ai supprimé plusieurs tables. Ces dernières n'occupent plus qu'environ 100 Mo. Sauf que je constate que ma base fait toujours 150 go. Et que j'ai 99% d'espace disponible pour cette bdd.

C'est super étrange qu'en suivant les indications de SQLpro cela ne soit pas rentré dans l'ordre.

Comment l'espace disponible (99%) à été trouvé ?

Quelle est la taille de chaque fichier de la base avec le taux d'occupation respectif ?

**SQLpro** · 15/04/2019, 21h16

Il est possible qu'il y ait des tables systèmes qui occupent ce volume. Elle ne sont pas comptées dans les dimensionnement livré par la majorité des requêtes. C'est le cas notamment des tables système de Service Broker, lorsque les échanges de messages n'ont pas été paramétré avec un temps de rétention correct.

SI tel était le cas il faut simplement restaurer la base en demandant un "new broker".

A +

**Michel.Priori** · 16/04/2019, 10h57

D'où le double intérêt d'ajouter systématiquement un deuxième Groupe de Fichier (rendre ce GF "par défaut") et de lister la taille de chaque fichier (dans chaque GF) pour :
- déterminer où est le pb
- s'habituer à l'administration fichier & GF

Mais honnêtement, une base qui peut passer d'une occupation de 150 Go à 15 Go en supprimant des infos et sans que "ça gène", ça me laisse perplexe.

**SQLpro** · 16/04/2019, 11h08

Personnellement je n'utilise JAMAIS le groupe de fichier PRIMARY pour y mettre la moindre table ou index utilisateur….

Mais déjà expliquer cela aux clients, c'est une journée de boulot, car il ne savent même pas que SQL Server gère son propre storage indépendemment de l'OS….

A +

**SQLpro** · 16/04/2019, 11h11

Dans cette demande on ne sait pas non plus si c'est pas le journal de transaction qui est trop volumineux, par exemple à cause d'une transaction bloquée.

Il faudrait déjà faire un

Code :

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