Для крупных торговых сетей и дистрибьюторов объем базы данных «1С:Управление торговлей 11.4» (УТ) в 500 ГБ и более — явление неудивительное. Однако, когда темпы роста базы резко ускоряются и начинают составлять по 10% в месяц, это становится серьезным поводом для беспокойства. Стандартные операции вроде тестирования и исправления (ТИИ) перестают укладываться в технологические окна выходных дней, выгрузка в .dt не решает проблему, а скорость резервного копирования и восстановления начинает угрожать непрерывности бизнеса.
В этой статье мы подробно разберем, как локализовать проблему взрывного роста базы данных, почему именно регистры себестоимости и заказов раздувают систему, как оптимизировать MS SQL Server без остановки работы пользователей и как правильно выполнить «мягкую» свертку или перейти на новую базу.
Прежде чем предпринимать радикальные действия, нам необходимо точно понять, какие именно таблицы занимают больше всего места на диске и демонстрируют наибольший прирост. Проведение ТИИ на рабочей базе такого объема заблокирует работу компании, поэтому все диагностические операции мы будем выполнять непосредственно в СУБД или на резервной копии.
Проанализируем ситуацию на уровне MS SQL Server. Чтобы получить список самых тяжелых таблиц, мы можем запустить стандартный отчет в SQL Server Management Studio (SSMS). Для этого нажмите правой кнопкой мыши на имя вашей базы данных, выберите Reports -> Standard Reports -> Disk Usage by Top Tables. Если вы хотите автоматизировать этот процесс или получить более детальные данные, воспользуемся следующим T-SQL запросом:
SELECT
t.NAME AS TableName,
p.rows AS RowCounts,
SUM(a.total_pages) * 8 AS TotalSpaceKB,
SUM(a.used_pages) * 8 AS UsedSpaceKB,
(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
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
WHERE
t.is_ms_shipped = 0 AND i.object_id > 255
GROUP BY
t.Name, p.Rows
ORDER BY
TotalSpaceKB DESC;
Этот запрос наглядно покажет нам физические имена таблиц СУБД, количество записей в них и реальный объем на диске. Чтобы сопоставить технические имена таблиц СУБД (например, _AccumReg12345) с понятными именами объектов 1С, используйте встроенную функцию платформы ПолучитьСтруктуруХраненияБазыДанных().
Как показывает практика эксплуатации крупных баз «1С:Управление торговлей 11», лидерами по объему практически всегда становятся таблицы двух регистров накопления:
РегистрНакопления.СебестоимостьТоваров (таблицы движений и итогов).РегистрНакопления.ТоварыКПоступлению.Рассмотрим подробнее, почему именно эти объекты раздувают базу данных и как с этим бороться.
Этот регистр отвечает за оперативный контроль ожидаемых поставок товаров. Его лавинообразный рост указывает на системные проблемы в ведении учета и бизнес-процессах организации. Выделим основные причины:
Заказ поставщику, но фактически товар приходит не полностью (или не приходит вовсе), а сам заказ остается в статусе «К поступлению», движения по этому регистру остаются незакрытыми. Система продолжает хранить эти «ожидания» годами.Приобретение товаров и услуг и складской Приходный ордер на товары. Если между ними возникают количественные расхождения, пересортица или документы проводятся некорректной датой, в регистре остаются незакрытые остатки.Чем это грозит СУБД? В 1С регистры накопления состоят из двух основных физических таблиц: таблицы движений (оборотов) и таблицы итогов (остатков). Если остатки в регистре не закрываются в ноль, таблица итогов _AccumRegTotals начинает раздуваться. СУБД вынуждена переносить эти зависшие остатки из месяца в месяц при каждом расчете итогов. В результате таблица итогов начинает весить в разы больше, чем таблица самих движений.
Регистр себестоимости — это ядро финансового контура УТ 11. Его рост обусловлен сложными математическими алгоритмами расчета и ошибками пользователей:
Для баз данных объемом более 500 ГБ стандартных механизмов обслуживания средствами 1С (таких как выгрузка/загрузка .dt) становится недостаточно. Нам необходимо переложить заботу о производительности и хранении данных на СУБД.
Посмотрим на ключевые настройки обслуживания, которые должен выполнить системный администратор:
По умолчанию в MS SQL Server шаг роста базы данных может быть установлен в процентах (например, 10%). Для базы в 500 ГБ однократное автоувеличение потребует выделения сразу 50 ГБ дискового пространства. В этот момент СУБД полностью заблокирует транзакции, и пользователи увидят критическое зависание системы. Измените настройки файлов базы данных (MDF и LDF), установив фиксированный шаг роста (например, 512 МБ или 1024 МБ).
Если вы используете MS SQL Server редакции Enterprise (или Standard, начиная с версии 2016 SP1), обязательно задействуйте встроенное сжатие данных. Это позволит уменьшить физический размер базы на диске примерно на 40–60%, существенно разгрузив дисковую подсистему ввода-вывода (I/O). Сжатие можно включить без остановки пользователей с помощью SQL-команды:
ALTER INDEX ALL ON [_AccumReg12345] REBUILD WITH (DATA_COMPRESSION = PAGE);
Фрагментация индексов в активно растущей базе быстро достигает 80-90%. Настройте планы обслуживания СУБД. Рекомендуется использовать проверенные скрипты (например, планы обслуживания Олы Халленгрена или специализированные скрипты для 1С). Разделите обслуживание на ежедневное (быстрое обновление статистики и реорганизация индексов) и еженедельное (полная реиндексация наиболее фрагментированных таблиц во внерабочее время).
Когда размер базы мешает выполнять резервное копирование, стандартная свертка через встроенный помощник 1С превращается в кошмар: процесс зависает, падает из-за нехватки оперативной памяти или переполнения лога транзакций. В таких случаях опытные администраторы и программисты используют технологию «мягкой» свертки.
Разберем по шагам этот алгоритм:
Ввод начальных остатков. Данные для них собираются программно на основе остатков из архивной копии.
TRUNCATE TABLE _AccumReg12345;
Важно: Данную операцию должен выполнять только квалифицированный DBA, предварительно убедившись в наличии резервных копий и понимая структуру связей таблиц.Если конфигурация сильно кастомизирована, а в данных накоплено много логических ошибок, оптимальным вариантом будет начать учет в новой, чистой базе данных. Посмотрим, как сделать этот переход максимально безболезненным:
Даже самые мощные серверы и тонкие настройки SQL не спасут систему от распухания, если пользователи будут продолжать вводить некорректные данные. Необходимо внедрить следующие технические ограничения: