Как объединить файлы данных MS SQL Server в один после восстановления бэкапа и оптимизировать размер базы 1С

← На главную

При администрировании крупных баз данных 1С:Предприятия на платформе MS SQL Server специалисты часто сталкиваются с базами, файлы данных которых разделены на несколько частей (файлы вторичных данных с расширением .ndf). Такое разделение обычно выполняется для повышения производительности дисковой подсистемы за счет параллельной записи. Однако при переносе базы, создании тестовых копий или изменении архитектуры хранения возникает задача консолидации этих файлов в один стандартный файл .mdf.

В этой статье мы подробно разберем, почему MS SQL Server не позволяет объединить файлы данных напрямую в процессе восстановления из бэкапа, как правильно выполнить эту процедуру на восстановленной копии без потери данных, а также выясним природу «фантомного» размера таблиц итогов в 1С и способы борьбы с ним.

Часть 1. Ограничения MS SQL Server при восстановлении многофайлового бэкапа

Попытка восстановить резервную копию базы данных, состоящую из нескольких файлов данных, в базу с меньшим количеством файлов с помощью стандартной команды RESTORE DATABASE завершится ошибкой. СУБД жестко контролирует структуру хранения, описанную в метаданных бэкапа.

Каждый логический файл внутри резервной копии представляет собой независимый адресуемый поток страниц данных. При восстановлении SQL Server требует сопоставить каждому логическому файлу физический путь на диске с помощью конструкции MOVE ... TO. Не существует синтаксиса, который позволил бы перенаправить страницы из двух разных логических файлов резервной копии в один физический файл на целевом сервере. СУБД просто не сможет корректно распределить страницы данных и нарушит внутреннюю адресацию базы.

Поэтому единственным рабочим решением является двухэтапный процесс: восстановление базы в ее исходной многофайловой структуре с последующим переносом данных и удалением лишних файлов средствами Transact-SQL.

Часть 2. Пошаговый алгоритм объединения файлов данных

Рассмотрим ситуацию, когда наша исходная база имеет два файла данных (например, основной Avto_Data_01 и вторичный Avto_Data_02) и один файл журнала транзакций. Наша цель — развернуть копию этой базы на новом сервере и объединить все данные в одном файле.

Шаг 1. Восстановление базы данных в исходной структуре

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


RESTORE DATABASE [Avto_Copy]
FROM DISK = 'D:\Backups\Avto_Original.bak'
WITH 
  MOVE 'Avto_Data_01' TO 'D:\SQL_Data\Avto_Copy.mdf',
  MOVE 'Avto_Data_02' TO 'D:\SQL_Data\Avto_Copy_temp.ndf',
  MOVE 'Avto_Log' TO 'D:\SQL_Data\Avto_Copy_log.ldf',
  REPLACE, RECOVERY;
GO

После успешного выполнения команды база станет доступна в режиме ONLINE, но все еще будет занимать место в двух файлах на диске.

Шаг 2. Освобождение вторичного файла с помощью EMPTYFILE

Для того чтобы безопасно перенести все страницы данных из временного файла Avto_Copy_temp.ndf в основной файл Avto_Copy.mdf, мы воспользуемся специализированной командой СУБД — DBCC SHRINKFILE с параметром EMPTYFILE.

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

Запустим процесс переноса данных:


USE [Avto_Copy];
GO
DBCC SHRINKFILE (Avto_Data_02, EMPTYFILE);
GO

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

Шаг 3. Физическое удаление файла из базы данных

После того как команда EMPTYFILE успешно отработала, вторичный файл полностью опустел. Теперь мы можем безопасно исключить его из метаданных и физически удалить с жесткого диска. Это делается стандартной DDL-командой:


ALTER DATABASE [Avto_Copy] REMOVE FILE Avto_Data_02;
GO

Теперь наша база данных успешно переведена в однофайловый режим хранения.

Шаг 4. Устранение фрагментации индексов

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

Поэтому сразу после удаления файла критически важно провести полное перестроение индексов для всех таблиц базы данных. Выполним следующий скрипт:


USE [Avto_Copy];
GO
DECLARE @TableName NVARCHAR(255);
DECLARE @SQL NVARCHAR(MAX);

DECLARE TableCursor CURSOR FOR
SELECT '[' + SCHEMA_NAME(schema_id) + '].[' + name + ']'
FROM sys.tables;

OPEN TableCursor;
FETCH NEXT FROM TableCursor INTO @TableName;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @SQL = 'ALTER INDEX ALL ON ' + @TableName + ' REBUILD WITH (ONLINE = OFF);';
    EXEC sp_executesql @SQL;
    FETCH NEXT FROM TableCursor INTO @TableName;
END;

CLOSE TableCursor;
DEALLOCATE TableCursor;
GO

Часть 3. Почему пустые таблицы итогов в 1С занимают место на SQL?

Перейдем ко второму важному вопросу, затронутому на форуме: почему таблица остатков регистра накопления (например, _AccumRgT9393), из которой полностью удалены все движения, продолжает занимать гигабайты пространства на диске, и почему ее размер уменьшается неравномерно?

Проанализируем архитектурные особенности работы платформы 1С:Предприятие с итогами регистров:

Логика работы с нулевыми итогами. Операция физического удаления строк (DELETE) в реляционных СУБД является крайне ресурсоемкой и медленной, так как требует блокировок и активной работы с журналом транзакций. Чтобы оптимизировать производительность при записи движений, 1С не удаляет строки итогов при их закрытии в ноль. Вместо этого платформа выполняет быстрый UPDATE, записывая нулевые значения в поля ресурсов для существующей комбинации измерений. Таким образом, в таблице итогов накапливаются миллионы «нулевых» записей.
Пересчет итогов. Полное физическое удаление этих записей происходит только в момент запуска штатной процедуры пересчета итогов (через конфигуратор или программно). Платформа находит закрытые в ноль комбинации и вычищает их из базы данных. Именно поэтому после запуска пересчета размер таблицы на MS SQL Server снижается (в примере автора — с 9 до 6 Гб).
Особенности выделения страниц в SQL Server (Deferred Drop). Почему же после очистки таблица все еще показывала большой объем, а окончательно обнулилась только после ночного бэкапа? В современных версиях SQL Server используется механизм отложенного удаления страниц (Deferred Drop). При удалении больших массивов данных СУБД освобождает страницы не мгновенно, чтобы не перегружать дисковую подсистему и процессор. Метаданные в системных таблицах (таких как sys.allocation_units) обновляются транзакционно с задержкой. Резервное копирование инициирует принудительную контрольную точку (CHECKPOINT) и фиксирует все отложенные операции на диске, после чего отчеты начинают показывать реальный нулевой размер таблиц.

Часть 4. Безопасная очистка таблиц итогов через T-SQL

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

Сначала определим точное соответствие метаданных 1С и физических таблиц в SQL Server. Для этого выполним в конфигурации 1С код:


СтруктураБД = ПолучитьСтруктуруХраненияБазыДанных();
Для Каждого Таблица Из СтруктураБД Цикл
    Если Найти(Таблица.ИмяТаблицы, "AccumRgT") > 0 Тогда
        Сообщить("Имя в SQL: " + Таблица.ИмяТаблицы + " -> Метаданные: " + Таблица.Метаданные);
    КонецЕсли;
КонецЦикла;

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


USE [Avto_Copy];
GO
-- Быстрое и полное удаление данных из таблицы итогов регистра
TRUNCATE TABLE _AccumRgT9181;
GO

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