Categories
Database Backup

** Discover expert strategies for preventing and resolving MSSQL transaction log full errors (Error 9002). Learn rapid recovery techniques, VLF management, and architectural best practices for DBAs.

Для адміністраторів баз даних (DBA) та DevOps-інженерів, що керують Microsoft SQL Server, мало які сповіщення викликають таку миттєву тривогу, як помилка 9002: Журнал транзакцій для бази даних «X» переповнений. Коли журнал транзакцій заповнюється і не може збільшитися, база даних фактично стає доступною лише для читання. Усі операції INSERT, UPDATE та DELETE зупиняються, транзакції застосунків зазнають невдачі, а робота продуктивного середовища зупиняється.

Розуміння архітектури журналу транзакцій SQL Server, точна діагностика першопричини та виконання швидких процедур відновлення є критично важливими навичками для підтримки високої доступності. Цей вичерпний посібник розглядає механіку роботи журналу транзакцій, способи вирішення проблеми переповнення журналу в екстрених ситуаціях та архітектурні найкращі практики для запобігання повторенню цієї ситуації.

Розуміння архітектури журналу транзакцій SQL Server

Щоб ефективно усувати несправності, пов’язані з переповненням журналу транзакцій, ви повинні спочатку зрозуміти, як SQL Server записує та керує даними.

Протокол запису з випередженням (Write-Ahead Logging, WAL)

SQL Server використовує протокол Write-Ahead Logging (WAL). Щоразу, коли відбувається зміна даних, ця зміна спочатку записується в журнал транзакцій у пам’яті, а потім скидається у фізичний файл журналу на диску, перш ніж фактичні сторінки даних будуть оновлені у файлах бази даних (MDF/NDF). Це гарантує відповідність вимогам ACID (атомарність, узгодженість, ізольованість, довговічність), забезпечуючи можливість повторного виконання (roll forward) або скасування (roll back) транзакцій у разі збою SQL Server.

Віртуальні файли журналу (VLF) та циклічне ведення журналу

Внутрішньо фізичний файл журналу транзакцій (LDF) поділяється на менші логічні сегменти, які називаються віртуальними файлами журналу (VLF). Журнал транзакцій працює циклічно. У міру запису записів журналу вони заповнюють один VLF і переходять до наступного.

Коли журнал досягає кінця фізичного файлу, він намагається повернутися до початку. Однак він може перезаписати VLF лише в тому випадку, якщо цей VLF позначений як неактивний. Якщо всі VLF активні (це означає, що вони містять записи журналу, які все ще потрібні SQL Server), журнал не може «зациклитися». Якщо ввімкнено автозбільшення і є вільне місце на диску, фізичний файл збільшується. Якщо диск заповнений або автозбільшення обмежене, ви отримуєте помилку 9002.

Усічення журналу (Truncation) проти стиснення журналу (Shrinking)

Поширеною помилкою є думка, що усічення журналу зменшує розмір фізичного файлу.
* Усічення журналу (Log Truncation): Процес позначення активних VLF як неактивних, що робить простір доступним для повторного використання. Це не зменшує розмір файлу LDF на диску.
* Стиснення журналу (Log Shrinking): Процес фізичного зменшення розміру файлу LDF та повернення простору операційній системі.

У моделі відновлення Full (повна) усічення журналу відбувається лише після успішного завершення резервного копіювання журналу транзакцій (за умови, що жодні інші процеси не утримують журнал активним).

Діагностика помилки «Журнал транзакцій переповнений» (Помилка 9002)

Коли журнал переповнений, вашим першим кроком не повинно бути сліпе додавання місця на диску або стиснення файлів. Ви повинні визначити, чому журнал не може усіктися. SQL Server надає вбудований механізм, який точно вказує, що саме перешкоджає повторному використанню журналу, через представлення каталогу sys.databases.

Виконайте наступну команду T-SQL, щоб визначити «вузьке місце»:

SELECT 
    name AS DatabaseName, 
    recovery_model_desc AS RecoveryModel, 
    log_reuse_wait_desc AS LogReuseWaitReason
FROM sys.databases
WHERE name = 'YourDatabaseName';

Ви також можете перевірити поточне використання простору вашими журналами транзакцій за допомогою:

DBCC SQLPERF(LOGSPACE);

Поширені стани log_reuse_wait_desc

  1. LOG_BACKUP: База даних використовує модель відновлення Full або Bulk-Logged, і резервна копія журналу транзакцій не створювалася останнім часом. Це найпоширеніша причина.
  2. ACTIVE_TRANSACTION: Тривала транзакція (наприклад, масивна перебудова індексу або забута незафіксована транзакція) утримує журнал активним.
  3. REPLICATION / CDC: Увімкнено транзакційну реплікацію або Change Data Capture (CDC), і агент зчитування журналу (Log Reader Agent) ще не обробив транзакції.
  4. AVAILABILITY_REPLICA: У групі доступності AlwaysOn вторинна репліка відключена або синхронізується занадто повільно, змушуючи первинну репліку зберігати записи журналу, доки вони не будуть закріплені на вторинній.

Стратегії швидкого відновлення: вирішення проблеми у продуктивному середовищі

Залежно від значення log_reuse_wait_desc, ваша реакція на надзвичайну ситуацію буде різною. Ось стратегії швидкого відновлення для найпоширеніших сценаріїв.

Сценарій 1: Відсутні або невдалі резервні копії журналу (LOG_BACKUP)

Якщо тип очікування — LOG_BACKUP, рішення просте: ви повинні створити резервну копію журналу транзакцій.

BACKUP LOG [YourDatabaseName] 
TO DISK = 'N:BackupsYourDatabaseName_EmergencyLog.trn' 
WITH COMPRESSION, STATS = 10;

Після завершення резервного копіювання неактивні VLF будуть усічені, і SQL Server відновить нормальну роботу. Якщо ваш диск для резервних копій заповнений, можливо, доведеться створити резервну копію на тимчасовий мережевий ресурс або нульовий пристрій (не рекомендується, якщо базу даних неможливо легко відтворити, оскільки це розриває ланцюжок журналу):

-- ПОПЕРЕДЖЕННЯ: Це розриває ланцюжок журналу і ставить під загрозу відновлення на момент часу.
-- Використовуйте лише в крайньому разі і негайно виконайте ПОВНЕ резервне копіювання.
BACKUP LOG [YourDatabaseName] TO DISK = 'NUL';

Сценарій 2: Тривалі активні транзакції (ACTIVE_TRANSACTION)

Якщо одна транзакція виконується годинами, вона запобігає усіченню журналу протягом усього часу. Спочатку визначте транзакцію, що спричиняє проблему:

DBCC OPENTRAN('YourDatabaseName');

Ця команда повертає найстарішу активну транзакцію та її ідентифікатор процесу сервера (SPID). Ви можете отримати більше деталей про те, що робить цей SPID, запитавши динамічні адміністративні представлення (DMV):

SELECT 
    s.session_id,
    s.login_name,
    s.host_name,
    r.start_time,
    r.status,
    r.command,
    t.text AS QueryText
FROM sys.dm_exec_sessions s
JOIN sys.dm_exec_requests r ON s.session_id = r.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE s.session_id = <SPID_FROM_DBCC_OPENTRAN>;

Якщо транзакція є «шкідливим» запитом або завислим процесом, можливо, вам доведеться завершити її, щоб звільнити журнал.

KILL <SPID>;

Примітка: Примусове завершення масивної транзакції запустить процес відкату (rollback), який може зайняти значний час і тимчасово створити додаткову активність у журналі. Не перезапускайте службу SQL Server під час відкату, інакше база даних перейде в режим відновлення після перезапуску.

Сценарій 3: Екстрене виділення простору (диск заповнений на 100%)

Якщо файл LDF зайняв весь диск, ви навіть не зможете запустити резервне копіювання, оскільки SQL Server потребує невеликої кількості місця в журналі для запису самої події резервного копіювання. У цьому сценарії ви повинні додати вторинний файл журналу на інший диск, де є вільне місце.

ALTER DATABASE [YourDatabaseName]
ADD LOG FILE 
(
    NAME = N'YourDatabaseName_Log2',
    FILENAME = N'E:TempLogsYourDatabaseName_Log2.ldf',
    SIZE = 5GB,
    MAXSIZE = 50GB,
    FILEGROWTH = 1GB
);

Це негайно надає SQL Server простір для роботи. Як тільки база даних перейде в онлайн, створіть резервну копію журналу транзакцій, очистіть вторинний файл журналу та видаліть його:

-- 1. Створіть резервну копію журналу для його усічення
BACKUP LOG [YourDatabaseName] TO DISK = '...';

-- 2. Очистіть тимчасовий файл журналу
DBCC SHRINKFILE (N'YourDatabaseName_Log2', EMPTYFILE);

-- 3. Видаліть тимчасовий файл журналу
ALTER DATABASE [YourDatabaseName] REMOVE FILE [YourDatabaseName_Log2];

Найкращі практики запобігання та керування журналом транзакцій

Реактивне усунення несправностей є стресовим і впливає на SLA. Впровадження проактивних архітектурних та операційних практик є важливим для стабільності корпоративних баз даних.

1. Впровадження надійної автоматизованої стратегії резервного копіювання

Якщо база даних використовує модель відновлення Full, часте створення резервних копій журналу транзакцій є обов’язковим. Залежно від вашої цілі щодо точки відновлення (RPO) та обсягу транзакцій, резервне копіювання журналу має відбуватися кожні 5–15 хвилин.

Корпоративні рішення для резервного копіювання, такі як CloudSave, значно спрощують цей процес. Завдяки прямій інтеграції з SQL Server через VDI (Virtual Device Interface), CloudSave дозволяє адміністраторам баз даних налаштовувати політику високочастотного резервного копіювання журналу транзакцій. Це гарантує, що журнали постійно усікаються, надійно шифруються та зберігаються за межами основного майданчика або в незмінному хмарному сховищі, запобігаючи стану очікування LOG_BACKUP без необхідності створення складних користувацьких завдань SQL Agent.

2. Правильний розмір журналу транзакцій та керування VLF

Покладання на автозбільшення для керування розміром журналу транзакцій — це небезпечний антипатерн. Операції автозбільшення є «дорогими» і призупиняють обробку транзакцій, поки диск ініціалізується нулями (якщо не ввімкнено миттєву ініціалізацію файлів, яка не застосовується до файлів журналу).

Крім того, часті невеликі автозбільшення (наприклад, на 10% або 50 МБ за раз) призводять до фрагментації VLF. Журнал транзакцій з тисячами крихітних VLF значно погіршить час запуску бази даних, продуктивність резервного копіювання та затримку реплікації.

  • Попередньо задайте розмір журналу: Проаналізуйте свої найбільші операції обслуговування (наприклад, перебудову індексів) і задайте розмір файлу LDF так, щоб він міг вмістити їх без збільшення.
  • Встановіть фіксоване автозбільшення: Змініть автозбільшення з відсотків на фіксований розмір (наприклад, 1 ГБ або 5 ГБ), щоб гарантувати створення VLF прийнятного розміру.

Ви можете перевірити кількість VLF за допомогою наступного запиту (для SQL Server 2017+):

SELECT 
    db_name(database_id) AS DatabaseName,
    COUNT(vlf_sequence_number) AS VLF_Count
FROM sys.dm_db_log_info(DB_ID('YourDatabaseName'));

Якщо кількість VLF перевищує 500, подумайте про те, щоб дочекатися періоду низької активності, стиснути журнал до мінімального розміру та вручну збільшити його до необхідного розміру великими блоками.

3. Оптимізація операцій обслуговування індексів

Перебудова індексів — це операції, що повністю записуються в журнал, навіть у моделі відновлення Bulk-Logged (залежно від типу індексу). Перебудова індексу розміром 500 ГБ створить щонайменше 500 ГБ записів у журналі транзакцій.

Щоб зменшити роздуття журналу під час обслуговування:
* Використовуйте SORT_IN_TEMPDB = ON при перебудові індексів. Це переносить фазу сортування в TempDB, зменшуючи навантаження на журнал транзакцій користувацької бази даних.
* Перейдіть від перебудови індексів до їх реорганізації, де це можливо, оскільки реорганізація є більш ефективною з точки зору журналу і може бути перервана без відкату всієї операції.
* Виконуйте великі операції DELETE або UPDATE порціями. Замість видалення 10 мільйонів рядків за одну транзакцію, видаляйте їх порціями по 50 000, фіксуючи зміни та дозволяючи резервному копіюванню журналу усікати його між порціями.

4. Моніторинг високої доступності та топологій реплікації

У групах доступності AlwaysOn первинна репліка не може усікти свій журнал, доки записи журналу не будуть закріплені на всіх синхронних та асинхронних вторинних репліках.

Якщо вторинна репліка виходить з ладу або якщо пропускна здатність мережі не встигає за швидкістю генерації транзакцій на первинній репліці, черга відправки (send queue) первинної репліки зростатиме, і журнал переповниться (тип очікування AVAILABILITY_REPLICA).

Впровадьте надійний моніторинг лічильника продуктивності SQLServer:Replica > Log Send Queue. Якщо вторинна репліка втрачена остаточно, ви повинні видалити її з групи доступності або призупинити переміщення даних, щоб дозволити журналу первинної репліки усіктися.

Висновок

Зіткнення з переповненим журналом транзакцій — це «бойове хрещення» для адміністраторів баз даних, але це не обов’язково має призводити до тривалого простою. Розуміючи механіку роботи протоколу Write-Ahead Logging та VLF, ви можете швидко діагностувати першопричину за допомогою sys.databases і застосувати правильну стратегію швидкого відновлення.

Довгострокова стабільність залежить від відмови від реактивних виправлень. Попереднє налаштування розміру файлів журналу, оптимізація процедур обслуговування та використання корпоративних платформ резервного копіювання, таких як CloudSave, для забезпечення суворих автоматизованих графіків резервного копіювання журналу гарантують, що ваші журнали транзакцій залишатимуться здоровими, усіченими та готовими до підтримки високонавантажених робочих процесів.

Категорії