Почему одна и та же база данных 1С на SQL Server 2017 весит значительно больше, чем на SQL Server 2012, и как решить проблему со сжатием?

← На главную

Перенос баз данных 1С:Предприятие со старых версий СУБД на более современные — стандартная задача администрирования. Однако часто технические специалисты сталкиваются с необъяснимым на первый взгляд поведением: одна и та же база данных после миграции с SQL Server 2012 на SQL Server 2017 увеличивается в размерах в полтора-два раза (например, с 15 Гб до 24,5 Гб), а стандартная процедура сжатия (Shrink) не приносит ожидаемого результата. Особенно остро встает вопрос, когда файл лога транзакций (.ldf) категорически отказывается уменьшаться.

В этой статье мы подробно разберем внутренние механизмы работы СУБД MS SQL Server, выясним причины такого поведения системы и рассмотрим эффективные методы оптимизации дискового пространства.

Причина 1. Изменение механизма выделения страниц (Mixed Page Allocation)

Это наиболее частая и скрытая причина раздувания именно 1С-ных баз данных на версиях СУБД SQL Server 2016 и старше (включая SQL Server 2017). Разберем физику этого процесса подробнее.

Данные в SQL Server хранятся в страницах объемом 8 Кб. Восемь физически последовательных страниц объединяются в блок, который называется экстентом (Extent) объемом 64 Кб. Существует два типа экстентов:

Смешанные (Mixed Extents) — в них могут храниться страницы, принадлежащие разным таблицам или индексам.
Однородные (Uniform Extents) — полностью принадлежат одной таблице или индексу.

В SQL Server 2012 по умолчанию первые 8 страниц для любой таблицы выделялись из смешанных экстентов. Это позволяло экономить место: если таблица пустая или содержит всего одну строку, она занимала 8 Кб, а не 64 Кб.

Начиная с версии SQL Server 2016, поведение по умолчанию изменилось. Параметр MIXED_PAGE_ALLOCATION теперь по умолчанию отключен (OFF). Это означает, что под любую новую таблицу или индекс сразу же выделяется полноценный однородный экстент размером 64 Кб.

Почему это критично для 1С? Конфигурации 1С содержат тысячи (иногда десятки тысяч) таблиц. Многие из них в конкретной информационной базе остаются пустыми или содержат минимальное количество записей. В SQL Server 2012 они хранились максимально компактно. При переносе на SQL Server 2017 и последующей реиндексации СУБД выделяет под каждую такую пустую таблицу и ее индекс по 64 Кб. В масштабах тысяч таблиц это приводит к лавинообразному увеличению файла данных (.mdf) «из воздуха».

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

ALTER DATABASE [Имя_Вашей_Базы] SET MIXED_PAGE_ALLOCATION ON;

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

Причина 2. Настройки коэффициента заполнения индексов (Fill Factor)

Проанализируем еще один важный параметр — Fill Factor (коэффициент заполнения индексов). При переносе базы на новый сервер настройки СУБД по умолчанию могли измениться.

Если на старом сервере Fill Factor был равен 100% (или 0), это означало, что страницы данных при реиндексации упаковывались максимально плотно. Если на новом SQL Server 2017 в настройках СУБД или в планах обслуживания установлен рекомендуемый для высоконагруженных систем 1С коэффициент заполнения 80%, то при первой же переиндексации SQL Server начнет оставлять по 20% пустого места на каждой странице для будущих записей. Это автоматически увеличит размер базы данных примерно на четверть, зато снизит фрагментацию при активной вставке данных.

Причина 3. Использование встроенного хранилища запросов (Query Store)

В SQL Server 2016 появился замечательный инструмент мониторинга производительности — Query Store. Если при переносе на SQL Server 2017 этот инструмент оказался включен для вашей базы данных, он начинает непрерывно собирать статистику выполнения запросов, планы выполнения и данные о загрузке процессора, записывая всё это непосредственно внутрь файла данных (.mdf).

В зависимости от интенсивности работы пользователей и настроек хранения, Query Store может незаметно забрать под свои нужды от нескольких гигабайт до десятков процентов от общего объема базы данных. Проверить его состояние и при необходимости отключить или ограничить его аппетиты можно через свойства базы данных в среде SQL Server Management Studio (SSMS).

Как детально проанализировать распределение места в таблицах

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

Запустим следующий T-SQL скрипт в контексте исследуемой базы данных:


SELECT 
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 AS TotalSpaceKB,
    CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,
    SUM(a.used_pages) * 8 AS UsedSpaceKB,
    CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB,
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,
    CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
FROM 
    sys.tables t
INNER JOIN 
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN 
    sys.schemas s ON t.schema_id = s.schema_id
WHERE 
    t.NAME NOT LIKE 'dt%' 
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255
GROUP BY 
    t.Name, s.Name, p.Rows
ORDER BY 
    TotalSpaceMB DESC;

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

Почему не сжимается лог транзакций (LDF) и как решить эту проблему

Часто файлы данных успешно сжимаются, но файл лога транзакций (.ldf) продолжает занимать огромное пространство и не реагирует на команды сжатия. Выясним, почему так происходит.

Главная причина кроется в установленной **модели восстановления** (Recovery Model) базы данных и отсутствии регулярных резервных копий журнала транзакций.

Если ваша база данных находится в модели восстановления FULL (Полная), то SQL Server записывает абсолютно все изменения в лог транзакций и хранит их там до тех пор, пока не будет выполнен бэкап этого самого лога. Обычный бэкап базы данных (Full Backup) лог не очищает! До тех пор, пока транзакции не заархивированы бэкапом лога, они считаются «активными», и SQL Server физически блокирует их усечение.

Чтобы точно определить, что именно мешает усечению и сжатию лога в данный момент, выполним диагностический запрос:

SELECT name, log_reuse_wait, log_reuse_wait_desc FROM sys.databases;

Проанализируем значение в столбце log_reuse_wait_desc для нашей базы данных:

LOG_BACKUP — лог ждет резервного копирования журнала транзакций. Вам необходимо сделать резервную копию лога (командой BACKUP LOG) и только после этого выполнять сжатие файла.
ACTIVE_TRANSACTION — в базе есть незавершенная транзакция (например, зависший сеанс 1С или долгая фоновая операция). Выявить виновника можно с помощью команды DBCC OPENTRAN.
NOTHING — преград для сжатия нет, можно приступать к процедуре.

Если для базы данных 1С (например, тестовой или некритичной рабочей) не требуется возможность восстановления на определенный момент времени, самым простым решением будет переключение модели восстановления в режим SIMPLE (Простая). В этом режиме лог автоматически очищается СУБД при достижении контрольных точек (Checkpoint).

Переведем базу в простую модель восстановления и сожмем лог транзакций до минимального размера следующими командами:


-- Переключаем модель восстановления в SIMPLE
ALTER DATABASE [Имя_Вашей_Базы] SET RECOVERY SIMPLE;
GO

-- Освобождаем неиспользуемое пространство лога транзакций
-- Здесь 'Имя_Файла_Лога' — это логическое имя файла LDF (не физическое имя на диске!)
DBCC SHRINKFILE (N'Имя_Файла_Лога' , 1024);
GO

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

Резюме и план действий

Для приведения размера базы данных на новом сервере SQL Server 2017 в оптимальное состояние рекомендуем придерживаться следующего алгоритма:

Определите фактический размер таблиц с помощью предоставленного T-SQL скрипта.
Если обнаружено большое количество пустых зарезервированных страниц, включите параметр MIXED_PAGE_ALLOCATION.
Проверьте модель восстановления базы данных. Для тестовых баз установите модель SIMPLE. Для рабочих баз в модели FULL настройте регулярный бэкап журнала транзакций (например, каждые 15–30 минут).
Выполните регламентное обслуживание (реиндексацию), после чего произведите сжатие файлов данных (mdf) и логов (ldf) до разумных технологических пределов.