Журнал транзакций SQL Server

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

Назначение и принцип работы

Основная функция журнала транзакций — гарантировать, что база данных может быть восстановлена в согласованное состояние при любом сбое (отключение питания, ошибка оборудования, сбой программного обеспечения). SQL Server использует упреждающую запись журнала (Write-Ahead Logging, WAL). Согласно этому принципу, запись об изменении данных сначала фиксируется в журнале транзакций, и только после этого изменённая страница данных записывается на диск (операция checkpoint). Это гарантирует, что в случае сбоя система может либо откатить незавершённые транзакции (ROLLBACK), либо завершить уже зафиксированные, но не записанные в данные (REDO).

Журнал транзакций работает на уровне логических записей, называемых записями журнала (log records). Каждая запись содержит уникальный номер последовательности в журнале (Log Sequence Number, LSN), идентификатор транзакции, тип операции (вставка, обновление, удаление, создание индекса и т.д.), а также данные до и после изменения (для операций обновления).

Физическая структура

Журнал транзакций хранится в одном или нескольких физических файлах с расширением .ldf (Log Data File). Каждая база данных SQL Server имеет как минимум один файл журнала. Файл журнала логически делится на виртуальные файлы журнала (Virtual Log Files, VLF). VLF — это сегменты фиксированного размера, которые SQL Server управляет как единицами повторного использования пространства. Количество и размер VLF зависят от общего размера файла журнала и его настроек роста.

Журнал транзакций является циклическим (для простой модели восстановления) или растущим (для полной модели). В простой модели восстановления пространство, занятое записями о завершённых транзакциях, автоматически освобождается после контрольной точки. В полной модели записи хранятся до тех пор, пока не будет выполнена резервная копия журнала.

Модели восстановления

SQL Server поддерживает три модели восстановления, определяющие поведение журнала транзакций:

• Простая (Simple) — журнал автоматически усекается после каждой контрольной точки. Восстановление возможно только до последней полной или разностной резервной копии. Невозможно восстановление на момент времени (point-in-time recovery). Используется для баз данных, где потеря данных между резервными копиями допустима.
• Полная (Full) — все транзакции полностью записываются в журнал. Усечение журнала происходит только после выполнения резервного копирования журнала транзакций. Позволяет восстановить базу данных на любой момент времени в пределах последней резервной копии журнала. Требует регулярного резервного копирования журнала для предотвращения его неконтролируемого роста.
• С неполным протоколированием (Bulk-Logged) — для массовых операций (например, BULK INSERT, CREATE INDEX) протоколируется только результат, а не каждая строка. Это снижает нагрузку на журнал, но не позволяет восстановить базу данных на момент времени, если в период между резервными копиями выполнялись массовые операции.

Операции с журналом транзакций

Резервное копирование журнала

Резервная копия журнала транзакций (log backup) содержит все записи журнала, созданные с момента последней резервной копии журнала (или с момента создания полной резервной копии). Восстановление базы данных с использованием полной модели восстановления требует последовательного применения цепочки резервных копий журнала.

Усечение журнала

Усечение (log truncation) — это процесс освобождения пространства в файле журнала путём удаления записей о завершённых транзакциях, которые больше не нужны для восстановления. В простой модели усечение происходит автоматически. В полной модели — только после резервного копирования журнала. Если усечение не происходит (например, из-за долгой открытой транзакции или отсутствия резервного копирования), журнал может расти до заполнения диска.

Мониторинг и управление размером

Размер журнала транзакций можно отслеживать с помощью системных представлений, таких как sys.dm_db_log_space_usage и sys.database_files. Для управления размером используются команды DBCC SHRINKFILE (с осторожностью, так как это может привести к фрагментации VLF) и ALTER DATABASE ... MODIFY FILE для изменения начального размера или параметра автоприроста.

Проблемы и их решение

• Чрезмерный рост журнала — наиболее частая проблема. Причины: отсутствие резервного копирования журнала (в полной модели), долгие открытые транзакции, неправильная настройка модели восстановления, репликация или зеркалирование. Решение: выполнить резервное копирование журнала, найти и завершить долгие транзакции, переключить модель восстановления на простую (если это допустимо).
• Повреждение журнала — может произойти из-за сбоя диска или ошибки ввода-вывода. В этом случае база данных переходит в состояние «подозрительная» (suspect) или требует восстановления. Восстановление возможно только из резервной копии; в крайнем случае — с использованием опции WITH CONTINUE_AFTER_ERROR или восстановления до последней полной копии.
• Фрагментация VLF — возникает при частом увеличении или уменьшении файла журнала. Большое количество мелких VLF может снизить производительность операций записи. Решение: выполнить ручное усечение и изменение размера файла с оптимальным количеством VLF (рекомендуется не более 1000 VLF на файл).

Влияние на производительность

Журнал транзакций является узким местом для операций записи, так как каждая транзакция должна быть зафиксирована в журнале до подтверждения. Для повышения производительности рекомендуется:

• Размещать файл журнала на отдельном быстром диске (SSD) с низкой задержкой.
• Использовать правильный размер начального файла и автоприроста (фиксированный, а не в процентах).
• Минимизировать количество долгих транзакций.
• Настраивать параметр log buffer size (по умолчанию 60 КБ) и max server memory для оптимального кэширования.

Интересные факты

• Журнал транзакций не может быть размещён на сжатом томе NTFS (сжатие на уровне файловой системы не поддерживается).
• SQL Server не позволяет удалить файл журнала без удаления базы данных, но существуют методы для его восстановления при повреждении (например, использование опции EMERGENCY).
• В версиях SQL Server 2019 и выше появилась возможность использования ускоренного восстановления базы данных (Accelerated Database Recovery, ADR), которое уменьшает время отката долгих транзакций за счёт использования версионного хранения в журнале.
• Журнал транзакций используется не только для восстановления, но и для репликации (например, для технологии Always On Availability Groups), а также для создания моментальных снимков (snapshot) базы данных.

Источники

• Microsoft Docs: «Transaction Log Architecture» (SQL Server documentation)
• Microsoft Docs: «Manage the size of the transaction log file»
• Microsoft Docs: «Models of recovery (SQL Server)»
• Microsoft Docs: «Write-ahead logging (WAL)»
• Microsoft Docs: «Virtual Log Files (VLFs)»
• Microsoft Docs: «Accelerated database recovery (ADR)»