Почему не уменьшается журнал транзакций LDF в MS SQL и как его правильно сжать?

← На главную

При администрировании баз данных 1С:Предприятие на платформе MS SQL Server (особенно на бесплатной версии Express) администраторы часто сталкиваются с лавинообразным ростом файла журнала транзакций (с расширением .ldf). В некоторых случаях размер лога может в десятки раз превышать размер самой базы данных. Например, при размере самой базы данных 1С всего в 450 Мб файл журнала транзакций может разрастись до 10 Гб и более.

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

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

Шаг 1. Первоочередная диагностика: почему SQL Server блокирует очистку лога?

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

Откроем SQL Server Management Studio (SSMS), создадим новый запрос к нашей базе данных и выполним следующий скрипт:


SELECT name, log_reuse_wait, log_reuse_wait_desc 
FROM sys.databases 
WHERE name = 'Имя_Вашей_Базы_1С';

В полученных результатах нас больше всего интересует колонка log_reuse_wait_desc. Именно она содержит текстовое описание причины, по которой SQL Server не может повторно использовать пространство внутри лога. Рассмотрим основные значения, которые наиболее часто встречаются при работе с базами данных 1С:

LOG_BACKUP. База данных находится в полной модели восстановления (FULL), и СУБД ожидает создания резервной копии самого журнала транзакций. Важно понимать: при полной модели восстановления обычные бэкапы базы данных (файлы .bak) не очищают лог. Для освобождения места СУБД требует регулярного создания бэкапов именно журнала транзакций (файлов .trn).
ACTIVE_TRANSACTION. В базе присутствует незавершенная активная транзакция. Для систем 1С это регулярная ситуация: зависший сеанс пользователя, тяжелое фоновое задание, затянувшийся процесс проведения документов или незакрытая транзакция при аварийном завершении работы клиента.
REPLICATION. СУБД считает, что для базы настроена репликация или технология отслеживания измененных данных (CDC), и ожидает, пока транзакции будут переданы подписчику. Такое часто происходит при некорректном переносе баз данных между серверами через бэкапы, когда «фантомные» настройки репликации блокируют лог на новом сервере.
CHECKPOINT. База данных ожидает выполнения контрольной точки — процесса переноса измененных («грязных») страниц данных из оперативной памяти на физический диск.

Шаг 2. Понимание физической структуры лога (концепция VLF)

Чтобы понять, почему стандартная команда SHRINKFILE не дает эффекта даже при отсутствии блокировок, нам необходимо рассмотреть внутреннее устройство файла журнала транзакций. Физический файл .ldf состоит из множества логических сегментов, которые называются VLF (Virtual Log Files — виртуальные файлы журнала).

SQL Server записывает транзакции в эти виртуальные файлы последовательно. Сжатие физического файла журнала транзакций возможно только с его конца. Если активная транзакция (или указатель текущей записи) находится в виртуальном файле VLF, который расположен в самом конце физического файла .ldf, то СУБД не сможет сжать этот файл, даже если предыдущие 99% пространства лога абсолютно пусты и не используются.

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

Способ 1. Безопасное решение для продуктивных баз (Резервное копирование журнала)

Если ваша база данных является рабочей и для нее критически важно сохранение возможности восстановления на любой момент времени (Point-in-Time Recovery), база должна работать в полной модели восстановления (FULL). В этом случае единственно верным решением является регулярный бэкап лога.

Для разового сжатия выполним последовательно бэкап журнала транзакций и последующее усечение файла. Нам понадобится узнать логическое имя файла журнала транзакций (его можно посмотреть в свойствах базы данных на вкладке «Файлы» или выполнив системную процедуру exec sp_helpdb 'Имя_Вашей_Базы'). Обычно имя лога выглядит как Имя_Базы_log.

Выполним следующий T-SQL скрипт:


-- Шаг 1: Создаем резервную копию журнала транзакций во внешний файл
BACKUP LOG [Имя_Вашей_Базы_1С] 
TO DISK = N'C:\Backup\Database_log_backup.trn' 
WITH NOFORMAT, NOINIT, Name = N'LogBackup', SKIP, NOREWIND, NOUNLOAD, STATS = 10;
GO

-- Шаг 2: Выполняем сжатие физического файла журнала до минимального размера
DBCC SHRINKFILE (N'Имя_Вашей_Базы_1С_log', 0, TRUNCATEONLY);
GO

Параметр TRUNCATEONLY указывает системе просто освободить все неиспользуемое пространство в конце файла операционной системе, не пытаясь перераспределять страницы внутри самого файла, что делает операцию мгновенной и безопасной.

Важное примечание для MS SQL Express: В бесплатной версии SQL Express отсутствует компонент SQL Server Agent (планировщик задач СУБД). Это значит, что вы не сможете настроить регулярный автоматический бэкап лога средствами самого SQL-сервера. Если вы решите оставить модель FULL на SQL Express, вам придется настроить резервное копирование лога через внешние скрипты (например, bat-файл с вызовом утилиты sqlcmd), запускаемые через стандартный Планировщик задач Windows.

Способ 2. Быстрое решение для баз разработки, тестов и баз на SQL Express (Перевод в SIMPLE)

Если база данных используется для разработки, тестов или на ней работает небольшое количество пользователей в файлово-клиентском режиме на MS SQL Express, использование полной модели восстановления чаще всего избыточно. В таких случаях гораздо эффективнее перевести базу в простую модель восстановления (SIMPLE), при которой транзакции автоматически очищаются из лога сразу после их фиксации и выполнения контрольной точки.

Разберем по шагам, как перевести базу в режим SIMPLE, сжать файл лога до фиксированного размера и (при необходимости) вернуть полную модель восстановления:


-- Шаг 1: Переводим базу данных в простую модель восстановления (SIMPLE)
ALTER DATABASE [Имя_Вашей_Базы_1С] SET RECOVERY SIMPLE WITH NO_WAIT;
GO

-- Шаг 2: Очищаем лог и сжимаем его до целевого размера (например, до 100 Мб)
DBCC SHRINKFILE (N'Имя_Вашей_Базы_1С_log', 100);
GO

-- Шаг 3 (Опционально): Если вам действительно необходима модель FULL, возвращаем ее обратно
ALTER DATABASE [Имя_Вашей_Базы_1С] SET RECOVERY FULL;
GO

Для небольших баз данных 1С (как в примере автора темы — 450 Мб) рекомендуется оставить модель восстановления SIMPLE на постоянной основе. Это полностью избавит вас от проблемы неконтролируемого роста файла .ldf в будущем, а бэкапы всей базы можно будет делать целиком один или несколько раз в сутки.

Способ 3. Что делать при зависших транзакциях в 1С?

Если диагностика на Шаге 1 показала статус блокировки ACTIVE_TRANSACTION, стандартное сжатие не сработает ни при каких условиях. Нам необходимо найти и корректно завершить процесс, который удерживает транзакцию.

Чтобы определить ID зависшего процесса (SPID), выполним команду:

DBCC OPENTRAN ('Имя_Вашей_Базы_1С');

СУБД вернет информацию о старейшей активной транзакции, включая SPID (ID системного процесса) и время начала транзакции. Получив SPID, мы можем проанализировать его активность с помощью встроенного монитора активности SSMS (Activity Monitor) или завершить его принудительно прямо из SQL-запроса:


-- Где XX - это полученный SPID зависшей транзакции
KILL XX;

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

Способ 4. Решение проблемы с фантомной репликацией

Если диагностический запрос выявил статус блокировки REPLICATION, но вы точно знаете, что никакая репликация данных или механизмы CDC для этой базы не настраивались (или использовались ранее, но база была перенесена на другой сервер), СУБД блокирует лог «фантомно».

Чтобы полностью сбросить все признаки репликации и освободить файл лога для усечения, выполните специальную системную процедуру:


USE [Имя_Вашей_Базы_1С];
GO
EXEC sp_removedbreplication 'Имя_Вашей_Базы_1С';
GO

После успешного выполнения этой процедуры выполните сжатие файла .ldf. Файл мгновенно уменьшится до минимального объема.

Почему помогло изменение начального размера лога через GUI (свойства базы)?

В ходе обсуждения на форуме автор темы отметил, что ему помогло простое изменение параметра «Начальный размер» (Initial Size) для файла журнала транзакций в свойствах базы данных через графический интерфейс Management Studio.

Проанализируем этот феномен. Когда мы заходим в свойства базы данных в SSMS, меняем начальный размер файла .ldf в меньшую сторону и нажимаем кнопку «ОК», утилита Management Studio генерирует и отправляет SQL-серверу пакет инструкций. Под капотом этой визуальной операции скрывается неявный вызов системных команд изменения параметров базы данных и запуск DBCC SHRINKFILE с указанием нового целевого размера.

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