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 (атомарност, последователност, изолация, устойчивост) съответствие, като гарантира, че в случай на срив, SQL Server може да преиграе (roll forward) или отмени (roll back) транзакциите.

Виртуални лог файлове (VLFs) и циклично логване

Вътрешно физическият транзакционен лог файл (LDF) е разделен на по-малки, логически сегменти, наречени виртуални лог файлове (VLF). Транзакционният лог работи циклично. Докато записите в лога се пишат, те запълват един VLF и преминават към следващия.

Когато логът достигне края на физическия файл, той се опитва да се върне в началото. Той обаче може да презапише VLF само ако този VLF е маркиран като неактивен. Ако всички VLF са активни (което означава, че съдържат записи в лога, които все още са необходими на SQL Server), логът не може да се превърти. Ако автоматичното разширяване (auto-growth) е активирано и има налично дисково пространство, физическият файл се разширява. Ако дискът е пълен или автоматичното разширяване е ограничено, ще срещнете Грешка 9002.

Трункиране на лога срещу свиване на лога

Често срещано погрешно схващане е, че трункирането (изрязването) на лога намалява размера на физическия файл.
* Трункиране на лога: Процесът на маркиране на активни VLF като неактивни, което прави пространството достъпно за повторна употреба. Това не намалява размера на LDF файла на диска.
* Свиване на лога (Log Shrinking): Процесът на физическо намаляване на размера на LDF файла и връщане на пространството към операционната система.

При модела на пълно възстановяване (Full Recovery), трункирането на лога се случва само когато архивирането на транзакционния лог е успешно завършено (при условие че няма други процеси, които държат лога активен).

Диагностициране на грешката „Транзакционният лог е пълен“ (Грешка 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 Availability Group, вторична реплика е прекъсната или се синхронизира твърде бавно, принуждавайки първичната реплика да запази записите в лога, докато не бъдат потвърдени на вторичната.

Стратегии за бързо възстановяване: Разрешаване на проблема в продукция

В зависимост от върнатия 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 recovery), честите архиви на транзакционния лог са задължителни. В зависимост от вашата цел за точка на възстановяване (RPO) и обема на транзакциите, архивирането на лога трябва да се извършва на всеки 5 до 15 минути.

Корпоративни решения за архивиране като CloudSave значително опростяват този процес. Чрез директна интеграция с SQL Server чрез VDI (Virtual Device Interface), CloudSave позволява на DBA да конфигурират базирани на политики, високочестотни архиви на транзакционния лог. Това гарантира, че логовете се трункират непрекъснато, криптират се сигурно и се съхраняват извън обекта или в неизменно облачно хранилище, предотвратявайки състоянието на изчакване LOG_BACKUP, без да са необходими сложни персонализирани задачи на SQL Agent.

2. Оразмерете правилно транзакционния лог и управлявайте VLF

Разчитането на автоматично разширяване за управление на размера на вашия транзакционен лог е опасен анти-модел. Операциите за автоматично разширяване са скъпи и спират обработката на транзакции, докато дискът се инициализира с нули (освен ако не е активирана инициализация на файловете (Instant File Initialization), което не се отнася за лог файлове).

Освен това, честите, малки автоматични разширявания (напр. нарастване с 10% или 50MB наведнъж) водят до фрагментация на VLF. Транзакционен лог с хиляди малки VLF сериозно ще влоши времето за стартиране на базата данни, производителността на архивирането и латентността на репликацията.

  • Предварително оразмерете лога: Анализирайте най-големите си операции по поддръжка (като преизграждане на индекси) и предварително оразмерете LDF файла, за да ги поеме, без да се налага разширяване.
  • Задайте фиксирано автоматично разширяване: Променете автоматичното разширяване от процент на фиксиран размер (напр. 1GB или 5GB), за да гарантирате, че 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. Оптимизирайте операциите по поддръжка на индекси

Преизграждането на индекси са напълно логвани операции, дори в модела на групово логване (в зависимост от типа индекс). Преизграждането на 500GB индекс ще генерира поне 500GB записи в транзакционния лог.

За да смекчите раздуването на лога по време на поддръжка:
* Използвайте SORT_IN_TEMPDB = ON при преизграждане на индекси. Това прехвърля фазата на сортиране към TempDB, намалявайки тежестта върху транзакционния лог на потребителската база данни.
* Преминете от преизграждане на индекси към реорганизиране на индекси, където е възможно, тъй като реорганизациите са по-ефективни по отношение на лога и могат да бъдат прекъснати, без да се отменя цялата операция.
* Разделяйте големи операции DELETE или UPDATE на партиди. Вместо да изтривате 10 милиона реда в една транзакция, изтривайте ги на порции от 50 000, като извършвате commit и позволявате на архивите на лога да трункират лога между партидите.

4. Наблюдавайте топологиите за висока наличност и репликация

В AlwaysOn Availability Groups, първичната реплика не може да трункира своя лог, докато записите в лога не бъдат потвърдени на всички синхронни и асинхронни вторични реплики.

Ако вторична реплика излезе офлайн или ако честотната лента на мрежата не може да се справи с темпото на генериране на транзакции на първичната, опашката за изпращане на първичната ще нарасне и логът ще се запълни (тип изчакване AVAILABILITY_REPLICA).

Внедрете стабилно наблюдение за брояча на производителността SQLServer:Replica > Log Send Queue. Ако вторична реплика е окончателно загубена, трябва да я премахнете от групата за наличност или да спрете движението на данни, за да позволите на първичния лог да се трункира.

Заключение

Сблъсъкът с пълен транзакционен лог е „бойно кръщение“ за администраторите на бази данни, но не е задължително да води до продължителни престои. Като разбирате механиката на Write-Ahead Logging и VLF, можете бързо да диагностицирате първопричината, използвайки sys.databases, и да приложите правилната стратегия за бързо възстановяване.

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

Категории