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.

Dla administratorów baz danych (DBA) i inżynierów DevOps zarządzających Microsoft SQL Server, niewiele alertów wywołuje tak natychmiastowy niepokój jak błąd 9002: Dziennik transakcji dla bazy danych „X” jest pełny. Gdy dziennik transakcji zapełni się i nie może wzrosnąć, baza danych staje się efektywnie tylko do odczytu. Wszystkie operacje INSERT, UPDATE i DELETE zostają wstrzymane, transakcje aplikacji kończą się niepowodzeniem, a produkcja staje w miejscu.

Zrozumienie podstawowej architektury dziennika transakcji SQL Server, dokładne zdiagnozowanie przyczyny źródłowej i wykonanie szybkich procedur odzyskiwania to kluczowe umiejętności w utrzymaniu wysokiej dostępności. Ten kompleksowy przewodnik omawia mechanikę dziennika transakcji, sposoby rozwiązania problemu pełnego dziennika w sytuacjach awaryjnych oraz najlepsze praktyki architektoniczne, które zapobiegną powtórzeniu się tej sytuacji.

Zrozumienie architektury dziennika transakcji SQL Server

Aby skutecznie rozwiązywać problemy z pełnym dziennikiem transakcji, musisz najpierw zrozumieć, w jaki sposób SQL Server zapisuje i zarządza danymi.

Logowanie z wyprzedzeniem (Write-Ahead Logging – WAL)

SQL Server używa protokołu Write-Ahead Logging (WAL). Ilekroć następuje modyfikacja danych, zmiana jest najpierw zapisywana w dzienniku transakcji w pamięci, a następnie przesyłana do fizycznego pliku dziennika na dysku, zanim rzeczywiste strony danych zostaną zaktualizowane w plikach bazy danych (MDF/NDF). Gwarantuje to zgodność z zasadami ACID (Atomowość, Spójność, Izolacja, Trwałość), zapewniając, że w przypadku awarii SQL Server może powtórzyć (roll forward) lub cofnąć (roll back) transakcje.

Wirtualne pliki dziennika (VLF) i logowanie cykliczne

Wewnętrznie fizyczny plik dziennika transakcji (LDF) jest podzielony na mniejsze, logiczne segmenty zwane wirtualnymi plikami dziennika (VLF). Dziennik transakcji działa cyklicznie. W miarę zapisywania rekordów dziennika, wypełniają one jeden VLF i przechodzą do następnego.

Gdy dziennik osiągnie koniec fizycznego pliku, próbuje zawinąć się do początku. Może jednak nadpisać VLF tylko wtedy, gdy ten jest oznaczony jako nieaktywny. Jeśli wszystkie VLF są aktywne (co oznacza, że zawierają rekordy dziennika nadal wymagane przez SQL Server), dziennik nie może się zawinąć. Jeśli włączone jest automatyczne zwiększanie rozmiaru (auto-growth) i dostępna jest przestrzeń dyskowa, fizyczny plik rośnie. Jeśli dysk jest pełny lub automatyczne zwiększanie jest ograniczone, napotkasz błąd 9002.

Obcinanie dziennika (Truncation) a zmniejszanie dziennika (Shrinking)

Powszechnym błędnym przekonaniem jest to, że obcinanie dziennika zmniejsza fizyczny rozmiar pliku.
* Obcinanie dziennika (Log Truncation): Proces oznaczania aktywnych VLF jako nieaktywnych, udostępniający miejsce do ponownego wykorzystania. Nie zmniejsza to rozmiaru pliku LDF na dysku.
* Zmniejszanie dziennika (Log Shrinking): Proces fizycznego zmniejszania rozmiaru pliku LDF i zwracania miejsca systemowi operacyjnemu.

W modelu odzyskiwania Full (Pełny), obcinanie dziennika następuje tylko po pomyślnym wykonaniu kopii zapasowej dziennika transakcji (przy założeniu, że żadne inne procesy nie utrzymują dziennika jako aktywnego).

Diagnozowanie błędu „Dziennik transakcji pełny” (Błąd 9002)

Gdy dziennik jest pełny, pierwszym krokiem nie powinno być ślepe dodawanie miejsca na dysku lub zmniejszanie plików. Musisz zidentyfikować, dlaczego dziennik nie może zostać obcięty. SQL Server udostępnia wbudowany mechanizm informujący dokładnie, co uniemożliwia ponowne wykorzystanie dziennika za pośrednictwem widoku katalogu sys.databases.

Uruchom następujące polecenie T-SQL, aby zidentyfikować wąskie gardło:

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

Możesz również sprawdzić bieżące wykorzystanie miejsca przez dzienniki transakcji za pomocą:

DBCC SQLPERF(LOGSPACE);

Typowe stany log_reuse_wait_desc

  1. LOG_BACKUP: Baza danych jest w modelu odzyskiwania Full lub Bulk-Logged, a kopia zapasowa dziennika transakcji nie była ostatnio wykonywana. Jest to najczęstsza przyczyna.
  2. ACTIVE_TRANSACTION: Długotrwała transakcja (np. ogromna przebudowa indeksu lub zapomniana niezatwierdzona transakcja) utrzymuje dziennik jako aktywny.
  3. REPLICATION / CDC: Włączona jest replikacja transakcyjna lub Change Data Capture (CDC), a agent czytnika dziennika (Log Reader Agent) nie przetworzył jeszcze transakcji.
  4. AVAILABILITY_REPLICA: W grupie dostępności AlwaysOn, replika pomocnicza jest odłączona lub synchronizuje się zbyt wolno, zmuszając replikę podstawową do przechowywania rekordów dziennika, dopóki nie zostaną one utrwalone na replice pomocniczej.

Strategie szybkiego odzyskiwania: Rozwiązywanie problemu na produkcji

W zależności od zwróconego stanu log_reuse_wait_desc, Twoja reakcja awaryjna będzie inna. Oto strategie szybkiego odzyskiwania dla najczęstszych scenariuszy.

Scenariusz 1: Brakujące lub nieudane kopie zapasowe dziennika (LOG_BACKUP)

Jeśli typem oczekiwania jest LOG_BACKUP, rozwiązanie jest proste: musisz wykonać kopię zapasową dziennika transakcji.

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

Po zakończeniu tworzenia kopii zapasowej nieaktywne VLF zostaną obcięte, a SQL Server wznowi normalne działanie. Jeśli Twój dysk kopii zapasowych jest pełny, może być konieczne wykonanie kopii zapasowej do tymczasowego udziału sieciowego lub urządzenia null (wysoce odradzane, chyba że bazę danych można łatwo odtworzyć, ponieważ przerywa to łańcuch dziennika):

-- OSTRZEŻENIE: To przerywa łańcuch dziennika i narusza odzyskiwanie do punktu w czasie.
-- Używaj tylko w absolutnej konieczności i natychmiast wykonaj pełną kopię zapasową.
BACKUP LOG [YourDatabaseName] TO DISK = 'NUL';

Scenariusz 2: Długotrwałe aktywne transakcje (ACTIVE_TRANSACTION)

Jeśli pojedyncza transakcja trwa godzinami, uniemożliwia ona obcinanie dziennika przez cały ten czas. Najpierw zidentyfikuj problematyczną transakcję:

DBCC OPENTRAN('YourDatabaseName');

To polecenie zwraca najstarszą aktywną transakcję i jej identyfikator procesu serwera (SPID). Możesz uzyskać więcej szczegółów na temat tego, co robi dany SPID, odpytując dynamiczne widoki zarządzania (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>;

Jeśli transakcja jest błędnym zapytaniem lub zawieszonym procesem, może być konieczne jej przerwanie, aby zwolnić dziennik.

KILL <SPID>;

Uwaga: Przerwanie ogromnej transakcji wywoła wycofywanie (rollback), co może zająć znaczną ilość czasu i tymczasowo wygeneruje dodatkową aktywność w dzienniku. Nie restartuj usługi SQL Server podczas wycofywania, w przeciwnym razie baza danych przejdzie w tryb odzyskiwania po restarcie.

Scenariusz 3: Awaryjna alokacja miejsca (Dysk pełny w 100%)

Jeśli plik LDF zajął cały dysk, nie można nawet wykonać kopii zapasowej, ponieważ SQL Server wymaga niewielkiej ilości miejsca w dzienniku, aby zarejestrować samo zdarzenie kopii zapasowej. W tym scenariuszu musisz dodać pomocniczy plik dziennika na innym dysku z dostępnym miejscem.

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

To natychmiast zapewnia SQL Serverowi przestrzeń do działania. Gdy baza danych będzie online, wykonaj kopię zapasową dziennika transakcji, opróżnij pomocniczy plik dziennika i usuń go:

-- 1. Wykonaj kopię zapasową dziennika, aby go obciąć
BACKUP LOG [YourDatabaseName] TO DISK = '...';

-- 2. Opróżnij tymczasowy plik dziennika
DBCC SHRINKFILE (N'YourDatabaseName_Log2', EMPTYFILE);

-- 3. Usuń tymczasowy plik dziennika
ALTER DATABASE [YourDatabaseName] REMOVE FILE [YourDatabaseName_Log2];

Najlepsze praktyki zapobiegania i zarządzania dziennikiem transakcji

Reaktywne rozwiązywanie problemów jest stresujące i wpływa na SLA. Wdrożenie proaktywnych praktyk architektonicznych i operacyjnych jest niezbędne dla stabilności bazy danych w przedsiębiorstwie.

1. Wdrożenie solidnej, zautomatyzowanej strategii kopii zapasowych

Jeśli baza danych jest w modelu odzyskiwania Full, częste kopie zapasowe dziennika transakcji są obowiązkowe. W zależności od Twojego celu punktu odzyskiwania (RPO) i wolumenu transakcji, kopie zapasowe dziennika powinny odbywać się co 5 do 15 minut.

Rozwiązania do tworzenia kopii zapasowych klasy korporacyjnej, takie jak CloudSave, znacznie upraszczają ten proces. Dzięki bezpośredniej integracji z SQL Server za pośrednictwem VDI (Virtual Device Interface), CloudSave pozwala administratorom baz danych konfigurować oparte na politykach, wysokoczęstotliwościowe kopie zapasowe dziennika transakcji. Zapewnia to ciągłe obcinanie dzienników, ich bezpieczne szyfrowanie i przechowywanie poza siedzibą firmy lub w niezmiennej pamięci masowej w chmurze, zapobiegając stanowi oczekiwania LOG_BACKUP bez konieczności tworzenia złożonych, niestandardowych zadań SQL Agent.

2. Odpowiednie dobranie rozmiaru dziennika transakcji i zarządzanie VLF

Poleganie na automatycznym zwiększaniu rozmiaru (auto-growth) w celu zarządzania rozmiarem dziennika transakcji jest niebezpiecznym antywzorcem. Operacje automatycznego zwiększania są kosztowne i wstrzymują przetwarzanie transakcji, podczas gdy dysk jest inicjowany zerami (chyba że włączona jest funkcja Instant File Initialization, która nie dotyczy plików dziennika).

Ponadto częste, małe automatyczne zwiększenia (np. o 10% lub 50 MB na raz) prowadzą do fragmentacji VLF. Dziennik transakcji z tysiącami małych VLF drastycznie obniży czas uruchamiania bazy danych, wydajność kopii zapasowych i opóźnienia replikacji.

  • Wstępne określenie rozmiaru dziennika: Przeanalizuj swoje największe operacje konserwacyjne (takie jak przebudowa indeksów) i wstępnie określ rozmiar pliku LDF tak, aby je pomieścił bez konieczności wzrostu.
  • Ustawienie stałego automatycznego zwiększania: Zmień automatyczne zwiększanie z wartości procentowej na stały rozmiar (np. 1 GB lub 5 GB), aby zapewnić, że VLF są tworzone w odpowiednim rozmiarze.

Możesz sprawdzić liczbę VLF za pomocą następującego zapytania (dla 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'));

Jeśli liczba VLF przekracza 500, rozważ poczekanie na okres mniejszego obciążenia, zmniejszenie dziennika do minimalnego rozmiaru i ręczne zwiększenie go z powrotem do wymaganego rozmiaru w dużych blokach.

3. Optymalizacja operacji konserwacji indeksów

Przebudowy indeksów są operacjami w pełni logowanymi, nawet w modelu odzyskiwania Bulk-Logged (w zależności od typu indeksu). Przebudowa indeksu o rozmiarze 500 GB wygeneruje co najmniej 500 GB rekordów dziennika transakcji.

Aby ograniczyć rozrost dziennika podczas konserwacji:
* Używaj SORT_IN_TEMPDB = ON podczas przebudowy indeksów. Odciąża to fazę sortowania do TempDB, zmniejszając obciążenie dziennika transakcji bazy danych użytkownika.
* Przejdź z przebudowy (rebuild) indeksów na ich reorganizację (reorganize), gdzie to możliwe, ponieważ reorganizacje są bardziej wydajne pod względem logowania i można je przerwać bez wycofywania całej operacji.
* Grupuj duże operacje DELETE lub UPDATE. Zamiast usuwać 10 milionów wierszy w jednej transakcji, usuwaj je w porcjach po 50 000, zatwierdzając transakcje i pozwalając kopiom zapasowym dziennika na obcinanie go pomiędzy partiami.

4. Monitorowanie wysokiej dostępności i topologii replikacji

W grupach dostępności AlwaysOn replika podstawowa nie może obciąć swojego dziennika, dopóki rekordy dziennika nie zostaną utrwalone na wszystkich synchronicznych i asynchronicznych replikach pomocniczych.

Jeśli replika pomocnicza przejdzie w tryb offline lub jeśli przepustowość sieci nie nadąża za tempem generowania transakcji przez replikę podstawową, kolejka wysyłania (send queue) repliki podstawowej wzrośnie, a dziennik się zapełni (typ oczekiwania AVAILABILITY_REPLICA).

Wdróż solidne monitorowanie licznika wydajności SQLServer:Replica > Log Send Queue. Jeśli replika pomocnicza zostanie trwale utracona, musisz usunąć ją z grupy dostępności lub zawiesić przesyłanie danych, aby umożliwić obcięcie dziennika podstawowego.

Podsumowanie

Napotkanie pełnego dziennika transakcji to „chrzest bojowy” dla administratorów baz danych, ale nie musi skutkować długotrwałymi przestojami. Dzięki zrozumieniu mechaniki logowania z wyprzedzeniem (WAL) i VLF, możesz szybko zdiagnozować przyczynę źródłową za pomocą sys.databases i zastosować odpowiednią strategię szybkiego odzyskiwania.

Długoterminowa stabilność opiera się na odejściu od reaktywnych napraw. Wstępne określenie rozmiaru plików dziennika, optymalizacja procedur konserwacyjnych i wykorzystanie platform do tworzenia kopii zapasowych klasy korporacyjnej, takich jak CloudSave, w celu egzekwowania ścisłych, zautomatyzowanych harmonogramów kopii zapasowych dziennika, zapewni, że Twoje dzienniki transakcji pozostaną zdrowe, obcięte i gotowe do obsługi obciążeń produkcyjnych o wysokiej przepustowości.

Kategorie