Почему база 1С на SQL Server значительно увеличивается в размере после тестирования и исправления и как её уменьшить?

Многие администраторы и разработчики 1С сталкиваются с парадоксальной ситуацией: после выполнения стандартной процедуры Тестирование и исправление (ТиИ), которая, казалось бы, должна оптимизировать данные, физический размер базы данных на SQL Server существенно увеличивается. Давайте разберем по шагам, почему это происходит, как устроены механизмы хранения данных в СУБД и какими способами можно реально уменьшить объем «распухшей» базы. При обслуживании нескольких информационных баз регулярный запуск сервисных процедур и резервного копирования можно упростить, настроив автоматическое архивирование, тестирование и обновление баз 1С.

Выясним причину: почему база растет при ТиИ?

Для начала проанализируем ситуацию, описанную в теме. Исходная база ЗУП занимает 41 Гб, файл выгрузки .dt — 2 Гб, а после загрузки в новую базу и проведения ТиИ объем подскакивает до 53 Гб. Нам важно понимать, что файл .dt — это сжатый архив данных, и его размер никогда не будет соответствовать размеру базы в SQL. Однако двукратный рост после тестирования объясняется спецификой работы SQL Server и платформы 1С.

Основная причина кроется в пункте Реструктуризация таблиц информационной базы. Рассмотрим подробнее, что происходит «под капотом»:

1. Копирование таблиц: При реструктуризации SQL Server не меняет структуру существующей таблицы «на лету». Он создает новую временную таблицу с новой структурой, переносит туда все данные из старой, удаляет старую и переименовывает новую. В момент переноса данных в базе фактически сосуществуют две копии одной и той же таблицы, что мгновенно удваивает занимаемое место.
2. Резервирование места (High Water Mark): SQL Server работает по принципу захвата пространства. Когда временные таблицы удаляются, освободившееся место внутри файла .mdf не возвращается операционной системе автоматически. Оно помечается как «свободное внутри файла» для будущих операций. С точки зрения Windows файл базы данных остается огромным.
3. Лог транзакций (LDF): Любое массовое изменение данных, включая создание индексов и перенос записей, фиксируется в журнале транзакций. Если база находится в модели восстановления Full, файл .ldf будет расти до тех пор, пока не будет сделан бэкап лога.
4. Создание объектов по битым ссылкам: Если при ТиИ выбраны опции «Создавать объекты» при наличии битых ссылок, в базе физически появляются новые записи, что также увеличивает её объем.

Шаг 1. Анализ распределения места в таблицах

Прежде чем приступать к сжатию, нам необходимо выяснить, какие именно таблицы занимают больше всего места. Для этого воспользуемся SQL-скриптом, который выведет детальную информацию о данных и индексах каждой таблицы. Выполним следующий код в SQL Server Management Studio:

USE [Имя_Вашей_Базы]
GO
CREATE TABLE #temp (
table_name sysname ,
row_count INT,
reserved_size VARCHAR(50),
data_size VARCHAR(50),
index_size VARCHAR(50),
unused_size VARCHAR(50))
SET NOCOUNT ON
INSERT #temp
EXEC sp_msforeachtable 'sp_spaceused ''?'''
SELECT a.table_name,
a.row_count,
COUNT(*) AS col_count,
a.data_size
FROM #temp a
INNER JOIN information_schema.columns b
ON a.table_name collate database_default
= b.table_name collate database_default
GROUP BY a.table_name, a.row_count, a.data_size
ORDER BY CAST(REPLACE(a.data_size, ' KB', '') AS integer) DESC
DROP TABLE #temp

Проанализируем результаты. Если лидерами роста являются таблицы с именами типа _InfoReg... (Регистры сведений) или _AccumReg... (Регистры накопления), это нормально для ЗУП. Если же «пухнут» таблицы Config или ConfigSave, возможно, в базе хранится избыточное количество версий конфигурации.

Шаг 2. Освобождение неиспользуемого места (Shrink)

Поскольку после ТиИ внутри файла .mdf остается много пустого места, нам нужно выполнить операцию Shrink (сжатие). Разберем, как это сделать правильно. Важно помнить, что сжатие файлов данных вызывает сильную фрагментацию индексов, поэтому его следует делать только после массовых операций типа реструктуризации.

Для уменьшения файла данных используем команду:

DBCC SHRINKDATABASE (N'Имя_Вашей_Базы');

Или через интерфейс SSMS: Задачи -> Сжать -> База данных. Это вернет неиспользуемое пространство операционной системе, и размер файла .mdf на диске уменьшится до реального объема данных.

Шаг 3. Использование сжатия данных на уровне SQL (Data Compression)

Если после шринка размер базы все еще кажется избыточным, рассмотрим современный метод — Data Compression (сжатие страниц или строк). Начиная с SQL Server 2016 SP1, эта функция доступна во всех редакциях, включая Standard. Для баз 1С, где много пустых значений и повторяющихся типов данных, это дает колоссальный эффект.

Для применения сжатия ко всем разделам конкретной таблицы выполним команду:

ALTER TABLE [Имя_Таблицы] REBUILD PARTITION = ALL 
WITH (DATA_COMPRESSION = PAGE);

Рассмотрим преимущества этого подхода: сжатие уровня PAGE позволяет упаковать данные в 2–5 раз. При этом нагрузка на процессор немного возрастает, но скорость чтения с диска увеличивается, так как серверу нужно считывать меньше физических блоков. Это отличный способ «ужать» базу без потери функциональности.

Шаг 4. Настройка индексов и Fill Factor

Выясним еще одну причину «пухлости» — коэффициент заполнения страниц индексов (Fill Factor). При выполнении реиндексации в рамках ТиИ, если в настройках сервера Fill Factor установлен, например, на 80%, SQL Server намеренно оставляет 20% каждой страницы данных пустой. Это нужно для ускорения вставки новых записей, но увеличивает общий размер базы на те же 20%.

Если база используется преимущественно для отчетов (как это часто бывает в ЗУП в периоды между расчетами), установим Fill Factor в значение 0 или 100. Это заставит сервер упаковывать индексы максимально плотно.

Шаг 5. Очистка служебных данных 1С

Посмотрим на проблему со стороны самой платформы 1С. База может расти из-за:

• Версионирования объектов: В ЗУП история изменений объектов может занимать огромный объем. Рекомендуется настроить регламентное задание по очистке версий старше 6 месяцев.
• Журнала регистрации: Проверьте, не хранится ли журнал регистрации в самой базе данных. Если да, его стоит выгрузить в файл или сократить.
• Итогов регистров: Пересчет итогов при ТиИ может временно увеличивать базу, так как пересоздаются таблицы итогов. Убедитесь, что итоги рассчитаны по текущую дату.

Подведем итог: рост базы после ТиИ — это нормальное поведение SQL Server из-за механизма реструктуризации. Чтобы вернуть базу к минимальному размеру, необходимо выполнить последовательность: ТиИ -> Реиндексация SQL -> Shrink базы. А для радикального уменьшения объема стоит внедрить сжатие таблиц DATA_COMPRESSION = PAGE.