Как сжать базу данных sql

Сжатие базы данных

В этой статье приводятся инструкции по сжатию базы данных в SQL Server с использованием обозревателя объектов в SQL Server Management Studio или Transact-SQL.

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

ограничения

• База данных не может быть меньше минимального размера базы данных. Минимальный размер — это первоначальный размер, указанный при создании базы данных, или последний размер, явно установленный операцией изменения размера файла, например, DBCC SHRINKFILE . Если, допустим, база данных была создана с размером 10 МБ и затем увеличилась до 100 МБ, ее можно сжать только до 10 МБ, даже если удалить из нее все данные.
• Невозможно сжать базу данных во время создания ее резервной копии. И наоборот, невозможно создать резервную копию базы данных во время операции сжатия.

Рекомендации

• Просмотр количества свободного (нераспределенного) пространства в базе данных. Дополнительные сведения см. в разделе Отображение данных и сведений о пространстве журнала для базы данных.
• Обратите внимание на следующие сведения при планировании сжатия базы данных.
  • Максимальный эффект от сжатия достигается после операции, при которой создается много неиспользуемого пространства в хранилище, например после объемной инструкции DELETE, усечения или удаления таблицы.
  • Большинству баз данных требуется некоторое свободное пространство для выполнения обычных ежедневных операций. Если сжатие базы данных производится регулярно, но она снова увеличивается в размерах, это означает, что для нормальной работы необходимо свободное пространство. В таких случаях повторное сжатие базы данных бессмысленно. События автоматического увеличения, необходимые для увеличения файлов базы данных, снижают производительность.
  • Операция сжатия не исключает фрагментацию индексов в базе данных и даже, наоборот, приводит к усилению фрагментации. Это еще одна причина, по которой не стоит выполнять регулярное сжатие базы данных.
  • Без достаточных на то оснований не следует устанавливать параметр базы данных AUTO_SHRINK равным ON.

  Разрешения

  Необходимо быть членом предопределенной роли сервера sysadmin или предопределенной роли базы данных db_owner .

  Замечания

  Выполняемые операции сжатия могут блокировать другие запросы к базе данных и могут заблокировать уже выполняющиеся запросы. В SQL Server 2022 (16.x) операции сжатия базы данных имеют WAIT_AT_LOW_PRIORITY параметр. Эта функция является новым дополнительным параметром для DBCC SHRINKDATABASE и DBCC SHRINKFILE . Если новая операция сжатия в режиме WAIT_AT_LOW_PRIORITY не может получить необходимые блокировки из-за длительного выполнения запроса, операция сжатия в конечном итоге истекает через одну минуту и автоматически завершает работу, предотвращая блокировку других запросов. Дополнительные сведения см. в разделе DBCC SHRINKDATABASE.

  Использование среды SQL Server Management Studio

  Сжатие базы данных

  1. В обозревателе объектов подключитесь к экземпляру ядра СУБД SQL Server, а затем разверните этот экземпляр.
  2. Разверните узел Базы данныхи щелкните правой кнопкой мыши базу данных, которую нужно сжать.
  3. В меню наведите указатель мыши на пункт Задачи, затем на пункт Сжатьи выберите команду База данных.
     • База данных Отображает имя выбранной базы данных.
     • Выделенное в данный момент место Отображает суммарное используемое и неиспользуемое пространство для выбранной базы данных.
     • Доступное свободное место Отображает суммарное свободное место для файлов журналов и данных в выбранной базе данных.
     • Реорганизовать файлы перед освобождением неиспользованного места Установка данного флажка эквивалентна выполнению инструкции DBCC SHRINKDATABASE с заданием целевого процентного параметра. Снятие этого флажка равнозначно выполнению процедуры DBCC SHRINKDATABASE с параметром TRUNCATEONLY. По умолчанию этот параметр не выбирается при открытии диалогового окна. Если этот флажок установлен, то пользователь должен задать целевое процентное значение.
     • Максимальное свободное пространство в файлах после сжатия Введите максимальный процент свободного пространства, которое должно остаться в базе данных после ее сжатия. Допустимы значения от 0 до 99.
  4. Нажмите ОК.

  Использование Transact-SQL

  Сжатие базы данных

  1. Соединитесь с ядром СУБД .
  2. На стандартной панели выберите пункт Создать запрос.
  3. Скопируйте приведенный ниже пример в окно запроса и нажмите кнопку Выполнить. В этом примере инструкция DBCC SHRINKDATABASE используется для уменьшения размера данных и файлов журнала в базе данных UserDB и для выделения 10 процентов свободного пространства в базе данных.

  DBCC SHRINKDATABASE (UserDB, 10); GO 

  Продолжение: после сжатия базы данных

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

  См. также

  • Сжатие базы данных tempdb
  • Рекомендации по настройке автоувеличения и автосжатия в SQL Server
  • Файлы и файловые группы базы данных
  • sys.databases (Transact-SQL)
  • sys.database_files (Transact-SQL)

  Далее

  • DBCC SHRINKDATABASE (Transact-SQL)
  • DBCC SHRINKFILE (Transact-SQL)
  • Удаление файлов данных или журнала из базы данных
  • Сжатие файла

  Задача «Сжатие базы данных» (план обслуживания)

  Диалоговое окно Задача «Сжатие базы данных» используется для создания задачи, которая пытается уменьшить размер выбранных баз данных. Перечисленные ниже параметры используются для определения количества неиспользуемого пространства, которое должно остаться в базе данных после сжатия (чем больше процент, тем меньше сжимается база данных). Это значение определяется долей фактических данных в базе данных. Например: 100-мегабайтная база данных, содержащая 60 МБ данных и 40 МБ свободного пространства с заданным значением свободного пространства, равным 50 процентам, будет содержать 60 МБ данных и 30 МБ свободного пространства (поскольку 50 процентов от 60 МБ равно 30 МБ). Удаляется только лишнее пространство в базе данных. Допустимые значения: от 0 до 100.

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

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

  Эта задача выполняет инструкцию DBCC SHRINKDATABASE .

  Параметры

  • Соединение Выберите соединение с сервером, которое будет использоваться для выполнения этой задачи.
  • New Создать новое соединение с сервером для его использования при выполнении этой задачи. Диалоговое окно Создание соединения описано ниже.
  • Базы данных Укажите базы данных, для которых должна выполняться эта задача.
    • Все базы данных Создайте план обслуживания, который выполняет задачи обслуживания для всех баз данных Microsoft SQL Server, кроме tempdb .
    • Все системные базы данных Создайте план обслуживания, который выполняет задачи обслуживания для каждой из системных баз данных SQL Server, кроме tempdb . Для баз данных, созданных пользователями, задачи обслуживания выполняться не будут.
    • Все пользовательские базы данных Создается план обслуживания, по которому задачи обслуживания выполняются для всех баз данных, созданных пользователем. Задачи обслуживания не выполняются в системных базах данных SQL Server.
    • Следующие базы данных Создается план обслуживания, по которому задачи обслуживания должны выполняться только для указанных баз данных. Если выбран этот параметр, необходимо выбрать в списке хотя бы одну базу данных.

    Заметка Планы обслуживания выполняются только для баз данных, уровень совместимости которых 80 или выше. Базы данных с уровнем совместимости 70 или ниже не отображаются.

    Заметка Если количество затронутых объектов велико, построение этого отображения может занять значительное время.

    Диалоговое окно «Новое соединение»

    • Имя подключения Введите имя нового соединения.
    • Выберите или введите имя сервера Выберите сервер для подключения при выполнении этой задачи.
    • Обновить Обновите список доступных серверов.
    • Введите данные для входа на сервер Укажите способ проверки подлинности на сервере.
    • Использовать встроенную систему безопасности Windows NT Подключитесь к экземпляру ядра СУБД SQL Server с помощью проверки подлинности Microsoft Windows.
    • Использовать указанные имя пользователя и пароль Подключитесь к экземпляру ядра СУБД SQL Server с помощью проверки подлинности SQL Server. Этот параметр недоступен.
    • Имя пользователя Укажите имя входа SQL Server, используемое при проверке подлинности. Этот параметр недоступен.
    • Пароль Укажите используемый при проверке подлинности пароль. Этот параметр недоступен.

    См. также

    SQL-Ex blog

    

    DBCC ShrinkDatabase — я хочу сжать базу данных

    Добавил Sergey Moiseenko on Среда, 13 октября. 2021

    Не делайте этого. Вы можете перестать читать эту статью, но просто не делайте этого.

    Эта публикация относится к сжатию файлов базы данных (файлов mdf или ndf), а не сжатию файла журнала. Файл журнала — это совершенно другая тема, хотя ShrinkDatabase действительно сжимает файл журнала.

    • Избыточные операции ввода/вывода, связанные с сжатием.
    • Фрагментация индексов (с наибольшей вероятностью для всех ваших индексов).
    • Избыточные операции ввода/вывода из-за фрагментации индексов.
    • После завершения сжатия вставка или обновление строк, которым требуется больше пространства в базе данных, будут замедляться в результате роста размеров вашего файла данных.

    Базы данных SQL Server очень похожи на пример с магазином игрушек в том, что нет смысла ужимать размер только потому, что пространство не используется сегодня. Опасность может представлять опция автосжатия, которую вы можете включить для вашей базы данных и которая будет регулярно сжимать размер вашей базы данных без вашего участия. Другой опасной альтернативой является наличие DBCC SHRINK DATABASE или SHRINKFILE в задании, выполняемом по расписанию.

    Удаление устаревших данных <> сжатию базы данных

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

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

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

    Вот почему я предлагаю не сжимать базу данных. Команда сжатия базы данных мало что может вам дать, помимо освобождения места на диске. Размер резервных копий определяется не размером файла данных, а числом используемых 8-килобайтных страниц в этом файле. Удаление данных и перестройка индексов поможет ускорить создание бэкапов, время checkdb и работу статистики. При наличии файлов данных, на 10% — 20% превышающих необходимый на текущий момент размер, является хорошей практикой. Этот избыточный размер будет использован при добавлении новых данных, и использование существующего свободного пространства в файле данных оказывается много быстрей, чем расширение файла данных.

    Свободное пространство в файле данных обычно игнорируется в процессе CheckDB, создании резервных копий и в процессе сбора статистики и перестройки индексов, и более всего игнорируется при выполнении обычных запросов.

    Что если из моей базы была удалена большая часть данных?

    Давайте предположим, что мой файл данных однажды имел размер 1Тб (1000Гб). Я удалил устаревшие данные, и таблицы с индексами теперь занимают около 100Гб, или грубо говоря 10% от размера всего файла данных. Должен ли я теперь сжать базу данных?

    • Моя база данных не вырастет снова до 1Тб в течение ближайших года или двух.
    • У меня достаточно времени в нерабочие часы, или времени низкой нагрузки, для сжатия базы данных, возможно, 12 или более часов в зависимости от размера и производительности сервера.
    • У меня нехватка дискового пространства, и необходимо увеличить пространство для других нужд, не связанных с базой данных.
    • Если я сжимаю базу данных, то планирую перестройку или реорганизацию всех моих индексов.
    • Если после сжатия базы данных я собираюсь несколько расширить её (на 10%-20%) в расчете на предполагаемый рост.
    • Я понимаю, что производительность упадет во время сжатия базы данных и после сжатия, пока индексы не будут перестроены.

    А что с сжатием файлов журнала?

    Файлы журнала — это совершенно другой зверь по сравнению с файлами данных. Имеется много случаев, когда сжатие файлов журнала может быть вполне обоснованным, например, для сокращения числа VLF (виртуальный файл журнала) посредством сжатия файла журнала с последующим его наращиванием большими кусками. Другим примером может служить ситуация, когда ваши бэкапы журнала по какой-то причине терпели неудачу, что вызывало чрезмерный рост файла журнала. Тогда сжатие файла журнала обосновано. Это совсем другая ситуация по сравнению с сжатием файлов базы данных. Прежде чем рассматривать вопрос о сжатии файлов журнала, обязательно проведите исследование и разберитесь, о чем идет речь.

    Выводы

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

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

    И не позволяйте своим друзьям сжимать базы данных.

    Как сжать/снизить размеры базы данных в MS SQL?

    При использовании MS SQL появляется проблема, когда размеры расположенных баз данных на физическом носителе увеличиваются до огромных объемов.

    33K открытий

    Одно из решений — это покупка нового жесткого диска с большим объемом памяти. Но тот же самый MS SQL Server предлагает более экономичное решение (бесплатное) — свои собственные функции (как сжатие). Ниже представлены четыре основных метода по решению данной проблемы.

    Метод 1: Использование SQL Server Management Studio

    Шаг 1: Правая кнопка мыши по названию БД → Задачи (Tasks) → Сжать (Shrink) → База данных (Database)

    Шаг 2: Нажимаем на «ОК»

    Готово. Мы видим, что доступное свободное место можно освободить (сжать) на 0.69 МВ (11%).

    Метод 2: Использование Transact SQL Command

    Метод 1: Использование SQL Server Management Studio

    Шаг 1: Открываем наш SQL Server Management Studio

    Шаг 2: Подключаемся к необходимой Базе данных

    Шаг 3: Нажимаем на «Создать запрос» (New Query)

    Шаг 4: После чего в открывшемся окне прописываем соответствующую команду (ниже) и жмем кнопку «Выполнить» (Execute)

    DBCC SHRINKDATABASE (test, 10); GO

    Готово. Кол-во освободившегося места будет такой же, как и в 1-ом методе. Т.к. осуществляется разное исполнение одной и той же задачи.

    Метод 3: Сжатие на уровне строк
    ALTER TABLE tableName REBUILD WITH (DATA_COMPRESSION=ROW)

    Работа данного сжатия осуществляется за счет перевода фиксированного типа данных SQL в переменный тип данных. Используются следующие действия:

    • Хранит тип данных CHAR (фиксированной длины), так чтобы система думала, что они являются типами данными, которые имеют переменную длину,
    • Не применяет сохранение данных, если значения являются 0 и NULL

    Пример: Создадим таблицу на 14 500 строк. В целях безопасности данных, буду демонстрировать только результат. Мы видим, что занимаемое пространство данными составляет 9.7 МВ.

    Осуществим сжатие по строкам.

    Метод 4: Сжатие на уровне страниц
    ALTER TABLE tableName REBUILD WITH (DATA_COMPRESSION=PAGE)

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

    • Данное сжатие позволяет максимизировать кол-во строк, которые хранятся на странице,
    • Повторы данных заменяются ссылками, если происходит сжатие по префиксу.

    Пример: используем ту же самую таблицу на 14 500 строк.

    Осуществим сжатие по страницам.

    Результат: занимаемое пространство данными уменьшилось до 2МВ.

    Различия между сжатием на уровне страниц и строк

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

    Если вам известно, что БД использует огромное количество повторяющихся значений, то лучше использовать «Сжатие на уровне страниц» (Метод 4), т.к. система хранит ссылки на эти значения, а не дублирует данные. В остальных случаях лучше использовать «Сжатие на уровне рядов» (Метод 3). Первые 2 метода используются по желанию.

    Негативные факторы при использовании сжатия:

    • Частое сжатие Базы Данных не рекомендуется, т.к. сжатие приводит к фрагментации таблиц.
    • Размер базы данных никаким образом нельзя сделать меньше,чем минимальный размер этой БД. Пример: если базу данных создали с размером 5 МВ и она увеличилась до 50 МВ, то ее можно сжать только до изначального созданного размера в 5МВ (даже с пустыми столбцами и строками).
    • Чтобы достичь наибольшего эффекта от сжатия, ее нужно применять после операций, которые после своего применения создают большое количество неиспользуемого пространства в БД (удаление таблиц).
    • Происходит увеличение загрузки процессора.

    Сжатие таблицы в MS SQL позволяет существенно сэкономить дисковое пространство. Помимо экономии места, повышается производительность запросов, т.к. уменьшается количество обрабатываемых строк. При правильном выборе метода, мы можем увидеть значительное освобождение места для записи новых данных. Таблица на 14 500 строк это доказала (уменьшение размера в 2 и в 5 раз).

    Похожие публикации:

    1. Как изменить тип элементов списка python
    2. Как сделать кнопку неактивной html
    3. Как установить android tv на телевизор
    4. Что такое solid java