У меня есть база данных MS SQL Server 2000 объемом 9 ГБ, в которой содержится около 1 ГБ реальных данных. Он находится на общем веб-хосте. Мне нужно уменьшить размер, чтобы не платить чрезмерную плату за хостинг.
Я пробовал использовать DBCC SHRINKDATABASE и DBCC SHRINKFILE. Они оба не сжимают базу данных. (И да, я знаю, что мне не следует их использовать.)
DBCC SHRINKDATABASE (имя_бд) возвращает:
DbId FIleId CurrentSize MinimumSize UsedPages EstimatedPages 23 1 1114808 128 1113824 1113824 23 2 63 63 56 56
DBCC SHRINKFILE (1) возвращает:
DbId FIleId CurrentSize MinimumSize UsedPages EstimatedPages 23 1 1114808 128 1113824 1113824
На странице общих свойств указан размер 8 709 МБ. Доступное пространство указано как 0 МБ. На странице свойств файлов указан главный файл с начальным размером 8 652 МБ.
DBCC SHOWCONTIG (большая таблица) возвращает:
- Pages Scanned................................: 807 - Extents Scanned..............................: 103 - Extent Switches..............................: 102 - Avg. Pages per Extent........................: 7.8 - Scan Density [Best Count:Actual Count].......: 98.06% [101:103] - Logical Scan Fragmentation ..................: 0.37% - Extent Scan Fragmentation ...................: 1.94% - Avg. Bytes Free per Page.....................: 68.5 - Avg. Page Density (full).....................: 99.15%
Бег sp_spaceused, Я получаю это:
размер базы данных: 9131.94 МБ
нераспределенное пространство: 377,95 МБ
Бег sp_spaceusused bigTable, Я получаю это:
Ряды: 56095
Зарезервировано: 6 419 736 КБ
Данные: 252 656 КБ
Размер индекса: 4,640 КБ
Неиспользуемые: 6 162 440 КБ
Бег sp_spaceusused bigTable2, Я получаю это:
Ряды: 2791
Зарезервировано: 2 362 744 КБ
Данные: 114 232 КБ
Размер_индекса: 200 КБ
Неиспользовано: 2248312 КБ
Все остальные столы маленькие.
Я попросил свой хост создать резервную копию базы данных, удалить и воссоздать ее с меньшим начальным размером, а затем восстановить. Они попробовали это, и это был их ответ: «Мы выполнили шаги, но это не устранило проблему, кстати, SQL-сервер увеличил размер базы данных, и его текущий размер составляет более 9 ГБ». (До того, как они это сделали, было 7 ГБ.)
Возможно ли, что моей базе данных потребуется 8 ГБ неиспользуемого пространства? Если предположить, что это не так, какие еще варианты у меня есть для его уменьшения?
Мне удалось загрузить копию с моего веб-хостинга для работы на моем локальном компьютере. Я установил пробную версию SQL Server 2008 и прикрепил базу данных. Я запустил ALTER INDEX ALL ON BIGTABLE REORGANIZE, ALTER INDEX ALL ON BIGTABLE REBUILD и DBCC SHRINKDATABASE. Ничего не изменилось. Я все еще получаю те же базовые ответы от различных команд, перечисленных выше.
Каков режим восстановления этой базы данных? Перед запуском DBCC_SHRINKDATABASE необходимо создать резервную копию журналов транзакций. Я предполагаю, что режим восстановления полный? Что, вероятно, лучше всего в большинстве случаев.
Как часто выполняется резервное копирование вашей базы данных для этой БД? и когда было выполнено последнее резервное копирование журнала транзакций.
вы также можете попробовать DBCC SHRINKDATABASE (DatabaseName, TRUNCATEONLY);
Когда они удалили базу данных, воссоздали ее с меньшим размером и восстановили базу данных, ничего не должно было измениться. Когда вы восстанавливаете базу данных, первое, что делает SQL Server, - это удаляет существующую базу данных.
Использование команды DBCC SHRINKFILE - правильный метод. Вам просто нужно получить SQL, чтобы вы могли перемещать некоторые данные.
Вам может потребоваться перестроить индексы в таблицах, чтобы сжать страницы данных, чтобы позволить SQL Server удалить часть свободного пространства из таблиц. Когда вы используете DBCC SHRINKFILE для сжатия физического файла, он будет перемещаться по страницам данных внутри файла, так что все пустые страницы находятся в конце файла, а затем он может освободить страницы данных. Однако, если ваши страницы заполнены только частично, SQL Server не объединит их, чтобы заполнить больше при использовании этой команды.
Однако это можно сделать с помощью команд переиндексации (фактические команды различаются в зависимости от используемой версии SQL Server). Это несколько сжимает записи (в зависимости от уровня коэффициента заполнения, указанного при создании таблицы / индексов.
Вы также должны знать, что в SQL Server 2000 и ранее возникали проблемы при работе с типами данных text и ntext и при перемещении данных внутри страниц данных. Эти более старые версии SQL Server оставляли фрагменты данных, лежащие вокруг файлов данных, которые вы не могли бы очистить, поскольку они не связаны ни с какими строками.
Ваши проблемы, скорее всего, связаны с тем, что удаленный текст или нетекстовые данные не освобождают удаленное пространство - это то же самое для всех версий страницы.
Предложение перестроить индексы было ложным - SHOWCONTIG ясно и sp_spaceused ясно показывают, что потраченное впустую пространство отсутствует в индексах. Резервное копирование предложения журнала также было поддельным - sp_spaceused показывает, что дополнительное пространство находится в файлах данных, а не в журнале.
Есть ли в таблице какие-либо столбцы text / ntext? Если это так, то единственный способ вернуть это пространство в 2000 и ранее - это создать новую таблицу и затем экспортировать / импортировать в нее данные. Только в 2005 году я добавил LOB-уплотнение для сжатия и ALTER INDEX ... REORGANIZE.
Кроме того, имейте в виду, что при сжатии возникли две ошибки, которые фактически увеличили бы объем пространства, занимаемого данными text / ntext - убедитесь, что вы используете SP4.
Спасибо
В качестве мысли, можете ли вы сделать резервную копию и загрузить ее локально, затем обновить ее до SQL 2008, а затем попытаться сжать ее на своей собственной машине? Если работает, то загрузил резервную копию. Отбросьте текущий и замените его новым. Также это означает, что вам необходимо обновить свою учетную запись до SQL 2008, но, по крайней мере, вы можете локально проверить, будет ли она работать.
Дэвид,
Не могли бы вы опубликовать определения больших таблиц?
<ignore ref="see comments" editor="self"> Одна общая проблема в результате получаются очень большие безусадочные таблицы это неправильный выбор типов данных или структур данных. Таблицы могут использовать типы данных фиксированного размера для данных переменного размера. Например, если у вас есть поле, определенное как char (100), и многие записи содержат менее полных 100 символов (или даже NULL, если на то пошло), таблица все равно потребует всех 10 символов хранения. В этом случае лучшим выбором будет varchar (100), который будет хранить только данные и не будет заполнять данные без необходимости. </ignore>
Некоторые другие возможности заключаются в том, что ваш хостинг-провайдер может использовать квалификаторы TRUNCATEONLY или NOTRUNCATE (и тем самым не разрешать перенос данных на нераспределенные страницы, полностью пропуская точку вашего запроса), или они могут неправильно указывать целевой процент (целевой процент - это объем свободного пространства относительно данных, которые вы хотите сохранить после операции сжатия). В обоих этих случаях вы можете просто запросить команды SQL, которые они запускают, или просмотреть снимок экрана диалогового окна до выдачи директивы сжатия, если они используют графический интерфейс для сжатия базы данных.
Надеюсь, это поможет.
Джессика
Я знаю ответ на старый пост, но он может помочь другим: может быть, вам не хватает выполнения инструкции «контрольной точки»? Я испытал, что запуск этой команды сбрасывает изменения на диск, позволяя командам DBCC shrink * выполняться должным образом, тогда как они этого не делали до запуска оператора контрольной точки: https://docs.microsoft.com/en-us/sql/t-sql/language-elements/checkpoint-transact-sql?view=sql-server-ver15