Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

  1. #1
    Membre du Club
    Homme Profil pro
    Étudiant
    Inscrit en
    novembre 2016
    Messages
    88
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 25
    Localisation : France, Seine Saint Denis (Île de France)

    Informations professionnelles :
    Activité : Étudiant

    Informations forums :
    Inscription : novembre 2016
    Messages : 88
    Points : 48
    Points
    48

    Par défaut Réduire l'espace occupé par une BDD

    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

  2. #2
    Membre actif
    Homme Profil pro
    Architecte de base de données
    Inscrit en
    septembre 2016
    Messages
    176
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 51
    Localisation : France, Isère (Rhône Alpes)

    Informations professionnelles :
    Activité : Architecte de base de données
    Secteur : Conseil

    Informations forums :
    Inscription : septembre 2016
    Messages : 176
    Points : 291
    Points
    291

    Par défaut

    Citation Envoyé par nadjimsad Voir le message
    j'ai déjà essayé de réduire la bdd ainsi que les fichiers
    Qu'est ce qui a été fait exactement ?

  3. #3
    Membre du Club
    Homme Profil pro
    Étudiant
    Inscrit en
    novembre 2016
    Messages
    88
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 25
    Localisation : France, Seine Saint Denis (Île de France)

    Informations professionnelles :
    Activité : Étudiant

    Informations forums :
    Inscription : novembre 2016
    Messages : 88
    Points : 48
    Points
    48

    Par défaut

    Citation Envoyé par Michel.Priori Voir le message
    Qu'est ce qui a été fait exactement ?
    Réduire-> base de données
    Réduire -> fichiers

  4. #4
    Rédacteur
    Avatar de SQLpro
    Homme Profil pro
    Expert SGBDR & SQL, spécialiste Microsoft SQL Server
    Inscrit en
    mai 2002
    Messages
    18 712
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Expert SGBDR & SQL, spécialiste Microsoft SQL Server
    Secteur : Conseil

    Informations forums :
    Inscription : mai 2002
    Messages : 18 712
    Points : 43 721
    Points
    43 721

    Par défaut

    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 : 50
Taille : 105,0 Ko


    A +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence * * * * *

  5. #5
    Membre du Club
    Homme Profil pro
    Étudiant
    Inscrit en
    novembre 2016
    Messages
    88
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 25
    Localisation : France, Seine Saint Denis (Île de France)

    Informations professionnelles :
    Activité : Étudiant

    Informations forums :
    Inscription : novembre 2016
    Messages : 88
    Points : 48
    Points
    48

    Par défaut

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

  6. #6
    Membre du Club
    Homme Profil pro
    Étudiant
    Inscrit en
    novembre 2016
    Messages
    88
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 25
    Localisation : France, Seine Saint Denis (Île de France)

    Informations professionnelles :
    Activité : Étudiant

    Informations forums :
    Inscription : novembre 2016
    Messages : 88
    Points : 48
    Points
    48

    Par défaut

    Citation Envoyé par SQLpro Voir le message
    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...

  7. #7
    Rédacteur
    Avatar de SQLpro
    Homme Profil pro
    Expert SGBDR & SQL, spécialiste Microsoft SQL Server
    Inscrit en
    mai 2002
    Messages
    18 712
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Expert SGBDR & SQL, spécialiste Microsoft SQL Server
    Secteur : Conseil

    Informations forums :
    Inscription : mai 2002
    Messages : 18 712
    Points : 43 721
    Points
    43 721

    Par défaut

    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 +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence * * * * *

  8. #8
    Membre actif
    Homme Profil pro
    Architecte de base de données
    Inscrit en
    septembre 2016
    Messages
    176
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 51
    Localisation : France, Isère (Rhône Alpes)

    Informations professionnelles :
    Activité : Architecte de base de données
    Secteur : Conseil

    Informations forums :
    Inscription : septembre 2016
    Messages : 176
    Points : 291
    Points
    291

    Par défaut

    Citation Envoyé par nadjimsad Voir le message
    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 ?

  9. #9
    Rédacteur
    Avatar de SQLpro
    Homme Profil pro
    Expert SGBDR & SQL, spécialiste Microsoft SQL Server
    Inscrit en
    mai 2002
    Messages
    18 712
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Expert SGBDR & SQL, spécialiste Microsoft SQL Server
    Secteur : Conseil

    Informations forums :
    Inscription : mai 2002
    Messages : 18 712
    Points : 43 721
    Points
    43 721

    Par défaut

    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 +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence * * * * *

  10. #10
    Membre actif
    Homme Profil pro
    Architecte de base de données
    Inscrit en
    septembre 2016
    Messages
    176
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 51
    Localisation : France, Isère (Rhône Alpes)

    Informations professionnelles :
    Activité : Architecte de base de données
    Secteur : Conseil

    Informations forums :
    Inscription : septembre 2016
    Messages : 176
    Points : 291
    Points
    291

    Par défaut

    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.

  11. #11
    Rédacteur
    Avatar de SQLpro
    Homme Profil pro
    Expert SGBDR & SQL, spécialiste Microsoft SQL Server
    Inscrit en
    mai 2002
    Messages
    18 712
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Expert SGBDR & SQL, spécialiste Microsoft SQL Server
    Secteur : Conseil

    Informations forums :
    Inscription : mai 2002
    Messages : 18 712
    Points : 43 721
    Points
    43 721

    Par défaut

    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 +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence * * * * *

  12. #12
    Rédacteur
    Avatar de SQLpro
    Homme Profil pro
    Expert SGBDR & SQL, spécialiste Microsoft SQL Server
    Inscrit en
    mai 2002
    Messages
    18 712
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Expert SGBDR & SQL, spécialiste Microsoft SQL Server
    Secteur : Conseil

    Informations forums :
    Inscription : mai 2002
    Messages : 18 712
    Points : 43 721
    Points
    43 721

    Par défaut

    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

    Dans le contexte de la base pour savoir cela…

    Enfin une dernière chose est que le stockage peut avoir été endommagé. Donc faire un :

    Dans le contexte de la base pour savoir cela aussi…

    A +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence * * * * *

Discussions similaires

  1. Espace occupé par une partie d'une table
    Par besco dans le forum Administration
    Réponses: 1
    Dernier message: 24/10/2007, 16h00
  2. Gestion de l'espace occupé par une valeur null
    Par Oberown dans le forum MS SQL Server
    Réponses: 3
    Dernier message: 01/08/2007, 14h03
  3. Calcul de l'espace occupé par une Collection
    Par had35 dans le forum Général Java
    Réponses: 17
    Dernier message: 13/09/2006, 11h52
  4. [ASA]Espace occupé par une Table
    Par madina dans le forum Sybase
    Réponses: 3
    Dernier message: 17/07/2006, 17h16
  5. Espace occupé par une table
    Par Mihaela dans le forum Oracle
    Réponses: 3
    Dernier message: 18/06/2006, 22h09

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo