При работе с базами данных 1С на платформе Microsoft SQL Server, мы часто сталкиваемся с проблемой их стремительного роста, что приводит к нехватке дискового пространства и снижению производительности. В таких ситуациях нередко возникает вопрос о применении процедуры сжатия базы данных. Давайте подробно разберем, насколько это решение эффективно, какие риски оно несет и какие существуют более правильные альтернативы.
Первое, что мы должны уяснить, это то, что корпорация Microsoft и большинство экспертов по SQL Server настоятельно не рекомендуют регулярно выполнять операции сжатия файлов данных (DBCC SHRINKDATABASE или DBCC SHRINKFILE). Почему же такая категоричность? Рассмотрим основные причины:
Мы должны понимать, что сжатие файлов данных – это операция, которая в основном направлена на освобождение места на диске, но делает это ценой производительности и стабильности системы. Это как пытаться заставить воду из переполненного бассейна поместиться в меньшую емкость, вместо того чтобы выяснить, почему бассейн переполняется, и устранить источник поступления воды.
Существуют очень специфические и редкие условия, когда сжатие файлов данных все же может быть рассмотрено:
tempdb. Если tempdb значительно разрослась из-за каких-либо аномалий. Однако это сложная операция, часто требующая перезапуска SQL Server, и ее следует проводить с крайней осторожностью.Сжатие файла журнала транзакций (LDF) отличается от сжатия файлов данных (MDF) и в некоторых случаях может быть необходимым, особенно если журнал значительно разросся. Давайте разберем причины его роста и методы управления:
Full Recovery Model) журнал транзакций продолжает расти до тех пор, пока не будет выполнено его резервное копирование. Если резервное копирование журнала не настроено или выполняется нерегулярно, LDF-файл будет постоянно увеличиваться. Он хранит все транзакции для возможности восстановления базы данных до любой точки во времени.Simple Recovery Model) журнал автоматически усекается при каждом контрольном событии (checkpoint), что помогает поддерживать его размер под контролем, поскольку сохраняется только минимально необходимая для восстановления информация.Full Recovery Model):
Для уменьшения LDF-файла сначала необходимо сделать резервную копию журнала транзакций. Эта операция помечает неактивные записи журнала как доступные для усечения. После этого можно использовать команду DBCC SHRINKFILE для его сжатия.
-- 1. Сделайте резервную копию журнала транзакций (замените 'YourDatabaseName' и путь к файлу)
BACKUP LOG [YourDatabaseName] TO DISK = N'C:\Backup\YourDatabaseName_Log.trn' WITH NOFORMAT, NOINIT, NAME = N'YourDatabaseName-Log Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10;
GO
-- 2. Проверьте логическое имя файла журнала (обычно YourDatabaseName_log)
SELECT name, physical_name FROM sys.database_files WHERE type_desc = 'LOG';
GO
-- 3. Сожмите файл журнала транзакций до желаемого размера (например, 1024 МБ)
DBCC SHRINKFILE (N'YourDatabaseName_log', 1024);
GO
Если LDF-файл разросся критически и резервное копирование не помогает его усечь (возможно, из-за длительной транзакции или других проблем), можно временно переключить базу данных в простую модель восстановления (Simple Recovery Model), выполнить сжатие, а затем вернуть полную модель. Важно: этот подход прерывает цепочку резервных копий журнала транзакций, поэтому сразу после возвращения в Full Recovery Model необходимо выполнить полное резервное копирование базы данных.
-- 1. Переключите базу данных в Simple Recovery Model
ALTER DATABASE [YourDatabaseName] SET RECOVERY SIMPLE;
GO
-- 2. Сожмите файл журнала транзакций (замените 'YourDatabaseName_log' и желаемый размер)
DBCC SHRINKFILE (N'YourDatabaseName_log', 1024);
GO
-- 3. Верните базу данных в Full Recovery Model
ALTER DATABASE [YourDatabaseName] SET RECOVERY FULL;
GO
-- 4. СРАЗУ ЖЕ выполните полное резервное копирование базы данных
BACKUP DATABASE [YourDatabaseName] TO DISK = N'C:\Backup\YourDatabaseName_Full.bak' WITH NOFORMAT, NOINIT, NAME = N'YourDatabaseName-Full Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10;
GO
Вместо того чтобы бороться с последствиями разрастания базы с помощью «шринка», мы рекомендуем сосредоточиться на управлении данными и оптимизации их хранения. Давайте рассмотрим несколько действенных подходов:
Это самый эффективный и рекомендуемый способ уменьшить объем данных в базе. В контексте 1С это может быть очистка различных регистров сведений, которые со временем могут сильно разрастаться, или удаление давно неактуальных документов и справочников. Мы должны анализировать, какие данные нам действительно нужны для оперативной работы, а какие можно архивировать или удалить.
Прежде всего, нам нужно выявить, какие таблицы в базе данных занимают наибольший объем. Для этого мы можем использовать различные инструменты SQL Server Management Studio (SSMS) или специализированные обработки в 1С (например, "Структура базы данных"). Часто "пухнущими" оказываются регистры сведений, которые хранят историю изменений, логи синхронизаций, журналы ошибок и прочие служебные данные.
Как показывает практический опыт коллег, в базах 1С:Битрикс, например, это могут быть регистры сведений, связанные с историей синхронизаций или журналом ошибок. После выявления таких "тяжеловесов", мы можем разработать механизм их очистки. Если объем данных очень большой, стандартные механизмы удаления в 1С (например, СоздатьНаборЗаписей().Записать()) могут не сработать из-за нехватки памяти или таймаутов. В таких случаях нам приходится создавать специализированные обработки, которые удаляют данные порциями, либо использовать прямые SQL-запросы через внешние инструменты с крайней осторожностью и только после полного резервного копирования.
Пример концепции обработки для очистки регистра сведений в 1С (псевдокод):
// Предположим, что "РегистрСведенийЖурналОшибок" - это наш разрастающийся регистр
// Определяем дату, до которой хотим удалить данные (например, все, что старше одной недели)
ДатаОчистки = ДобавитьКДате(ТекущаяДата(), "НЕДЕЛЯ", -1);
// Создаем выборку для удаления порциями
Запрос = Новый Запрос;
Запрос.Текст =
"ВЫБРАТЬ ПЕРВЫЕ 10000
| РегистрСведенийЖурналОшибок.Период КАК Период
|ИЗ
| РегистрСведенийЖурналОшибок КАК РегистрСведенийЖурналОшибок
|ГДЕ
| РегистрСведенийЖурналОшибок.Период < &ДатаОчистки";
Запрос.УстановитьПараметр("ДатаОчистки", ДатаОчистки);
Пока Истина Цикл
Выборка = Запрос.Выполнить().Выбрать();
Если Не Выборка.Следующий() Тогда
Прервать;
КонецЕсли;
// Начинаем транзакцию для безопасного удаления порции
НачатьТранзакцию();
Попытка
НаборЗаписей = РегистрыСведений.РегистрСведенийЖурналОшибок.СоздатьНаборЗаписей();
НаборЗаписей.Отбор.Период.Установить(Выборка.Период, РежимИспользованияПериода.НачалоДня); // Уточнить отбор
НаборЗаписей.Прочитать();
НаборЗаписей.Очистить();
НаборЗаписей.Записать(); // Записываем пустой набор, что равносильно удалению
ЗафиксироватьТранзакцию();
Сообщить("Удалена порция записей до " + Формат(Выборка.Период, "ДФ=dd.MM.yyyy"));
Исключение
ОтменитьТранзакцию();
Сообщить("Ошибка при удалении порции: " + ОписаниеОшибки());
Прервать;
КонецПопытки;
КонецЦикла;
Сообщить("Очистка регистра завершена.");
После очистки данных в 1С, мы можем обнаружить, что файл базы данных на SQL Server уменьшился, но чаще всего он остается того же размера, просто содержит больше свободного места внутри. В таких случаях, если это свободное место критично для дискового пространства и мы уверены, что оно не понадобится в ближайшее время, единожды можно рассмотреть сжатие файла данных с последующим перестроением индексов.
Также не забываем про удаление помеченных на удаление объектов в 1С, так как они продолжают занимать место до физического удаления.
SQL Server предоставляет функциональность сжатия данных на уровне строк (Row Compression) и страниц (Page Compression). Это не то же самое, что сжатие файлов базы данных (shrink), а именно оптимизация способа хранения данных внутри страниц, что позволяет разместить больше данных на меньшем количестве страниц.
Row Compression): оптимизирует хранение типов данных фиксированной длины, преобразуя их в переменную длину и не храня нулевые или пустые значения.Page Compression): включает в себя сжатие строк, а также дополнительные методы префиксного и словарного сжатия для уменьшения избыточности данных на странице. Обычно обеспечивает более высокую степень сжатия.Data Compression) является функцией, доступной только в выпуске Enterprise SQL Server.Сжатие включается для таблицы или индекса с помощью команды ALTER TABLE ... REBUILD WITH (DATA_COMPRESSION = PAGE/ROW). Мы можем использовать хранимую процедуру sp_estimate_data_compression_savings для предварительной оценки потенциальной экономии места, чтобы принять обоснованное решение.
Пример включения сжатия страниц для таблицы:
-- Оценка потенциальной экономии для таблицы 'MyBigTable' с сжатием страниц
EXEC sp_estimate_data_compression_savings 'dbo', 'MyBigTable', NULL, NULL, 'PAGE';
GO
-- Включение сжатия страниц для таблицы 'MyBigTable' (и всех ее индексов)
ALTER TABLE [dbo].[MyBigTable] REBUILD WITH (DATA_COMPRESSION = PAGE);
GO
-- Или для конкретного индекса
ALTER INDEX [IX_MyBigTable_MyColumn] ON [dbo].[MyBigTable] REBUILD WITH (DATA_COMPRESSION = PAGE);
GO
Эта операция является блокирующей и ресурсоемкой, поэтому ее следует выполнять в период минимальной нагрузки или в специально выделенное технологическое окно.
В 1С часто хранятся сканы документов, изображения, электронные письма и другие вложения, которые значительно увеличивают объем базы данных. Если эти файлы хранятся непосредственно в полях типа VARBINARY(MAX) в SQL Server, они быстро могут стать основными "потребителями" дискового пространства.
Решение: мы можем перенести эти вложения во внешние тома или другие файловые хранилища (например, в файловую систему, S3-совместимые хранилища и т.д.), а в базе данных 1С хранить только ссылки на эти файлы. Это существенно уменьшит размер самой базы данных SQL Server. В 1С для этого часто используется механизм "хранения файлов в томах" или "хранение файлов на диске".
Важно учитывать: при таком подходе выгрузка базы в файл 1Cv8.dt не будет содержать вложения, и их придется архивировать и переносить отдельно, обеспечивая целостность связей.
Неправильная настройка автороста файлов базы данных может приводить к неэффективному использованию места и проблемам с производительностью. Если авторост настроен с большим процентом, это может вызывать быстрые и частые увеличения файлов, что является ресурсоемкой операцией.
Рекомендация: Мы должны настраивать авторост фиксированным размером (например, 512 МБ, 1024 МБ или больше, в зависимости от динамики роста базы) вместо процентов. Фиксированный размер позволяет более предсказуемо управлять ростом и избегать частых небольших приращений. Также крайне полезно включить опцию "Database instant file initialization" (IFI) для ускорения операций роста файлов, так как она позволяет SQL Server резервировать новое дисковое пространство без необходимости предварительной очистки его нулями.
После значительных изменений данных (вставки, удаления, обновления) индексы могут фрагментироваться. Фрагментация снижает производительность запросов. Регулярная перестройка (REBUILD INDEX) или реорганизация (REORGANIZE INDEX) индексов помогает дефрагментировать их. Перестройка индексов создает индекс заново, что может также освободить неиспользуемое пространство внутри файлов данных, но это не является прямой заменой сжатию файлов данных. Основная цель перестройки индексов – повышение производительности запросов, а не уменьшение размера файла.
Мы можем использовать следующий запрос для выявления фрагментированных индексов:
SELECT
OBJECT_NAME(ips.object_id) AS TableName,
i.name AS IndexName,
ips.index_type_desc,
ips.avg_fragmentation_in_percent,
ips.page_count
FROM
sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') AS ips
INNER JOIN
sys.indexes AS i ON ips.object_id = i.object_id AND ips.index_id = i.index_id
WHERE
ips.avg_fragmentation_in_percent > 5 -- Порог для реорганизации/перестройки
ORDER BY
ips.avg_fragmentation_in_percent DESC;
Затем, для индексов с высокой фрагментацией (например, >30%), мы можем выполнять перестройку:
ALTER INDEX [IndexName] ON [TableName] REBUILD WITH (ONLINE = ON); -- ONLINE = ON только для Enterprise Edition
Для меньшей фрагментации (например, 5-30%) можно использовать реорганизацию или адаптивное обновление индексов MS SQL:
ALTER INDEX [IndexName] ON [TableName] REORGANIZE;
Для обеспечения оптимальной работы 1С с SQL Server мы рекомендуем инструкцию по настройке регламентных операций MS SQL SERVER и следующие настройки:
В целом, проблема разрастания базы данных 1С на MS SQL Server чаще всего эффективно решается путем комплексного подхода: управления данными (архивация, удаление ненужных данных в 1С), оптимизации хранения вложений, использования функций сжатия данных на уровне таблиц/индексов SQL Server, и правильной настройки самого SQL Server, а не регулярным сжатием файлов базы данных, которое, как мы выяснили, может приводить к большему количеству проблем, чем решений.