Как мне сжать мою базу данных SQL Server?
У меня есть база данных размером почти 1,9 ГБ, и MSDE2000 не позволяет использовать базы данных, размер которых превышает 2,0 ГБ.
Мне нужно уменьшить эту БД (и многие другие, подобные этому, в разных местах клиентов).
Я нашел и удалил много сотен из тысяч записей, которые считаются ненужными: на эти записи приходится большой процент некоторых основных (самых больших) таблиц в базе данных. Поэтому разумно предположить, что много места теперь должно быть извлечено.
Так что теперь мне нужно сжать БД для учета отсутствующих записей.
- Я исполняю
DBCC ShrinkDatabase('MyDB')...... Нет эффекта. - Я пробовал различные термоусадочные средства, предоставляемые в MSSMS.... По-прежнему безрезультатно.
- Я сделал резервную копию базы данных и восстановил ее... По-прежнему безрезультатно.
Еще 1.9Гб
Зачем?
Какую бы процедуру я в итоге ни нашел, ее необходимо воспроизвести на клиентском компьютере, не имеющем доступа ни к чему, кроме OSql или аналогичного.
15 ответов
ALTER DATABASE MyDatabase SET RECOVERY SIMPLE
GO
DBCC SHRINKFILE (MyDatabase_Log, 5)
GO
ALTER DATABASE MyDatabase SET RECOVERY FULL
GO
DBCC SHRINKDATABASE работает для меня, но это его полный синтаксис:
DBCC SHRINKDATABASE ( database_name, [target_percent], [truncate] )
где target_percent желаемый процент свободного места, оставшегося в файле базы данных после сокращения базы данных.
А также truncate Параметр может быть:
NOTRUNCATE
Вызывает сохранение освобожденного файлового пространства в файлах базы данных. Если не указано, освобожденное файловое пространство освобождается операционной системой.
TRUNCATEONLY
Вызывает освобождение неиспользуемого пространства в файлах данных в операционной системе и сжимает файл до последней выделенной степени, уменьшая размер файла без перемещения каких-либо данных. Не делается попытка переместить строки на нераспределенные страницы. target_percent игнорируется, когда используется TRUNCATEONLY.
... и да no_one правильно, сокращение базы данных не очень хорошая практика, например:
Сжатие в файлах данных - это отличный способ внести существенную логическую фрагментацию, поскольку он перемещает страницы из конца выделенного диапазона файла базы данных куда-то впереди файла...
Сжатие базы данных может иметь большое значение для базы данных, сервера.... подумайте много об этом, прежде чем сделать это!
в сети есть много блогов и статей об этом.
Это может показаться странным, но для меня это сработало, и я написал программу на 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);
Это старый вопрос, но я только что натолкнулся на него.
Действительно короткий и правильный ответ уже дан и имеет большинство голосов. Именно так вы уменьшаете журнал транзакций, и это, вероятно, проблема ОП. А когда журнал транзакций выходит из-под контроля, его часто приходится сокращать обратно, но следует позаботиться о том, чтобы в будущем не выходить из-под контроля журналы. Этот вопрос на dba.se объясняет это. В основном - не позволяйте этому стать настолько большим во-первых через надлежащую модель восстановления, обслуживание журнала транзакций, управление транзакциями и т. Д.
Но больший вопрос в моей голове, когда я читаю этот вопрос об уменьшении файла данных (или даже файла журнала),почему? а какие плохие вещи случаются при попытке? Похоже, что операции сжатия были сделаны. Теперь в этом случае это имеет смысл в некотором смысле - потому что издания MSDE/Express ограничены максимальным размером БД. Но правильным ответом может быть поиск правильной версии для ваших нужд. И если вы натолкнулись на этот вопрос, пытаясь уменьшить производственную базу данных, и это не причина, почему, вы должны спросить себя, почему? вопрос.
Я не хочу, чтобы кто-то искал в Интернете "как сжать базу данных", сталкиваясь с этим и думая, что это круто или приемлемо.
Сжатие файлов данных - это особая задача, которая должна быть зарезервирована для особых случаев. Учтите, что когда вы уменьшаете базу данных, вы эффективно фрагментируете свои индексы. Учтите, что когда вы уменьшаете базу данных, вы забираете свободное пространство, в которое однажды база данных может снова вырасти, - эффективно тратите свое время и наносите удар по производительности операции сжатия только для того, чтобы увидеть, как БД снова растет.
Я написал об этой концепции в нескольких сообщениях в блоге о сокращении баз данных. Это первое, что называется " Не трогай эту кнопку сжатия" приходит на ум первым. Я говорю об этих концепциях, изложенных здесь, а также о концепции "правильного определения размера" вашей базы данных. Гораздо лучше решить, каким должен быть размер вашей базы данных, спланировать будущий рост и распределить его на эту сумму. Благодаря возможности мгновенной инициализации файлов, доступной в SQL Server 2005 и более поздних версиях для файлов данных, стоимость прироста ниже - но я все же предпочитаю иметь правильное начальное приложение - и я гораздо меньше боюсь пробелов в базе данных, чем сжимается вообще без мыслей в первую очередь.:)
Поздний ответ, но может быть полезным для кого-то другого
Если ни DBCC ShrinkDatabase/ShrinkFile, ни SSMS (Tasks/Shrink/Database) не помогают, есть инструменты от Quest и ApexSQL, которые могут выполнить работу и даже запланировать периодическое сжатие, если вам это нужно.
Я использовал последний в бесплатной пробной версии, чтобы сделать это некоторое время назад, следуя короткому описанию в конце этой статьи:
Все, что вам нужно сделать, это установить ApexSQL Backup, нажать кнопку "Сжать базу данных" на главной ленте, выбрать базу данных в появившемся окне и нажать "Готово".
Вы должны использовать:
dbcc shrinkdatabase (MyDB)
Это сократит файл журнала (оставьте проводник Windows открытым и убедитесь, что это происходит).
Вам также необходимо уменьшить отдельные файлы данных.
Однако не стоит сокращать базы данных. Например, смотрите здесь
Вот еще одно решение: используйте мастер публикации баз данных, чтобы экспортировать вашу схему, безопасность и данные в сценарии sql. Затем вы можете перевести свою текущую БД в автономный режим и заново создать ее с помощью сценариев.
Звучит глупо, но есть пара преимуществ. Во-первых, нет никакой возможности потерять данные. Ваша исходная база данных (если вы не удаляете свою БД при ее удалении!) Безопасна, новая БД будет примерно такой маленькой, как может быть, и у вас будет два разных снимка вашей текущей базы данных - один готов свернуть, один минимизированный - вы можете выбрать для резервного копирования.
"Поэтому разумно предположить, что много места теперь должно быть извлечено".
Извиняюсь, если я неправильно понял вопрос, но уверены ли вы, что база данных, а не файлы журнала занимают место? Проверьте, в какой модели восстановления находится база данных. Скорее всего, она в полном объеме, что означает, что файл журнала никогда не усекается. Если вам не нужна полная запись каждой транзакции, вы сможете изменить ее на Simple, что приведет к обрезанию журналов. Вы можете уменьшить базу данных во время процесса. Если предположить, что все идет хорошо, процесс выглядит следующим образом:
- Сделайте резервную копию базы данных!
- Изменить на Простое восстановление
- Сжатие БД (щелкните правой кнопкой мыши БД, выберите все задачи> Сожмите БД -> установите 10% свободного места)
- Убедитесь, что пространство было освобождено, в противном случае вам может потребоваться сделать полную резервную копию
Если это не работает (или вы получаете сообщение "файл журнала заполнен" при попытке переключения режимов восстановления), попробуйте следующее:
- Резервное копирование
- Убить все подключения к БД
- Отсоединить базу данных (щелкните правой кнопкой мыши> Отсоединить или щелкните правой кнопкой мыши> Все задачи> Отключить)
- Удалить файл журнала (ldf)
- Прикрепите БД
- Изменить режим восстановления
и т.п.
Я наткнулся на этот пост, хотя мне нужно было SHRINKFILE на версии MSSQL 2012, что немного сложнее, чем версии 2000 или 2005. После ознакомления со всеми рисками и проблемами, связанными с этой проблемой, я закончил тестирование. Короче говоря, лучшие результаты я получил от использования MS SQL Server Management Studio.
Right-Click the DB -> TASKS -> SHRINK -> FILES -> select the LOG file
Удалите данные, убедитесь, что модель восстановления проста, а затем уменьшите (работает либо сжатие базы данных, либо сжатие файлов). Если файл данных по-прежнему слишком велик, и вы используете кучи для хранения данных, то есть нет кластеризованного индекса для больших таблиц, то у вас может возникнуть эта проблема с удалением данных из кучи: http://support.microsoft.com/kb/913399
Если для модели восстановления установлено значение "Простая" (и включено автоматическое сжатие), возможно, что SQL Server не сможет сжать журнал. Это связано с контрольными точками в журнале (или их отсутствием).
Так что сначала беги
DBCC CHECKDB
в вашей базе данных. После этого операция усадки должна работать как шарм.
Обычно я использую меню " Задачи"> "Сжать"> "Файлы" и выбираю файл журнала с возможностью реорганизации страниц.
Вы также должны изменить минимальный размер данных и файлов журналов. DBCC SHRINKDATABASE сократит данные внутри уже выделенных вами файлов. Чтобы сжать файл до размера, который меньше его минимального размера, используйте DBCC SHRINKFILE и укажите новый размер.
Я думаю, что вы можете удалить весь свой журнал с переключением с полного на простое восстановление. Щелкните правой кнопкой мыши на вашей базе данных и выберите Properties и выберите Options и изменить
Recovery modeвSimpleContainment typeвNone
Я недавно сделал это. Я пытался сделать компактную версию своей базы данных для тестирования в дороге, но я просто не мог заставить ее сжаться, независимо от того, сколько строк я удалил. В конце концов, после многих других команд в этом потоке, я обнаружил, что мои кластерные индексы не восстанавливались после удаления строк. Перестройка моих индексов сделала это таким образом, чтобы я мог правильно сжаться.
Не уверен, насколько это будет практично, и в зависимости от размера базы данных, количества таблиц и других сложностей, но я:
- дефрагментировать физический диск
- создать новую базу данных в соответствии с моими требованиями, пространством, процентным ростом и т. д.
- используйте простую задачу ssms для импорта всех таблиц из старой базы данных в новую базу данных
- Сценарий индексов для всех таблиц в старой базе данных, а затем воссоздать индексы в новой базе данных. расширять по мере необходимости для внешних ключей и т. д.
- при необходимости переименуйте базы данных, подтвердите успешность, удалите старую