Как уменьшить размер базы данных SQL Server?

Вы также должны изменить минимальный размер файлов данных и журналов. DBCC SHRINKDATABASE сожмет данные внутри файлов, которые вы уже разместили. Чтобы уменьшить файл до размера, меньшего, чем его минимальный размер, используйте DBCC SHRINKFILE и укажите новый размер.

Вам также потребуется сжать отдельные файлы данных.

Однако сокращать базы данных - не лучшая идея. Например, см. здесь

DBCC SHRINKDATABASE у меня работает, но вот его полный синтаксис:

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

где target_percent - желаемый процент свободного места, оставшегося в файле базы данных после сжатия базы данных.

А параметр truncate может быть:

NOTRUNCATE

Заставляет освободившееся файловое пространство оставаться в файлах базы данных. Если не указано иное, освободившееся файловое пространство передается операционной системе.

TRUNCATEONLY

Вызывает освобождение всего неиспользуемого пространства в файлах данных операционной системе и сжимает файл до последнего выделенного экстента, уменьшая размер файла без перемещения каких-либо данных. Попытки переместить строки на нераспределенные страницы не предпринимаются. target_percent игнорируется при использовании TRUNCATEONLY.

... и да, no_one правильно, сокращение базы данных - не очень хорошая практика, например:

сжатие файлов данных - отличный способ ввести значительную логическую фрагментацию, поскольку он перемещает страницы из конца выделенного диапазона файла базы данных в место в начале файла ...

сжатие базы данных может иметь серьезные последствия для базы данных, сервера .... хорошенько подумайте об этом, прежде чем делать это!

в сети много блогов и статей об этом.

Вот еще одно решение: используйте Мастер публикации базы данных для экспорта вашей схемы, безопасности и данных в сценарии sql. Затем вы можете отключить свою текущую БД и воссоздать ее с помощью сценариев.

Звучит глупо, но есть пара преимуществ. Во-первых, нет шансов потерять данные. Ваша исходная БД (если вы не удаляете свою БД при ее удалении!) Безопасна, новая БД будет примерно настолько маленькой, насколько это возможно, и у вас будет два разных снимка текущей базы данных - один готов свернуть, один минифицированный - вы можете выбрать для резервного копирования.

Это может показаться странным, но у меня это сработало, и я написал программу на C# для автоматизации этого.

Шаг 1. Обрежьте журнал транзакций (сделайте резервную копию только журнала транзакций, включив опцию удаления неактивных транзакций)

Шаг 2. Запустите сжатие базы данных, переместив все страницы в начало файлов.

Шаг 3. Еще раз обрежьте журнал транзакций, так как на шаге 2 добавляются записи журнала.

Шаг 4. Снова запустите сжатие базы данных.

Мой урезанный код, использующий библиотеку SQL DMO, выглядит следующим образом:

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

ALTER DATABASE MyDatabase SET RECOVERY SIMPLE

GO

DBCC SHRINKFILE (MyDatabase_Log, 5)

GO

ALTER DATABASE MyDatabase SET RECOVERY FULL

GO

Удалите данные, убедитесь, что модель восстановления проста, затем выполните сжатие (работает либо сжатие базы данных, либо сжатие файлов). Если файл данных по-прежнему слишком велик, И вы используете кучи для хранения данных, то есть без кластеризованного индекса для больших таблиц, тогда у вас может возникнуть проблема с удалением данных из кучи: http://support.microsoft.com/kb/913399

Вам следует использовать:

dbcc shrinkdatabase (MyDB)

Он сократит файл журнала (держите проводник Windows открытым и наблюдайте, как это происходит).

«Поэтому разумно предположить, что теперь можно извлечь много места».

Приносим извинения, если я неправильно понял вопрос, но вы уверены, что это база данных, а не файлы журнала, которые занимают место? Проверьте, в какой модели восстановления находится база данных. Скорее всего, она полная, что означает, что файл журнала никогда не усекается. Если вам не нужна полная запись каждой транзакции, вы сможете перейти на Simple, что приведет к усечению журналов. Вы можете уменьшить базу данных во время процесса. Если все идет хорошо, процесс выглядит так:

1. Сделайте резервную копию базы данных!
2. Перейти на простое восстановление
3. Сжать базу данных (щелкните правой кнопкой мыши по базе данных, выберите все задачи> сжать базу данных -> установите 10% свободного места)
4. Убедитесь, что пространство было освобождено, в противном случае вам, возможно, придется сделать полную резервную копию

Если это не сработает (или вы получите сообщение о том, что "файл журнала заполнен" при попытке переключить режимы восстановления), попробуйте следующее:

1. Резервное копирование
2. Убить все подключения к БД
3. Отключить базу данных (щелкните правой кнопкой мыши> Отключить или щелкните правой кнопкой мыши> Все задачи> Отключить)
4. Удалить файл журнала (ldf)
5. Повторно подключите базу данных
6. Сменить режим восстановления

и т.п.

Я недавно это сделал. Я пытался сделать компактную версию своей базы данных для тестирования в дороге, но никак не мог заставить ее сжаться, сколько бы строк я ни удалил. В конце концов, после многих других команд в этом потоке я обнаружил, что мои кластерные индексы не восстанавливаются после удаления строк. Перестройка индексов позволила мне правильно сжаться.

Это старый вопрос, но я только что наткнулся на него.

Действительно короткий и правильный ответ уже дан и набрал наибольшее количество голосов. То есть как вы сокращаете журнал транзакций, и это, вероятно, проблема OP. А когда журнал транзакций вышел из-под контроля, его часто необходимо сжать обратно, но следует позаботиться о предотвращении в будущем ситуаций, когда журнал выходит из-под контроля. Этот вопрос о dba.se объясняет это. По сути - не позволяйте ему стать таким большим, прежде всего, из-за правильной модели восстановления, ведения журнала транзакций, управления транзакциями и т. д.

Но при чтении этого вопроса об уменьшении файла данных (или даже файла журнала) у меня возникает более серьезный вопрос: Зачем? и что плохого случается, когда вы пытаетесь?. Похоже, что операции сжатия были выполнены. В этом случае это имеет смысл в некотором смысле, потому что выпуски MSDE / Express ограничены максимальным размером БД. Но правильный ответ может заключаться в том, чтобы выбрать версию, подходящую для ваших нужд. И если вы наткнетесь на этот вопрос, пытаясь уменьшить свою производственную базу данных, и это не причина, почему, вы должны задать себе вопрос Зачем?.

Я не хочу, чтобы кто-то искал в Интернете «как уменьшить базу данных», натолкнувшись на это и подумав, что это круто или приемлемо.

Сжатие файлов данных - это особая задача, которую следует выполнять в особых случаях. Учтите, что при сжатии базы данных вы эффективно фрагментируете индексы. Учтите, что при сжатии базы данных вы забираете свободное пространство, в которое база данных может когда-нибудь вырасти, - эффективно тратя время и снижая производительность операции сжатия только для того, чтобы снова увидеть, как база данных снова вырастет.

Я писал об этой концепции в нескольких сообщениях блога об уменьшении размера баз данных. Сначала на ум приходит тот, который называется "Не трогай эту кнопку сжатия". Я говорю об этих концепциях, изложенных здесь, а также о концепции «правильного определения размера» вашей базы данных. Гораздо лучше решить, каким должен быть размер вашей базы данных, спланировать будущий рост и выделить его на эту сумму. Благодаря мгновенной инициализации файлов, доступной в SQL Server 2005 и последующих версиях для файлов данных, стоимость роста ниже, но я по-прежнему предпочитаю иметь правильное начальное приложение - и я гораздо меньше боюсь пробелов в базе данных, чем я сокращение в целом, не думая в первую очередь. :)

Я наткнулся на этот пост, хотя мне нужно было SHRINKFILE в версии MSSQL 2012, что немного сложнее с версий 2000 или 2005 года. Прочитав обо всех рисках и проблемах, связанных с этой проблемой, я закончил тестирование. Короче говоря, лучшие результаты я получил от использования Студия управления MS SQL Server.

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

Не уверен, насколько это будет практично и зависит от размера базы данных, количества таблиц и других сложностей, но я:

1. дефрагментировать физический диск
2. создать новую базу данных в соответствии с моими требованиями, объемом, процентным ростом и т. д.
3. используйте простую задачу ssms для импорта всех таблиц из старой базы данных в новую базу данных
4. создайте скрипт индексов для всех таблиц в старой базе данных, а затем воссоздайте индексы в новой базе данных. расширять по мере необходимости для внешних ключей и т. д.
5. переименуйте базы данных по мере необходимости, подтвердите успешность, удалите старые

Поздний ответ, но может быть полезен кому-то другому

Если ни DBCC ShrinkDatabase / ShrinkFile, ни SSMS (Tasks / Shrink / Database) не помогают, есть инструменты от Quest и ApexSQL, которые могут выполнить эту работу и даже запланировать периодическое сжатие, если оно вам нужно.

Некоторое время назад я использовал последний в бесплатной пробной версии, чтобы сделать это, следуя краткому описанию в конце этой статьи:

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

Все, что вам нужно сделать, это установить ApexSQL Backup, нажать кнопку «Сжать базу данных» на главной ленте, выбрать базу данных в появившемся окне и нажать «Готово».

Я думаю, вы можете удалить весь журнал, переключившись с полного восстановления на простое. Щелкните правой кнопкой мыши свою базу данных и выберите Properties, затем выберите Options и измените

• Recovery mode в Simple
• Containment type в None

Если для модели восстановления установлено значение «Простая» (и включено автоматическое сжатие), возможно, что SQL Server не сможет сжать журнал. Это связано с контрольными точками в журнале (или их отсутствием).

Так что сначала беги

DBCC CHECKDB

в вашей базе данных. После этого операция усадки должна работать как шарм.

Обычно я использую меню Задачи> Сжать> Файлы и выбираю файл журнала с возможностью реорганизации страниц.