Как правильно уменьшить размер SQL-базы 1С, раздутой из-за хранения фотографий?

← На главную

Приветствуем вас! Сегодня мы разберем одну из самых частых и болезненных проблем, с которой сталкиваются администраторы крупных баз данных 1С, — критическое раздувание таблиц из-за накопления медиафайлов. Когда пользователи на протяжении многих лет бесконтрольно загружают фотографии, сканы документов и другие тяжелые данные непосредственно в информационную базу (в поля типа ХранилищеЗначения), размер базы начинает расти по экспоненте. Это неизбежно приводит к деградации производительности СУБД, колоссальным трудностям при резервном копировании и дефициту дорогостоящего дискового пространства в облаке.

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

Выясняем причину и масштаб бедствия: аудит данных

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

Рассмотрим стандартный подход к анализу таблиц в СУБД MS SQL Server. С помощью стандартного отчета Disk Usage by Top Tables в SQL Server Management Studio (SSMS) мы можем мгновенно увидеть, какая именно таблица занимает львиную долю дискового пространства. Как правило, в базах 1С это таблицы, соответствующие справочникам присоединенных файлов (например, _InfoRg или _Reference), содержащие колонки с типом данных varbinary(max) или image.

Если мы работаем непосредственно в 1С, полезно написать простой запрос для анализа количества файлов и их среднего размера по периодам. Очень часто здесь применимо правило Парето (80/20): удалив или оптимизировав всего 20% самых свежих или, наоборот, самых старых и неиспользуемых файлов, мы сможем освободить до 80% дискового пространства. Проанализируем дату создания файлов и их объем перед тем, как планировать глобальное сжатие.

Решение 1: Перенос файлов в тома хранения (Рекомендованный штатный метод)

Если ваша конфигурация построена на базе БСП (Библиотеки Стандартных Подсистем), у вас уже есть мощнейший инструмент для решения этой проблемы, который не требует написания сложного кода. Это механизм хранения файлов в томах на диске.

Вместо того чтобы хранить терабайты картинок внутри транзакционной базы данных СУБД, мы можем вынести их в сетевой каталог или на быстрый локальный диск сервера приложений, оставив в самой базе только легкие текстовые ссылки на пути к файлам. Разберем этот процесс по шагам:

Создание тома хранения файлов: Перейдем в раздел администрирования вашей программы 1С, найдем настройки работы с файлами и создадим новый «Том хранения файлов». Укажем путь к сетевой папке или локальному диску (например, \\server\1c_files\). Настроим права доступа для учетной записи, под которой запущена служба сервера 1С:Предприятие (она должна иметь полные права на чтение и запись в этот каталог).
Настройка правил переноса: Включим признак «Хранить файлы в томах на диске». С этого момента все новые файлы будут автоматически сохраняться в указанную папку, минуя SQL-таблицы.
Запуск регламентного задания переноса: Для переноса ранее накопленных файлов запустим стандартное регламентное задание ПереносФайловВТома. Данное задание будет порциями извлекать бинарные данные из базы данных, записывать их на диск и очищать соответствующее поле ХранилищеЗначения в самой СУБД.

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

Решение 2: Программное сжатие изображений и создание миниатюр средствами 1С

Если перенос в тома по каким-то причинам не подходит, и вам обязательно нужно оставить файлы внутри базы, но в сильно уменьшенном виде (например, заменить оригиналы на миниатюры 32х32 пикселя, чтобы не ломать логику работы документов), нам придется написать собственную обработку. Основная сложность здесь заключается в том, что последовательный перебор 500 тысяч записей в обычном цикле может растянуться на недели.

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

Рассмотрим пример кода, который выполняет пакетную обработку данных порциями с фиксацией транзакций. Это критически важно для предотвращения переполнения оперативной памяти сервера приложений и транзакционного лога СУБД:


ВыборкаДетальныеЗаписи = Запрос.Выполнить().Выбрать();
ГраницаПакета = 1000;
Счетчик = 0;

НачатьТранзакцию();
Пока ВыборкаДетальныеЗаписи.Следующий() Цикл
    Счетчик = Счетчик + 1;
    
    // Получаем объект файла
    ОбъектФайла = ВыборкаДетальныеЗаписи.Ссылка.ПолучитьОбъект();
    
    // Создаем пустую картинку-заглушку минимального размера
    КартинкаЗаглушка = Новый Картинка; 
    ДвоичныеДанныеЗаглушки = КартинкаЗаглушка.ПолучитьДвоичныеДанные();
    
    // Перезаписываем ХранилищеЗначения минимальным размером
    ОбъектФайла.ХранилищеФайла = Новый ХранилищеЗначения(ДвоичныеДанныеЗаглушки, Новый СжатиеДанных(9));
    ОбъектФайла.Записать();
    
    Если Счетчик % ГраницаПакета = 0 Тогда
        ЗафиксироватьТранзакцию();
        НачатьТранзакцию();
    КонецЕсли;
КонецЦикла;

Если ТранзакцияАктивна() Тогда
    ЗафиксироватьТранзакцию();
КонецЕсли;

Для того чтобы ускорить этот процесс в несколько раз, мы должны запустить обработку параллельно. Разделим общий массив записей (например, по диапазонам уникальных идентификаторов GUID или по датам создания документов) на несколько независимых диапазонов и запустим для каждого диапазона свое фоновое задание через механизм ФоновыеЗадания.Выполнить(). Использование 8–12 параллельных потоков позволит сократить время обработки с нескольких дней до считанных часов.

Решение 3: Прямое удаление LOB-данных на уровне СУБД (Экстремальный метод)

Если база данных настолько раздута, что штатные методы 1С зависают или приводят к аварийной остановке сервера из-за нехватки ресурсов, администраторы прибегают к прямым SQL-запросам. Помните, что это нарушает лицензионное соглашение 1С, поэтому предварительно обязательно сделайте полную резервную копию базы данных!

Сначала определим имя таблицы базы данных 1С и нужного поля. Для этого выполним в 1С метод ПолучитьСтруктуруХраненияБазыДанных(). Например, мы выяснили, что наши файлы хранятся в таблице _Reference125 в колонке _Fld1252_Type (хранилище значения). На стороне SQL Server мы можем выполнить прямой апдейт, заменяющий тяжелые бинарные данные на NULL или пустое значение 0x:


-- Пример SQL-запроса для очистки бинарного поля в таблице
UPDATE _Reference125
SET _Fld1252_Type = 0x
WHERE _IDRRef IN (
    SELECT _IDRRef 
    FROM _Reference125 
    WHERE _Fld1253_Date < '2020-01-01'
)

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

Важнейший этап: Физическое освобождение места на диске СУБД

Внимание! Самая частая ошибка начинающих администраторов: они удаляют файлы в 1С, видят, что в программе картинок больше нет, но размер файлов базы данных на диске (файлы .mdf в MS SQL или каталоги PostgreSQL) не уменьшился ни на один мегабайт. Почему так происходит?

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

Инструкция для MS SQL Server:

Перевод в Simple recovery model: Перед массовым удалением или очисткой обязательно переведите базу данных в модель восстановления Simple (Простая) в свойствах базы. Если этого не сделать, ваш транзакционный лог (файл .ldf) мгновенно раздуется до сотен гигабайт и заблокирует всю работу сервера.
Пошаговое сжатие файлов данных (Shrink): Никогда не запускайте полное автоматическое сжатие всей базы через графический интерфейс SSMS на больших объемах — это может намертво подвесить сервер и вызвать жуткую фрагментацию индексов. Используйте пошаговое сжатие файла данных небольшими порциями (например, по 10 ГБ) с помощью T-SQL команды: DBCC SHRINKFILE (N'Имя_Файла_Данных', Целевой_Размер_В_МБ)
Перестроение индексов (Rebuild): Данные типа LOB (Large Object) часто требуют физического перестроения индексов таблиц для освобождения страниц. Выполните команду ALTER INDEX ALL ON Имя_Таблицы REBUILD для тех таблиц, из которых удалялись фотографии.

Инструкция для PostgreSQL:

При обновлении или удалении строк в PostgreSQL старые версии данных остаются на диске в виде так называемых «мертвых строк» (Dead Tuples). Обычная очистка VACUUM не вернет место операционной системе.
Для полного освобождения места необходимо выполнить монопольную операцию VACUUM FULL Имя_Таблицы. Обратите внимание, что эта команда полностью блокирует таблицу для чтения и записи, поэтому ее нужно выполнять строго во время технологического окна, когда пользователи отключены от базы.
В качестве альтернативы без блокировки можно использовать стороннюю утилиту pg_repack, которая пересоздает таблицу и индексы в фоновом режиме, минимизируя время простоя и оперативно освобождая дисковое пространство.

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