Почему база SQL Server снова растет после сжатия и как правильно оптимизировать её объем

← На главную

Ситуация, когда база данных 1С на SQL Server 2012 после выполнения операции Shrink (сжатия) возвращается к исходному размеру в течение суток, является классической проблемой. Многие администраторы пытаются высвободить место на диске, не учитывая внутреннюю логику работы MS SQL Server и механизмы обслуживания индексов. Разберем подробно, почему так происходит, и какие шаги необходимо предпринять для стабильной работы системы.

Причина циклического роста: Конфликт Сжатия и Перестроения индексов

Проанализируем механику взаимодействия двух операций, которые в данном случае противоречат друг другу: Сжатие (Shrink) и Перестроение индексов (Index Rebuild).

Операция Shrink: Когда мы запускаем сжатие, SQL Server перемещает страницы данных из конца файла в любое свободное место в начале файла. При этом физический порядок страниц нарушается, что приводит к катастрофической фрагментации индексов (она может достигать 90-100%).
Регламентное задание: Каждую ночь в плане обслуживания срабатывает Index Rebuild.
Механика Rebuild: Чтобы перестроить индекс, серверу нужно создать его новую структуру «с нуля». Для этого SQL Server требуется свободное пространство внутри файла базы данных. Если мы предварительно «выжали» всё свободное место через Shrink, серверу не остается ничего другого, кроме как заново расширить файл базы данных на диске (Autogrowth).

Таким образом, мы получаем «смертельную петлю»: днем мы сжимаем базу, вызывая фрагментацию, а ночью сервер расширяет её обратно, чтобы устранить эту фрагментацию. База всегда будет занимать столько места, сколько ей нужно для выполнения регламентных операций.

Проблема маленького шага автоувеличения (Autogrowth)

В рассматриваемой ситуации установлено автоувеличение 20 МБ. Для базы объемом 570 ГБ — это критическая ошибка настройки. Рассмотрим, к чему это приводит:

Фрагментация на уровне файловой системы: Когда базе нужно вырасти на 1 ГБ во время ночного обслуживания, она выполняет 50 операций расширения файла. В итоге файл .mdf на жестком диске физически разбивается на тысячи мелких фрагментов. Это существенно замедляет дисковые операции чтения и записи.

Рекомендация: Установите фиксированный шаг автоувеличения. Для базы такого объема оптимальным будет значение 512 МБ или 1024 МБ. Это позволит SQL Server захватывать пространство крупными блоками, снижая нагрузку на операционную систему.

Оптимизация регламентного обслуживания индексов

Выясним, действительно ли нужно каждую ночь выполнять полное перестроение (Rebuild) всех индексов. Это очень тяжелая операция, которая требует огромного количества дискового пространства и ресурсов процессора. Рассмотрим более гибкий подход.

Вместо тотального перестроения рекомендуется использовать скрипты, которые анализируют уровень фрагментации каждого индекса в отдельности:

Если фрагментация от 5% до 30% — выполняем Reorganize (реорганизация). Она проходит быстрее и не требует значительного свободного места, так как работает внутри существующих страниц.
Если фрагментация более 30% — выполняем Rebuild (перестроение).

Рассмотрим пример логики, которую можно внедрить в Maintenance Plan или запустить через T-SQL:


-- Пример логики выбора метода обслуживания
ALTER INDEX ALL ON [Имя_Таблицы] REORGANIZE; -- Если фрагментация умеренная
GO
ALTER INDEX ALL ON [Имя_Таблицы] REBUILD WITH (FILLFACTOR = 80, ONLINE = ON); -- Если фрагментация высокая
GO

Обратим внимание на параметр FILLFACTOR. Если мы установим его, например, в значение 80, то при перестроении SQL Server будет оставлять 20% свободного места на каждой странице данных для будущих записей 1С. Это предотвратит немедленный рост базы при вводе новых документов.

Как реально уменьшить объем бэкапа и занимаемое место

Если ваша главная цель — экономия места на дисках и ускорение передачи бэкапов по сети (например, на FTP), то сжимать саму базу (файл .mdf) — это тупиковый путь. Проанализируем правильные методы:

Сжатие бэкапов (Backup Compression): Начиная с SQL Server 2012 Standard, доступна опция сжатия резервных копий «на лету». Это позволяет уменьшить размер файла бэкапа в 3–5 раз без деградации производительности базы данных. Бэкап базы 570 ГБ может весить около 100-150 ГБ.
Мгновенная инициализация файлов (Instant File Initialization): Убедитесь, что у учетной записи, под которой запущен SQL Server, есть право Perform volume maintenance tasks. Это позволит серверу мгновенно расширять файлы базы без заполнения их нулями, что ускорит ночное обслуживание.
Модель восстановления: Если вам не нужна возможность восстановления на любую секунду времени, модель Simple (как у автора) — верный выбор для 1С, так как она автоматически очищает лог транзакций. Однако помните, что при Simple вы теряете данные с момента последнего полного бэкапа.

Итоговый алгоритм действий

Подведем итог и составим план действий для оптимизации системы:

Перестаньте использовать Shrink. Если в базе стабильно остается 20-25% свободного места — это нормально. Это «дыхание» базы, необходимое для индексов.
Настройте Autogrowth. Увеличьте шаг с 20 МБ до 1024 МБ.
Измените план обслуживания. Вместо Index Rebuild для всех таблиц используйте интеллектуальные скрипты обслуживания (например, популярный скрипт Олы Халленгрена), которые выбирают между Reorganize и Rebuild.
Включите сжатие бэкапов. В настройках SQL Server или в команде бэкапа добавьте WITH COMPRESSION.
Проверьте дисковую подсистему. Если бэкап 570 ГБ идет 2 часа — это признак сильной фрагментации файлов или низкой скорости дисков. После настройки правильного автоувеличения и отказа от постоянных Shrink, скорость должна вырасти.

Помните: свободное место внутри файла базы данных — это не мусор, а ресурс, который SQL Server использует для эффективной работы индексов и быстрой записи данных 1С.