Очистка таблицы оборотов регистра накопления через TRUNCATE на уровне SQL: почему это крайне опасно и как правильно уменьшить размер базы данных 1С?

При администрировании крупных баз данных на платформе 1С:Предприятие 8, работающих под управлением СУБД MS SQL Server, технические специалисты часто сталкиваются с проблемой нехватки дискового пространства или снижения производительности регламентных операций. Базы данных, эксплуатирующиеся более 10 лет, могут разрастаться до терабайтных масштабов. При этом значительную часть объема (иногда до 30-50%) занимают таблицы итогов и оборотов регистров накопления, таких как Продажи, Закупки или Взаиморасчеты.

В попытках быстро решить проблему «здесь и сейчас» у администраторов баз данных возникает соблазн прибегнуть к неординарным методам — например, выполнить команду TRUNCATE TABLE для физической таблицы оборотов на уровне SQL. На первый взгляд, этот метод кажется рабочим: база существенно «худеет», документы проводятся, а простые отчеты продолжают показывать верные данные. Однако за этим внешним благополучием скрывается опаснейшая архитектурная ловушка. В этой статье мы подробно разберем, почему «наглая» очистка таблиц оборотов приводит к скрытому искажению данных в отчетах, каков внутренний механизм работы платформы 1С с СУБД и какими легальными и безопасными способами можно реально уменьшить объем базы данных.

Внутренняя архитектура регистров накопления: физические таблицы в SQL

Прежде чем анализировать последствия хирургического вмешательства в СУБД, разберем, как платформа 1С проектирует регистр накопления на физическом уровне базы данных. Для каждого оборотного регистра накопления в MS SQL создаются как минимум две основные таблицы:

1. Таблица движений (имеет имя вида _AccumRgXXXX) — содержит детальные записи (первичные документы, регистраторы, измерения, ресурсы и реквизиты). Это первоисточник всех данных, которые когда-либо были записаны в регистр.
2. Таблица оборотов (имеет имя вида _AccumRgTnXXXX) — хранит предварительно рассчитанные платформой итоги в разрезе измерений с периодичностью в один календарный месяц.

Платформа 1С использует таблицу оборотов _AccumRgTnXXXX как кэш-таблицу для ускорения выборки данных. Когда пользователь строит отчет за большой период, СУБД не приходится сканировать миллионы строк таблицы движений — она берет уже готовые агрегированные данные за нужные месяцы из таблицы оборотов.

Иллюзия успеха: почему у автора эксперимента «всё работало»?

Рассмотрим ситуацию, описанную автором темы. После выполнения команды TRUNCATE на таблице оборотов регистра продажи, запрос за период с 01.01.2015 по 15.01.2015 выдал абсолютно верные цифры. Почему так произошло, если таблица оборотов была пуста?

Дело в оптимизаторе запросов платформы 1С. Когда мы обращаемся к виртуальной таблице РегистрНакопления.Продажи.Обороты, платформа генерирует SQL-запрос, структура которого жестко зависит от параметров периода:

• Запрос за произвольный период (не кратный месяцу): Если границы запроса не совпадают с началом и концом календарного месяца (например, с 1 по 15 января), платформа понимает, что в таблице месячных оборотов нет готового предрассчитанного значения для этого интервала. В этом случае 1С полностью игнорирует таблицу оборотов _AccumRgTnXXXX и обращается напрямую к таблице движений _AccumRgXXXX, рассчитывая сумму «на лету». Именно поэтому автор увидел правильные данные за половину января — СУБД просто прочитала первичные документы.
• Запрос за полный календарный месяц: Если бы автор сформировал отчет ровно с 01.01.2015 по 31.01.2015 23:59:59 (без указания периодичности), платформа применила бы оптимизацию и обратилась напрямую к очищенной таблице оборотов _AccumRgTnXXXX. В результате отчет выдал бы пустую строку (нули), несмотря на то, что физические документы в базе существуют и проведены!

Проанализируем пример типичного SQL-запроса, который генерирует платформа при обращении к виртуальной таблице оборотов за полный месяц:

SELECT
    T1._Fld4328_Turnover,
    T1._Fld4329_Turnover
FROM (
    SELECT
        SUM(T2._Fld4328) AS _Fld4328_Turnover,
        SUM(T2._Fld4329) AS _Fld4329_Turnover
    FROM dbo._AccumRgTn4333 T2 WITH(NOLOCK)
    WHERE T2._Period >= @P1 AND T2._Period < @P2
) T1

Как мы видим, обращение идет к таблице _AccumRgTn4333. Если она подверглась очистке через TRUNCATE, результат выполнения данного запроса всегда будет нулевым. Таким образом, пользователи начнут получать искаженные отчёты при попытке проанализировать продажи за прошлые года.

Критические риски прямой очистки таблиц итогов через SQL

Помимо очевидной проблемы с получением неверных данных в отчетах, прямое удаление записей из базы данных в обход объектной техники 1С несет в себе колоссальные технические риски:

1. Ошибки при проведении документов и транзакционные сбои. Платформа 1С «уверена», что итоги по регистру рассчитаны (например, по текущий месяц). При проведении или отмене проведения старых документов платформа будет пытаться обновить или скорректировать записи в таблице _AccumRgTnXXXX. Если запись, которую платформа ожидает найти для модификации, отсутствует, это может привести к непредсказуемому поведению СУБД, ошибкам блокировок или нарушению ограничений уникальности индексов при попытке платформы вставить дублирующий ключ.
2. Механизм разделителя итогов (_Splitter). Для обеспечения высокой параллельности при записи платформа использует специальное поле разделителя итогов. Очистка таблиц нарушает целостность этих разделителей, что неизбежно приведет к взаимным блокировкам (Deadlocks) при одновременной работе пользователей.
3. Крах при первом же пересчете итогов. Любая попытка выполнить штатную процедуру «Пересчет итогов» или запуск «Тестирования и исправления» приведет к тому, что платформа начнет последовательно считывать всю таблицу движений _AccumRgXXXX и заново записывать агрегаты в пустую _AccumRgTnXXXX. База данных вернется к своему прежнему объему. Более того, в процессе этой гигантской транзакции файл лога транзакций SQL (LDF) раздуется до невероятных размеров, что может полностью заблокировать работу сервера из-за нехватки свободного места.

Безопасные и штатные альтернативы оптимизации размера базы

Если перед вами стоит реальная задача сократить объем базы данных на сервере MS SQL без потери исторической аналитики за 10 лет, воспользуйтесь проверенными и безопасными методами.

Метод 1. Управление периодом расчета итогов в 1С

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

Выясним, как ведет себя платформа при изменении периода расчета итогов:

• Если мы устанавливаем период расчета итогов только на актуальный интервал (например, последние 1-2 года), платформа 1С самостоятельно, транзакционно и абсолютно безопасно удаляет все строки за старые периоды из физических таблиц оборотов _AccumRgTnXXXX и остатков _AccumRgTXXXX.
• При этом первичные движения в таблице _AccumRgXXXX остаются нетронутыми. Пользователи по-прежнему смогут формировать отчеты за любые периоды 10-летней давности. Платформа просто будет рассчитывать эти отчеты динамически («на лету») по таблице движений. Да, скорость формирования старых отчетов может немного снизиться, но размер базы сократится на гигантские объемы без нарушения логики работы конфигурации.

Метод 2. Сжатие данных средствами MS SQL Server (Data Compression)

Если дисковая подсистема сервера перегружена, а данные прошлых лет необходимы в быстром доступе, отличным решением является использование встроенных механизмов сжатия MS SQL Server (доступно в редакциях Enterprise, а начиная с SQL Server 2016 SP1 — и в Standard).

Сжатие страниц (PAGE) или строк (ROW) выполняется прозрачно для платформы 1С и позволяет сжать исторические таблицы на 50–80%. Проанализируем, как применить сжатие к таблицам регистров накопления:

1. Определите самые тяжелые таблицы базы данных с помощью стандартного отчета MS SQL Disk Usage by Top Tables.
2. Выполните сжатие данных на уровне страниц для выбранной таблицы движений или итогов. Для этого можно воспользоваться SQL-скриптом:

ALTER TABLE dbo._AccumRg4330 REBUILD WITH (DATA_COMPRESSION = PAGE);
ALTER TABLE dbo._AccumRgTn4333 REBUILD WITH (DATA_COMPRESSION = PAGE);

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

Метод 3. Свертка разделителей итогов через Тестирование и Исправление

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

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

Заключение

Выполнение команды TRUNCATE TABLE для таблиц оборотов или остатков 1С на уровне СУБД — это классический пример «вредного совета», который создает ложную иллюзию оптимизации. Такое грубое вмешательство неизбежно приводит к искажению финансовой отчетности за прошлые периоды, ошибкам транзакций и блокировкам при проведении документов.

Используйте только штатные и проверенные механизмы: управляйте границей расчета итогов внутри самой платформы 1С, применяйте безопасное SQL-сжатие таблиц (PAGE/ROW) и своевременно проводите обслуживание индексов и оптимизацию таблиц итогов. Это позволит содержать базу данных в идеальном порядке без рисков для бизнеса.