Модели восстановления SQL Server и рост журнала транзакций

Если файл журнала транзакций SQL Server всё растёт и растёт, база почти наверняка находится в модели восстановления full, и никто не делает backup журнала. В модели full журнал можно использовать повторно только после того, как backup журнала куда-то его скопировал, так что без них каждое изменение с момента создания базы копится в файле .ldf, пока не кончится диск. Исправление - это решение, а не команда: либо вам нужно восстановление на момент времени, и тогда вы запускаете BACKUP LOG по расписанию каждые 15-60 минут, либо не нужно, и тогда вы переходите на модель восстановления simple. После этого сожмите журнал один раз до разумного размера и больше никогда этого не делайте.

Остальная часть руководства объясняет почему, чтобы вы могли принять это решение осознанно и распознать другие, более редкие причины, по которым журнал отказывается перестать расти.

Что делает журнал транзакций#

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

Внутри файл журнала разделён на виртуальные файлы журнала (VLF) и используется по кругу. Новые записи пишутся в голову; как только ни одна запись в VLF больше не нужна, этот VLF можно пометить как пригодный для повторного использования - это называется усечением журнала. Усечение не уменьшает файл. Оно лишь позволяет SQL Server писать поверх уже имеющегося места. Файл растёт, только когда голова круга догоняет VLF, который всё ещё нужен.

Так что вопрос «почему мой журнал такой большой?» на самом деле звучит как «что мешает усечению?». Обычный ответ определяет модель восстановления.

Три модели восстановления#

Simple подходит большинству небольших баз приложений. Журнал должен хранить только активные в данный момент транзакции, поэтому сам по себе остаётся небольшим. Цена в том, что восстановиться можно только на момент последнего полного или разностного backup: ночной backup означает возможную потерю до суток.

Full журналирует всё и хранит это, пока его не заберёт backup журнала. С полным backup в воскресенье и backup журнала каждые 15 минут можно восстановить базу в том виде, в каком она была в четверг в 14:31, - за минуту до того, как кто-то выполнил DELETE без WHERE. Эта возможность - единственная причина использовать модель full, и без backup журнала вы получаете все издержки и никакой пользы.

Bulk-logged - это модель full, в которой некоторые массовые операции журналируются минимально: BULK INSERT, загрузки bcp с блокировкой таблицы, SELECT INTO, перестроение индексов. Журнал при больших загрузках остаётся меньше, но backup журнала, содержащий массовую операцию, можно восстановить только до её конца, а не на момент внутри неё. Эту модель задумано включать на время окна обслуживания и затем выключать, а не оставлять включённой.

Проверьте все базы сразу, заодно с тем, что удерживает каждый журнал:

SELECT name, recovery_model_desc, log_reuse_wait_descFROM sys.databasesORDER BY name;

Новые базы наследуют модель восстановления от системной базы model. В Express model из коробки настроена на simple, так что созданные там базы начинают с модели simple; в Standard и Enterprise - на full. База, восстановленная с другого сервера, сохраняет модель, которая была на источнике, и именно так база в модели full без backup журнала оказывается на небольшом экземпляре Express.

Почему журнал не усекается: log_reuse_wait_desc#

log_reuse_wait_desc называет причину, по которой самая старая часть журнала всё ещё нужна. Значения, которые вы реально увидите:

LOG_BACKUP встречается чаще всего, и это случай, описанный в начале. ACTIVE_TRANSACTION на втором месте: один сеанс открыл транзакцию и так и не закрыл - окно SSMS с BEGIN TRAN без COMMIT, приложение, потерявшее соединение посреди транзакции, ещё не закончившаяся миграция. Всё, что записано после начала такой транзакции, не может быть усечено даже в модели simple. Найти её:

DBCC OPENTRAN;SELECT s.session_id, s.login_name, s.host_name, s.program_name,       t.transaction_begin_timeFROM sys.dm_tran_active_transactions AS tJOIN sys.dm_tran_session_transactions AS st ON st.transaction_id = t.transaction_idJOIN sys.dm_exec_sessions AS s ON s.session_id = st.session_idORDER BY t.transaction_begin_time;

Журнал удерживает самая старая строка. Спросите её владельца или, если сеанс явно брошен, выполните KILL, что откатит транзакцию, - а откат может длиться столько же, сколько сама работа.

Ещё одна тонкость: база, переключённая на модель full, на самом деле не ведёт себя как full, пока не сделан её первый полный backup. До этого нет основы для цепочки журнала, и SQL Server усекает журнал так, будто модель simple. Иногда журнал неделями остаётся маленьким, а потом начинает расти в тот день, когда прошёл первый полный backup. Это момент, когда началась цепочка журнала.

Насколько велик журнал и насколько он заполнен#

-- Every database: log size in MB and percentage usedDBCC SQLPERF(LOGSPACE);-- The current database in more detailSELECT total_log_size_in_bytes / 1048576.0 AS log_mb,       used_log_space_in_bytes / 1048576.0 AS used_mb,       used_log_space_in_percentFROM sys.dm_db_log_space_usage;-- Number of virtual log filesSELECT COUNT(*) AS vlf_count FROM sys.dm_db_log_info(DB_ID());

Большой журнал, заполненный на 3 процента, сам по себе не проблема - место когда-то понадобилось и будет использовано снова. Проблема - журнал, заполненный на 99 процентов и продолжающий расти. Важно и число VLF: журнал, выросший тысячами крошечных приращений, в итоге содержит тысячи VLF, что замедляет восстановление при запуске и восстановление из backup. Несколько сотен - ничего особенного; десятки тысяч стоит исправить, сжав журнал и снова увеличив его несколькими крупными шагами.

Когда журналу больше некуда расти - диск заполнен или задан максимальный размер, - SQL Server выдаёт ошибку 9002 и перестаёт принимать изменения в этой базе:

Msg 9002, Level 17, State 2The transaction log for database 'appdb' is full due to 'LOG_BACKUP'.

Сообщение называет причину из log_reuse_wait_desc, и по ней понятно, какое исправление подходит. Чтение продолжает работать; запись не проходит, пока не освободится место.

В Express здесь есть особая грань. Потолок в 10 ГБ на базу учитывает только файлы данных, так что журнал может незаметно перерасти его, а на хостинговом тарифе журнал делит диск тарифа с данными и backup. На диске в 10 или 20 ГБ журнал в модели full без присмотра - это авария в замедленной съёмке.

Исправление 1: переход на модель simple#

Если ночной или более частый полный backup - приемлемая точка восстановления, перейдите на simple, дайте журналу усечься и сожмите его один раз:

ALTER DATABASE [appdb] SET RECOVERY SIMPLE;CHECKPOINT;-- Find the log file's logical nameSELECT name, size / 128 AS size_mb FROM sys.database_files WHERE type_desc = 'LOG';-- Shrink it to 1 GB, then give it a sensible fixed growthDBCC SHRINKFILE (N'appdb_log', 1024);ALTER DATABASE [appdb]MODIFY FILE (NAME = N'appdb_log', FILEGROWTH = 256MB);

Если сжатие не уменьшает файл, значит, активная часть журнала находится в конце файла; через несколько минут снова выполните CHECKPOINT и сжатие. Задавайте размер журнала под самую большую регулярную транзакцию - часто это перестроение индекса или ночной импорт, - а не как можно меньше. Журнал, который сжимается до 100 МБ, чтобы каждую ночь снова вырасти до 2 ГБ, делает бессмысленную работу, а каждое приращение приостанавливает запись, пока новое место заполняется нулями. SQL Server 2022 умеет пропускать это обнуление для приращений журнала до 64 МБ, но более крупные приращения по-прежнему инициализируются нулями.

Сжать журнал один раз после устранения причины - нормально. Сжимать его по расписанию - признак того, что причину так и не устранили. Сжимать по расписанию файлы данных ещё хуже, потому что это фрагментирует каждый затронутый индекс.

Исправление 2: остаться в модели full и делать backup журнала#

Если потеря данных за сутки неприемлема, оставьте модель full и делайте backup журнала так часто, чтобы он никогда не становился большим:

BACKUP LOG [appdb]TO DISK = N'/var/opt/mssql/data/appdb-log-20261008-1415.trn'WITH CHECKSUM, INIT;

Каждый backup журнала копирует журнал с момента предыдущего и позволяет этой части усечься. Обычный интервал - каждые 15 минут; он и есть ваша максимальная потеря данных, если сервер пропадёт вместе с журналом. Называйте каждый файл по времени, никогда их не перезаписывайте и храните все backup журнала вплоть до самого старого полного backup, который вы храните, - цепочку с одним отсутствующим файлом нельзя восстановить дальше этого разрыва.

В Express нет SQL Server Agent, поэтому расписание работает вне движка: вызов sqlcmd из cron на другой машине со скриптом, который собирает имя файла из даты и времени, - точно так же, как для полных backup. Скрипт есть в статье backup и восстановление SQL Server, а флаг -b, делающий сбои видимыми, объясняет статья sqlcmd и bcp. Backup журнала полезны, только если они тоже покидают сервер, так что копируйте их по мере создания.

Восстановление на момент времени#

Вот что покупает модель full. Допустим, неудачный DELETE выполнился в 14:32. Сначала сделайте backup хвоста журнала: он захватывает всё до текущего момента и оставляет базу в состоянии восстановления, чтобы больше ничто не могло её изменить:

USE master;BACKUP LOG [appdb]TO DISK = N'/var/opt/mssql/data/appdb-tail.trn'WITH NORECOVERY;

Затем восстановите последний полный backup, разностный, если он есть, и все backup журнала по порядку, остановившись непосредственно перед ошибкой:

RESTORE DATABASE [appdb] FROM DISK = N'/var/opt/mssql/data/appdb-full.bak'WITH NORECOVERY, REPLACE;RESTORE LOG [appdb] FROM DISK = N'/var/opt/mssql/data/appdb-log-20261008-1400.trn'WITH NORECOVERY;RESTORE LOG [appdb] FROM DISK = N'/var/opt/mssql/data/appdb-log-20261008-1415.trn'WITH NORECOVERY;RESTORE LOG [appdb] FROM DISK = N'/var/opt/mssql/data/appdb-tail.trn'WITH STOPAT = '2026-10-08T14:31:30', RECOVERY;

Часто лучше восстановить базу под новым именем рядом с рабочей и скопировать обратно только удалённые строки, чтобы сохранить всё, что после 14:32 записали другие пользователи. История backup в msdb перечисляет каждый backup журнала с его временным диапазоном, и так находят нужные файлы, когда их сотни. Отрепетируйте это один раз на копии до того, как понадобится; доводы приводит статья проверка восстановления до того, как оно понадобится.

Модели восстановления и backup хостера#

Хостинговые серверы обычно идут с каким-то собственным backup, и стоит чётко понимать, как он соотносится с моделью восстановления, потому что они отвечают на разные вопросы.

Backup на уровне хостера копирует файлы сервера на определённый момент, и его восстановление возвращает к этому моменту весь сервер. О цепочках журнала он ничего не знает. Он не может восстановить одну базу на 14:31, и он хорош ровно настолько, насколько хорошо его расписание: ночная копия - это ночная точка восстановления, в какой бы модели ни была база. База в модели full без backup журнала ничего не выигрывает от backup хостера, кроме большего журнала для копирования.

Нативные backup - это слой, который понимает SQL Server. Полный backup даёт согласованную базу, разностный сокращает восстановление, а backup журнала дают точку восстановления с точностью до минуты. Практичное сочетание для небольшой базы: модель simple, нативный полный backup каждую ночь за несколько минут до собственного backup хостера по расписанию, чтобы в архиве всегда был согласованный .bak, и копия этого файла, раз в неделю скачанная куда-то ещё. Если нужна точка восстановления лучше суточной, перейдите на модель full и добавьте backup журнала, которые покидают сервер по мере создания.

В RE:NODE тарифы SQL Server идут с одним-четырьмя слотами backup в зависимости от уровня; backup делаются по запросу или по расписанию на вкладке Schedules и хранятся вне защищаемой машины. Это backup всего сервера ровно в описанном выше смысле. Созданную для вас базу вы настраиваете сами, так что проверьте её модель восстановления запросом из начала этого руководства в первый же день, а не после того, как заполнится диск.

Привычки, которые держат журнал маленьким#

Модель восстановления задаёт базовый уровень. Повседневный размер журнала определяет самая большая отдельная транзакция, потому что открытая транзакция удерживает весь журнал, записанный с её начала.

• Удаляйте пакетами. Один DELETE десяти миллионов строк - это одна транзакция, которая должна поместиться в журнал. Цикл, удаляющий по 5000 строк за раз, позволяет журналу усекаться между пакетами - после контрольной точки в модели simple или после следующего backup журнала в модели full.
• Держите транзакции короткими. Не держите транзакцию открытой, пока ждёте пользователя, HTTP-вызов или загрузку файла.
• Следите за обслуживанием индексов. Перестроение большого индекса в модели full журналируется полностью и может породить журнал размером с индекс. Перестраивайте только то, что действительно нужно, а на NVMe это совсем немного; обоснование есть в статье индексы и планы выполнения SQL Server.
• Задавайте приращение в мегабайтах, а не в процентах. Десять процентов маленького журнала - крошечное приращение, которое дробит его на множество VLF; десять процентов огромного - долгая пауза.

WHILE 1 = 1BEGIN    DELETE TOP (5000) FROM dbo.Events WHERE CreatedAt < '2026-01-01';    IF @@ROWCOUNT = 0 BREAK;END;

FAQ#

Безопасно ли удалить файл .ldf, чтобы освободить место?

Нет. Журнал - часть базы, и его удаление, пока в нём есть активные транзакции, может сделать базу невосстановимой или вынудить к восстановлению с потерей данных. Устраните причину роста, затем сожмите его через DBCC SHRINKFILE.

Какую модель выбрать для небольшого веб-приложения: simple или full?

Simple, если только потеря данных, записанных после последнего backup, не будет действительно болезненной. Большинство небольших приложений делают ночной полный backup и мирятся с этим окном. Если выбираете full, настройте backup журнала в тот же день, иначе журнал будет расти без ограничений.

Почему журнал вырос даже в модели simple?

Потому что что-то держало транзакцию открытой или одно выражение изменило огромное число строк. В модели simple журнал всё равно должен целиком хранить каждую активную транзакцию. Ищите виновника через log_reuse_wait_desc и DBCC OPENTRAN.

Учитывается ли журнал транзакций в лимите Express в 10 ГБ?

Нет. Лимит относится к файлам данных каждой базы. Но журнал всё равно занимает диск, а на хостинговом тарифе делит место с данными и с любыми backup, хранящимися на сервере, так что следить за ним нужно точно так же.

Как часто делать backup журнала?

Так часто, сколько работы вы готовы потерять. Каждые 15 минут - обычное значение по умолчанию; каждые 5 подходят нагруженным системам. Более частые backup к тому же сохраняют каждый файл небольшим, а сам журнал - компактным.