Comment rétrécir ma base de données SQL Server?

ALTER DATABASE MyDatabase SET RECOVERY SIMPLE

GO

DBCC SHRINKFILE (MyDatabase_Log, 5)

GO

ALTER DATABASE MyDatabase SET RECOVERY FULL

GO

cela peut sembler bizarre, mais ça a marché pour moi et j'ai écrit un programme C# pour automatiser ça.

Étape 1: tronquer le journal des transactions (sauvegarder seulement le journal des transactions, activer l'option de supprimer les transactions inactives)

Étape 2: Exécuter un shrink de base de données, en déplaçant toutes les pages au début des fichiers

Étape 3: tronquer de nouveau le journal des transactions, car l'étape 2 ajoute les entrées du journal

Step 4: Lancez à nouveau un rétrécissement de la base de données.

My stripped down code, qui utilise la bibliothèque DMO SQL, est le suivant:

SQLDatabase.TransactionLog.Truncate();
SQLDatabase.Shrink(5, SQLDMO.SQLDMO_SHRINK_TYPE.SQLDMOShrink_NoTruncate);
SQLDatabase.TransactionLog.Truncate();
SQLDatabase.Shrink(5, SQLDMO.SQLDMO_SHRINK_TYPE.SQLDMOShrink_Default);

C'est une vieille question, mais je viens de m'en occuper.

la réponse vraiment courte et correcte est déjà donnée et a le plus de voix. C'est comment vous rétrécissez un journal des transactions, et c'était probablement le problème de L'OPs. Et lorsque le journal des transactions est devenu hors de contrôle, il a souvent besoin d'être rétréci, mais il faut prendre soin d'éviter que les situations futures d'un journal devienne hors de contrôle. Cette question sur dba.se explique ça. fondamentalement - ne le laissez pas obtenir que grand en premier lieu par le modèle de récupération approprié, la maintenance du journal des transactions, la gestion des transactions, etc.

mais la plus grande question dans mon esprit lors de la lecture de cette question sur le rétrécissement du fichier de données (ou même le fichier journal) est pourquoi? et ce que de mauvaises choses se produisent lorsque vous essayez? il semble que des opérations de rétrécissement aient été effectuées. Maintenant dans ce cas, cela a du sens dans un sens - parce que les éditions MSDE/Express sont plafonnées à la taille Max DB. Mais la bonne réponse peut être de regarder la bonne version pour vos besoins. Et si vous tombez sur cette question en cherchant à rétrécir votre base de données de production et ce n'est pas la raison pour laquelle, vous devriez vous demander le pourquoi? question.

Je ne veux pas que quelqu'un cherche sur le web "comment rétrécir une base de données" à travers ceci et penser que c'est un cool ou une chose acceptable à faire.

réduire les fichiers de données est une tâche spéciale qui devrait être réservé pour des occasions spéciales. Considérez que lorsque vous rétrécissez une base de données, vous fragmentez efficacement vos index. Considérez que lorsque vous rétrécissez une base de données, vous enlevez l'espace libre qu'une base de données peut Un jour se développer à nouveau dans - effectivement perdre votre temps et encourir le coup de performance d'une opération de rétrécissement seulement pour voir la DB se développer à nouveau.

I a écrit sur ce concept dans plusieurs billets de blog sur le rétrécissement des bases de données. Celui-ci appelé " ne pas toucher ce bouton de rétrécissement " vient d'abord à l'esprit. Je parle de ces concepts décrits ici - mais aussi du concept de "taille adéquate" de votre base de données. Il est beaucoup mieux de décider de la taille de votre base de données doit être, planifier pour la croissance future et l'allouer à ce montant. Avec L'initialisation de fichier instantanée disponible dans SQL Server 2005 et au-delà pour les fichiers de données, le coût des croissances est plus faible - mais je préfère encore avoir une bonne application initiale - et je suis beaucoup moins peur de l'espace blanc dans une base de données que je suis de rétrécir en général sans penser d'abord. :)

DBCC SHRINKDATABASE fonctionne pour moi, mais c'est sa syntaxe complète:

DBCC SHRINKDATABASE ( database_name, [target_percent], [truncate] )

où target_percent est le pourcentage désiré d'espace libre laissé dans le fichier de base de données après que la base de données a été réduite.

et truncate paramètre peut être:

NOTRUNCATE

permet de conserver l'espace de fichier libéré dans les fichiers de la base de données. Si non spécifié, l'espace de fichier libéré est libéré dans le système d'exploitation.

TRUNCATEONLY

fait que tout espace non utilisé dans les fichiers de données est libéré dans le système d'exploitation et rétrécit le fichier dans la dernière mesure attribuée, réduisant la taille du fichier sans déplacer de données. Aucune tentative n'est faite pour déplacer des lignes vers des pages non attribuées. target_pourcentage est ignoré lorsque TRUNCATEONLY est utilisé.

...et oui non_une personne a raison, réduire la base de données n'est pas une très bonne pratique parce que par exemple:

rétrécir sur les fichiers de données sont excellent moyen d'introduire une fragmentation logique significative, parce qu'il déplace des pages de la fin de la gamme attribuée d'un fichier de base de données à quelque part au début du fichier...

base de données shrink peut avoir beaucoup de conséquences sur la base de données, serveur.... réfléchis beaucoup avant de le faire!

sur le web il y a beaucoup de blogs et d'articles à ce sujet.

réponse Tardive mais peut être utile pour quelqu'un d'autre

si ni DBCC ShrinkDatabase/ShrinkFile ni SSMS (Tasks/Shrink/Database) n'aide, il y a des outils de Quest et ApexSQL qui peuvent faire le travail, et même programmer le rétrécissement périodique si vous en avez besoin.

j'ai utilisé ce dernier dans free trial pour faire cela il y a quelque temps, en suivant une brève description à la fin de cet article:

https://solutioncenter.apexsql.com/sql-server-database-shrink-how-and-when-to-schedule-and-perform-shrinking-of-database-files /

Tout ce que vous devez faire est d'installer la sauvegarde ApexSQL, cliquez sur" Shrink database "bouton dans le ruban principal, sélectionnez base de données dans la fenêtre qui s'ouvrira, et cliquez sur"Terminer".

vous aurez également besoin de rétrécir les fichiers de données individuelles.

ce n'est toutefois pas une bonne idée de rétrécir les bases de données. Par exemple, voir ici

vous devez utiliser:

dbcc shrinkdatabase (MyDB)

il va rétrécir le fichier journal (garder un explorateur windows ouvert et voir ce qui se passe).

Voici une autre solution: utilisez le " Assistant de publication de base de données pour exporter votre schéma, votre sécurité et vos données vers des scripts sql. Vous pouvez alors désactiver votre base de données actuelle et la recréer avec les scripts.

semble un peu stupide, mais il y a quelques avantages. D'abord, il n'y a aucune chance de perdre data. Votre base de données originale (tant que vous ne supprimez pas votre base de données lors de sa chute!) est sûr, le nouveau DB sera à peu près aussi petit que possible, et vous aurez deux instantanés de votre base de données actuelle - un prêt à rouler, un minifiés - vous pouvez choisir de sauvegarder.

" par conséquent, il est raisonnable de supposer que beaucoup d'espace devrait maintenant être récupérable."

S'excuse si j'ai mal compris la question, mais Êtes-vous sûr que c'est la base de données et pas les fichiers journaux qui utilisent l'espace? Vérifiez dans quel modèle de récupération se trouve la base de données. Il y a des Chances qu'il soit complet, ce qui signifie que le fichier journal n'est jamais tronqué. Si vous n'avez pas besoin d'un enregistrement complet de chaque transaction, vous devriez pouvoir passer à Simple, qui tronquera les journaux. Vous pouvez rétrécir la base de données pendant le processus. En supposant que les choses vont bien, le processus ressemble à:

1. sauvegardez la base de données!
2. passage à la récupération Simple
3. Shrink db (cliquez-droit sur db, choisissez toutes les tâches > réduire db -> set de 10% d'espace libre)
4. vérifiez que l'espace a été récupéré, sinon vous pourriez avoir à faire une sauvegarde complète

Si cela ne fonctionne pas (ou vous obtenez un message disant "le fichier journal est plein" lorsque vous essayez de changer les modes de récupération), essayez ceci:

1. Sauvegarde
2. Tuer toutes les connexions à la bd
3. Détacher db (clic droit > Detach ou clic-droit > Toutes les Tâches > Detach)
4. supprimer le fichier log (ldf)
5. remettre le db
6. changer le mode de récupération

etc.

je suis tombé sur ce post même si j'avais besoin de SHRINKFILE sur la version MSSQL 2012 qui est peu plus compliqué depuis 2000 ou 2005 versions. Après avoir lu sur tous les risques et les questions liées à cette question, j'ai fini par tester. Pour faire court, les meilleurs résultats que j'ai obtenus ont été de l'utilisation du MS SQL Server Management Studio .

Right-Click the DB -> TASKS -> SHRINK -> FILES -> select the LOG file

, Vous devez également modifier la taille minimale des données et des fichiers journaux. DBCC SHRINKDATABASE va rétrécir les données à l'intérieur de les fichiers que vous avez déjà alloués. Pour rétrécir un fichier à une taille plus petite que sa taille minimale, utiliser DBCC SHRINKFILE et spécifier la nouvelle taille.

Supprimer des données, s'assurer que le modèle de récupération est simple, puis skrink (soit base de données de rétrécissement ou les fichiers de rétrécissement fonctionne). Si le fichier de données est encore trop grand, et que vous utilisez des tas pour stocker des données -- c'est-à-dire aucun index groupé sur de grandes tables -- alors vous pourriez avoir ce problème concernant la suppression de données à partir de tas: http://support.microsoft.com/kb/913399

j'ai récemment fait ça. J'essayais de faire une version compacte de ma base de données pour tester sur la route, mais je n'arrivais pas à la faire rétrécir, peu importe le nombre de lignes que j'ai effacées. Finalement, après beaucoup d'autres commandes dans ce thread, j'ai trouvé que mes index groupés n'étaient pas reconstruits après avoir effacé des lignes. J'ai reconstruit mes index pour pouvoir rétrécir correctement.

Je ne sais pas si cela serait pratique, et selon la taille de la base de données, le nombre de tableaux et d'autres complexités, mais je:

1. défragmenter le disque physique
2. créer une nouvelle base de données en fonction de mes besoins, de l'espace, de pourcentage de la croissance, etc
3. utilisez la tâche simple ssms pour importer toutes les tables de l'ancienne db à la nouvelle db
4. script les indices de toutes les tables sur l'ancien base de données, puis recréer les index sur la nouvelle base de données. agrandir au besoin pour les clés étrangères, etc.
5. renommer les bases de données selon les besoins, confirmer le succès, supprimer l'ancienne

je pense que vous pouvez enlever tout votre journal avec le commutateur de pleine à simple récupération. Faites un clic droit sur votre base de données et sélectionnez Properties et sélectionnez Options et changez

• Recovery mode à Simple
• Containment type à None