Каждый администратор больших баз данных 1С рано или поздно сталкивается с критическим дефицитом свободного места на дисках сервера СУБД. В нашей практике нередки ситуации, когда база данных «Управление производственным предприятием» (УПП) или «ERP Управление предприятием» разрастается до внушительных 1.7 ТБ на дисковом массиве емкостью всего 2 ТБ — решить проблему поможет перенос хранения файлов из базы на диски. Попытка решить проблему «в лоб» через конфигуратор — выгрузку в файл .dt и последующую загрузку в чистую базу — часто дает ошеломляющий результат: объем базы данных сокращается до 700 ГБ. Однако цена такого успеха слишком высока. В реальных условиях крупного предприятия выгрузка может занять около 15 часов, а обратная загрузка — более 30 часов. Итого: почти двое суток полного простоя системы, что абсолютно неприемлемо для бизнеса.
В этой статье мы подробно разберем, почему база данных так сильно уменьшается после цикла выгрузки-загрузки через файл .dt, и рассмотрим, как получить аналогичный или даже превосходящий результат исключительно встроенными средствами СУБД MS SQL Server — быстро, безопасно и практически без прерывания работы пользователей. Для решения этой задачи есть инструмент для свертки и сжатия баз 1С.
Давайте проанализируем физические процессы, которые происходят на уровне хранения данных СУБД — оценить их поможет анализ структуры и объема таблиц СУБД. Уменьшение размера базы данных с 1.7 ТБ до 700 ГБ после восстановления из резервной копии 1С происходит по нескольким ключевым причинам:
.dt платформа 1С выгружает только «чистые» логические данные. При последующей обратной загрузке MS SQL создает таблицы заново и строит индексы с нуля, укладывая страницы данных максимально плотно друг к другу..dt платформа полностью пересчитывает итоги, отбрасывая весь накопленный логический мусор..mdf), а файл журнала транзакций (.ldf), который некорректно обслуживается. Новая база создается с минимальным размером лог-файла.Понимая эти механизмы, мы можем воспроизвести все эти этапы непосредственно внутри MS SQL Server без долгой промежуточной конвертации данных в формат .dt.
Первое, что мы должны сделать для устранения пустот внутри файлов данных СУБД — выполнить полное перестроение (Rebuild) всех индексов. Это действие физически пересоздает индексы таблиц, укладывая данные на страницах максимально последовательно.
В отличие от реорганизации (Defragment/Reorganize), операция REBUILD полностью перестраивает структуру индекса. Мы можем выполнить эту процедуру с помощью простого скрипта T-SQL прямо в среде SQL Server Management Studio (SSMS):
-- Перестроение всех индексов во всех таблицах текущей базы данных
DECLARE @TableName NVARCHAR(257);
DECLARE @SQL NVARCHAR(MAX);
DECLARE TableCursor CURSOR FOR
SELECT OBJECT_SCHEMA_NAME(object_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 (FILLFACTOR = 90, ONLINE = ON);';
BEGIN TRY
EXEC sp_executesql @SQL;
END TRY
BEGIN CATCH
-- Если ONLINE-перестроение не поддерживается для таблицы (например, содержит LOB-данные в старых версиях SQL)
SET @SQL = 'ALTER INDEX ALL ON ' + @TableName + ' REBUILD WITH (FILLFACTOR = 90);';
EXEC sp_executesql @SQL;
END CATCH;
FETCH NEXT FROM TableCursor INTO @TableName;
END;
CLOSE TableCursor;
DEALLOCATE TableCursor;
Параметр FILLFACTOR = 90 указывает SQL Server оставлять 10% свободного места на страницах индекса под будущие вставки данных, чтобы избежать немедленной фрагментации после запуска пользователей в базу. Если вы используете MS SQL Server редакции Enterprise, опция ONLINE = ON позволит выполнять эту процедуру в фоновом режиме без блокировки работы пользователей 1С.
Начиная с версии MS SQL Server 2016 Service Pack 1, функционал сжатия таблиц и индексов доступен во всех редакциях СУБД, включая Standard. Для тяжелых баз 1С (таких как УПП или ERP) использование страничного сжатия (PAGE) дает колоссальный эффект.
При включении PAGE-сжатия SQL Server анализирует повторяющиеся цепочки байтов и значений в пределах одной физической страницы данных и заменяет их короткими внутренними ссылками. Это позволяет сжать таблицы базы данных 1С в 2–3 раза!
Преимущества сжатия страниц:
Для того чтобы применить сжатие ко всем таблицам и индексам базы данных, выполним следующий скрипт:
-- Скрипт для автоматического включения PAGE-сжатия на всех таблицах и индексах
DECLARE @SchemaName NVARCHAR(128);
DECLARE @TableName NVARCHAR(128);
DECLARE @SQL NVARCHAR(MAX);
DECLARE TableCompressCursor CURSOR FOR
SELECT s.name , t.name
FROM sys.tables t
JOIN sys.schemas s ON t.schema_id = s.schema_id;
OPEN TableCompressCursor;
FETCH NEXT FROM TableCompressCursor INTO @SchemaName, @TableName;
WHILE @@FETCH_STATUS = 0
BEGIN
-- Сжатие самой таблицы (кучи или кластеризованного индекса)
SET @SQL = 'ALTER TABLE [' + @SchemaName + '].[' + @TableName + '] REBUILD WITH (DATA_COMPRESSION = PAGE);';
BEGIN TRY
EXEC sp_executesql @SQL;
END TRY
BEGIN CATCH
PRINT 'Ошибка при сжатии таблицы: ' + @TableName;
END CATCH;
-- Сжатие всех некластеризованных индексов таблицы
SET @SQL = 'ALTER INDEX ALL ON [' + @SchemaName + '].[' + @TableName + '] REBUILD WITH (DATA_COMPRESSION = PAGE);';
BEGIN TRY
EXEC sp_executesql @SQL;
END TRY
BEGIN CATCH
PRINT 'Ошибка при сжатии индексов таблицы: ' + @TableName;
END CATCH;
FETCH NEXT FROM TableCompressCursor INTO @SchemaName, @TableName;
END;
CLOSE TableCompressCursor;
DEALLOCATE TableCompressCursor;
После того как мы перестроили индексы или применили сжатие страниц (PAGE), внутри физического файла базы данных (.mdf) освободится огромное количество места. Но операционная система этого не увидит: файл базы данных на диске останется прежнего размера (например, 1.7 ТБ), просто внутри него появится около 1 ТБ свободного пространства.
Чтобы физически вернуть неиспользуемое место операционной системе, необходимо выполнить сжатие файлов базы данных (операцию Shrink).
Внимание: Никогда не используйте команду DBCC SHRINKDATABASE или автоматическое сжатие базы данных (Auto Shrink) в настройках SQL. Эти инструменты перемещают страницы данных в конец файла в случайном порядке, что вызывает мгновенную фрагментацию индексов до 99% и катастрофически снижает производительность 1С.
Рассмотрим правильный и безопасный алгоритм сжатия файлов:
SELECT name, physical_name, type_desc
FROM sys.master_files
WHERE database_id = DB_ID();
DBCC SHRINKFILE, указав желаемый целевой размер в мегабайтах (например, сожмем до 800 ГБ, что равно 819 200 МБ):
-- Сжатие файла данных до целевого объема 800 ГБ
DBCC SHRINKFILE (Имя_Файла_Данных, 819200);
Если в процессе анализа выяснилось, что львиную долю дискового пространства занимает журнал транзакций (например, файл .ldf раздулся до 500 ГБ при простой модели восстановления SIMPLE), его можно сжать отдельно и безопасно. В простой модели восстановления журнал очищается автоматически после фиксации транзакций, но физический файл сам по себе не уменьшается.
Чтобы быстро вернуть место из журнала транзакций, выполните следующую команду:
-- Сжатие файла журнала транзакций базы данных до 2 ГБ (2048 МБ)
DECLARE @LogFileName NVARCHAR(128);
SELECT @LogFileName = name FROM sys.master_files WHERE database_id = DB_ID() AND type = 1;
EXEC('DBCC SHRINKFILE ([' + @LogFileName + '], 2048);');
Для того чтобы закрепить результат и исключить накопление «мусора» в таблицах итогов на уровне метаданных 1С, рекомендуется регулярно проводить встроенное обслуживание базы. Для этого запустим конфигуратор 1С и откроем меню Администрирование — Тестирование и исправление....
В открывшемся окне выберем следующие опции:
Эту операцию желательно выполнять в периоды минимальной активности пользователей (например, в технологическое окно на выходных).
Проанализировав все способы обслуживания, мы видим, что тратить 45 часов на опасную процедуру выгрузки и загрузки .dt файлов больше нет никакой необходимости. Используя механизмы сжатия страниц PAGE Compression и регулярное перестроение индексов средствами MS SQL Server, вы сможете поддерживать базу данных объемом 1.7 ТБ в максимально компактном и производительном состоянии (в пределах 700-800 ГБ), не прерывая работу пользователей и бизнес-процессы предприятия.