Log transakcji, który rośnie: model odzyskiwania SIMPLE i FULL

Na serwerze srv-sql-01 kończy się miejsce na dysku z plikami logów. Baza CrmDB w instancji CRM ma plik danych o rozmiarze 5 GB i plik logu CrmDB_log.ldf o rozmiarze 80 GB. Aplikacja nagle przestaje zapisywać zmiany, a w dzienniku SQL Servera pojawia się błąd 9002 z komunikatem The transaction log for database 'CrmDB' is full due to 'LOG_BACKUP'. Zapytania odczytujące dane nadal działają, ale żadna zmiana nie przejdzie, dopóki w logu nie zwolni się miejsce.

Taki plik logu prawie nigdy nie jest skutkiem awarii, tylko modelu odzyskiwania FULL bez kopii logu: SQL Server przechowuje każdą zmianę, dopóki nie trafi ona do kopii logu. Ten wpis wyjaśnia, jak działa log, jak odczytać przyczynę z kolumny log_reuse_wait_desc i jak przywrócić plik do rozsądnego rozmiaru bez utraty danych.

Jak działa log transakcji

Każda zmiana w bazie trafia najpierw do logu transakcji, a dopiero później do pliku danych. Log to zwykle jeden plik .ldf, który SQL Server dzieli wewnętrznie na wirtualne pliki logu (VLF, virtual log files). Ich liczby ani rozmiaru nie ustawia się ręcznie: powstają przy tworzeniu pliku i przy każdym jego przyroście.

Nowe rekordy są dopisywane na końcu logu. Fragment od najstarszego rekordu, który jest jeszcze potrzebny do odtworzenia bazy po awarii (MinLSN), do ostatniego zapisanego rekordu to aktywna część logu. Jej początek wyznacza między innymi najstarsza otwarta transakcja. VLF leżące w całości przed MinLSN są nieaktywne, ale do ponownego zapisu nadają się dopiero po obcięciu logu (log truncation). Gdy zapis dojdzie do końca pliku, wraca na jego początek, do zwolnionych VLF. Jeśli żaden VLF nie jest wolny, SQL Server powiększa plik o wartość FILEGROWTH. Gdy nie może tego zrobić, bo brakuje miejsca na dysku albo przyrost jest wyłączony, zgłasza błąd 9002.

SIMPLE a FULL

Model odzyskiwania (recovery model) decyduje, co obcina log i do jakiego momentu można odtworzyć bazę.

W modelu SIMPLE log jest obcinany automatycznie po punkcie kontrolnym (checkpoint). Kopii logu nie da się wykonać, więc bazę można odtworzyć tylko do chwili ostatniej kopii pełnej lub różnicowej. Przy kopii pełnej wykonywanej co noc awaria o 16:00 oznacza utratę zmian z całego dnia pracy.

W modelu FULL log jest obcinany dopiero po kopii logu (BACKUP LOG), o ile od poprzedniej kopii wystąpił punkt kontrolny. Kopia pełna logu nie obcina. W zamian nieprzerwany ciąg kopii logu pozwala odtworzyć bazę do wybranej chwili, na przykład do minuty przed omyłkowym DELETE bez klauzuli WHERE, a strata ogranicza się do zmian od ostatniej kopii logu.

Trzeci model, BULK_LOGGED, to odmiana FULL z minimalnym logowaniem operacji masowych, która również wymaga kopii logu, ale dla kopii logu zawierających operacje masowe nie pozwala odtworzyć bazy do punktu w czasie.

Cecha	SIMPLE	FULL
Co obcina log	punkt kontrolny	kopia logu
Kopie logu	niemożliwe	wymagane
Możliwa utrata danych	zmiany od ostatniej kopii pełnej lub różnicowej	zmiany od ostatniej kopii logu
Odtworzenie do punktu w czasie	nie	tak, RESTORE LOG … WITH STOPAT
Log shipping, grupy dostępności	nie	tak
Domyślny w edycji	Express	Standard i Enterprise

Wybór modelu to decyzja o tym, ile pracy firma może stracić, a nie ustawienie techniczne. Baza konfiguracyjna aplikacji, która zmienia się raz w tygodniu, może działać w SIMPLE z kopią pełną co noc. Baza CrmDB, w której przez cały dzień przybywa zamówień, zwykle potrzebuje FULL i kopii logu co 15–30 minut.

Dlaczego baza w FULL rośnie bez końca

W edycjach Standard i Enterprise baza systemowa model ma model FULL, a każda nowa baza dziedziczy model odzyskiwania właśnie z niej. Zaraz po utworzeniu baza w FULL zachowuje się jak w SIMPLE: dopóki nie powstanie pierwsza kopia pełna, log jest używany ponownie bez kopii logu. Potem administrator konfiguruje nocną kopię pełną i od tej chwili zaczyna się łańcuch kopii logu. Kopii logu nikt nie zaplanował, więc żaden VLF nie jest już zwalniany, a plik rośnie o każdy dzień pracy, każdą nocną przebudowę indeksów i każdy import. Kopie pełne się udają, raporty z zadań kopii są poprawne, a dysk zapełnia się przez kilka miesięcy.

Rozwiązania są dwa: zaplanować kopie logu albo świadomie zmienić model na SIMPLE. Jak wykonywać kopie logu i uruchamiać je według harmonogramu bez SQL Agenta, opisuje szósta część przewodnika.

log_reuse_wait_desc: na co czeka log

Kolumna log_reuse_wait_desc w widoku sys.databases pokazuje, co blokowało ponowne użycie logu w chwili ostatniego punktu kontrolnego, a ta sama wartość pojawia się w komunikacie błędu 9002 po słowach full due to.

Wartość log_reuse_wait_desc	Przyczyna	Co zrobić
NOTHING	w logu są wolne VLF	nic; zbyt duży plik można jednorazowo zmniejszyć
CHECKPOINT	od ostatniego obcięcia nie było punktu kontrolnego	zwykle mija samo; w razie potrzeby CHECKPOINT w kontekście bazy
LOG_BACKUP	baza w FULL lub BULK_LOGGED czeka na kopię logu	kopia logu i stały harmonogram albo świadome przejście na SIMPLE
ACTIVE_BACKUP_OR_RESTORE	trwa kopia danych albo odtwarzanie	poczekać na koniec operacji; sprawdzić, czy kopia nie zawisła
ACTIVE_TRANSACTION	długa lub porzucona transakcja, w każdym modelu	znaleźć ją przez DBCC OPENTRAN, zatwierdzić albo wycofać, w ostateczności KILL
REPLICATION	replikacja transakcyjna albo CDC nie odczytały jeszcze zmian	sprawdzić, czy agent Log Reader lub zadania CDC działają
AVAILABILITY_REPLICA	replika pomocnicza grupy dostępności nie zapisała albo nie zastosowała logu	sprawdzić kolejki wysyłania i odtwarzania na replikach

W bazie z regularnymi kopiami logu wartość LOG_BACKUP jest normalna i oznacza tylko, że od ostatniej kopii przybyły nowe rekordy. Problemem jest dopiero brak kopii logu albo odstępy dłuższe niż w planie.

Co zrobić od razu

Jeśli baza ma zostać w modelu FULL, pierwszym krokiem jest kopia logu. Kopia zajmuje mniej więcej tyle, ile zajęta część logu, więc dysk docelowy musi pomieścić nawet kilkadziesiąt gigabajtów. Gdy log nigdy wcześniej nie był kopiowany, pierwsza kopia może trwać długo, a zwolnienie końca pliku przed jego zmniejszeniem czasem wymaga kolejnej kopii logu.

-- Kopia logu obcina log; plik .trn staje się częścią łańcucha kopii
BACKUP LOG [CrmDB] TO DISK = N'D:\SQLBackup\CRM\CrmDB\CrmDB_20261018_101500_LOG.trn' WITH CHECKSUM;

Jeśli firma akceptuje utratę zmian od ostatniej kopii pełnej, można świadomie przejść na SIMPLE. Przed przełączeniem warto wykonać ostatnią kopię logu, o ile jest na nią miejsce. Zmiana przerywa łańcuch kopii logu: odtworzenie do punktu w czasie przestaje być możliwe, a zaplanowane kopie logu tej bazy trzeba wyłączyć.

USE [master];
ALTER DATABASE [CrmDB] SET RECOVERY SIMPLE;
GO
USE [CrmDB];
-- W modelu SIMPLE punkt kontrolny obcina log
CHECKPOINT;

Gdy log_reuse_wait_desc pokazuje NOTHING, plik można zmniejszyć. Docelowy rozmiar to nie zero, tylko tyle, ile log potrzebuje w najbardziej obciążonym okresie między kopiami logu, na przykład podczas nocnej przebudowy indeksów albo największego importu. Dla bazy z przykładu przyjmijmy 4 GB. Przy porządkowaniu logu Microsoft zaleca najpierw go zmniejszyć, a potem powiększyć do docelowego rozmiaru jednym krokiem, co przy okazji daje rozsądną liczbę VLF.

USE [CrmDB];
-- Rozmiar docelowy w MB; log zmniejsza się tylko do granicy VLF
DBCC SHRINKFILE (N'CrmDB_log', 1024);
-- Powiększenie do docelowego rozmiaru jednym krokiem
ALTER DATABASE [CrmDB] MODIFY FILE (NAME = N'CrmDB_log', SIZE = 4096MB);

Jeśli DBCC SHRINKFILE nie zejdzie do zadanego rozmiaru, aktywna część logu leży na końcu pliku. Po kolejnej kopii logu (w SIMPLE po punkcie kontrolnym) polecenie trzeba powtórzyć.

Zmniejszanie logu ma być jednorazowe. Zadanie nocne z DBCC SHRINKFILE albo włączona opcja AUTO_SHRINK to błędne koło: plik i tak urośnie przy następnym obciążeniu, każdy przyrost spowalnia zapisy, a VLF przybywa. Microsoft wprost odradza traktowanie zmniejszania plików jako rutynowej konserwacji.

Przyrost w megabajtach, nie w procentach

Przyrost procentowy rośnie razem z plikiem: przy 10% plik o rozmiarze 80 GB powiększa się jednorazowo o 8 GB. W wersjach starszych niż SQL Server 2022 (16.x) nowa przestrzeń logu zawsze jest zerowana, a zapisy w bazie czekają, aż to się skończy. Od SQL Server 2022 przyrosty logu do 64 MB korzystają z natychmiastowej inicjalizacji plików (instant file initialization), większe nadal wymagają zerowania.

Od wielkości przyrostu zależy też liczba VLF: przyrost mniejszy niż 1/8 rozmiaru pliku tworzy jeden VLF, większy 4 VLF poniżej 64 MB (od SQL Server 2022 jeden VLF do 64 MB włącznie), 8 VLF do 1 GB i 16 VLF powyżej. Wiele drobnych przyrostów daje bardzo dużo VLF, co wydłuża uruchamianie bazy, odtwarzanie i kopie logu. Microsoft zaleca utrzymywać ich liczbę najwyżej na poziomie kilku tysięcy.

Rozsądne wartości to stały przyrost logu w megabajtach: 64 MB dla małych logów, 256–512 MB dla logów kilkugigabajtowych i nie więcej niż 1024 MB, co jest górną granicą zalecaną przez Microsoft. Od SQL Server 2016 (13.x) domyślny przyrost nowych plików to 64 MB. Wartości 10% i 1 MB zostają w bazach utworzonych w starszych wersjach, bo ustawienia plików przechodzą razem z bazą przy migracji przez kopię i odtworzenie.

-- Przyrost w MB zamiast procentów, osobno dla logu i danych
ALTER DATABASE [CrmDB] MODIFY FILE (NAME = N'CrmDB_log', FILEGROWTH = 512MB);
ALTER DATABASE [CrmDB] MODIFY FILE (NAME = N'CrmDB', FILEGROWTH = 256MB);

Baza model jako wzorzec nowych baz

Każda nowa baza dziedziczy z bazy model opcje, w tym model odzyskiwania, oraz rozmiar początkowy pliku danych. Ustawienie model jest więc decyzją dla wszystkich przyszłych baz instancji. Jeśli instancja nie ma kopii logu, model powinien mieć SIMPLE. Jeśli kopie logu automatycznie obejmują każdą bazę w FULL, ustawienie FULL jest bezpieczne.

-- Dotyczy baz tworzonych po zmianie; istniejące zostają bez zmian
ALTER DATABASE [model] SET RECOVERY SIMPLE;

Bazy odtwarzane z kopii i dołączane do instancji zachowują własny model odzyskiwania, więc po migracji trzeba go sprawdzić. Rozmiar i przyrost plików najlepiej podawać jawnie w skrypcie tworzenia bazy. Edycja Express domyślnie używa modelu SIMPLE; kiedy ta edycja wystarcza, opisuje siódma część przewodnika.

Sprawdź u siebie

Model odzyskiwania i przyczyna oczekiwania logu dla każdej bazy instancji. Dobry wynik to NOTHING lub CHECKPOINT oraz LOG_BACKUP wyłącznie w bazach z regularnymi kopiami logu. Zły wynik to LOG_BACKUP w bazie bez kopii logu (sprawdza to trzeci blok) albo ACTIVE_TRANSACTION utrzymujące się godzinami.

SELECT name, recovery_model_desc, log_reuse_wait_desc
FROM sys.databases
ORDER BY recovery_model_desc, name;

Zajętość logu wszystkich baz i szczegóły jednej bazy. Plik 80 GB zajęty w kilku procentach to log, który kiedyś urósł i można go jednorazowo zmniejszyć. Zajętość powyżej 90% przy LOG_BACKUP oznacza, że błąd 9002 pojawi się, gdy tylko zabraknie miejsca na kolejny przyrost. Kolumna since_last_backup_mb pokazuje, ile logu przybyło od ostatniej kopii logu.

-- Wszystkie bazy: rozmiar logu w MB i procent zajętości
DBCC SQLPERF (LOGSPACE);

-- Bieżąca baza: rozmiar, zajętość i ilość logu od ostatniej kopii logu
USE [CrmDB];
SELECT total_log_size_in_bytes / 1048576 AS log_size_mb,
       used_log_space_in_bytes / 1048576 AS used_mb,
       used_log_space_in_percent,
       log_space_in_bytes_since_last_backup / 1048576 AS since_last_backup_mb
FROM sys.dm_db_log_space_usage;

Ostatnia kopia pełna i ostatnia kopia logu każdej bazy zestawione z modelem odzyskiwania. Zły wynik to baza w FULL z datą w last_full i pustym albo starszym niż planowany odstęp last_log, czyli dokładnie scenariusz z początku wpisu. Dla baz w SIMPLE puste last_log jest prawidłowe.

SELECT d.name, d.recovery_model_desc,
       MAX(CASE WHEN b.type = 'D' THEN b.backup_finish_date END) AS last_full,
       MAX(CASE WHEN b.type = 'L' THEN b.backup_finish_date END) AS last_log
FROM sys.databases AS d
LEFT JOIN msdb.dbo.backupset AS b ON b.database_name = d.name
WHERE d.name <> 'tempdb'
GROUP BY d.name, d.recovery_model_desc
ORDER BY d.recovery_model_desc, d.name;

Przyrost plików i liczba VLF (funkcja sys.dm_db_log_info jest dostępna od SQL Server 2016 SP2). Dobry wynik to przyrost w MB, dla logu najwyżej 1024 MB, i liczba VLF rzędu kilkudziesięciu lub kilkuset. Zły wynik to przyrost w procentach, wyłączony przyrost (przy zapełnieniu od razu błąd 9002) albo kilka tysięcy VLF i więcej, czyli log, który rósł drobnymi krokami.

-- growth jest w stronach 8 KB, gdy is_percent_growth = 0
SELECT DB_NAME(database_id) AS database_name, name, type_desc,
       size / 128 AS size_mb,
       CASE WHEN growth = 0 THEN 'brak przyrostu'
            WHEN is_percent_growth = 1 THEN CONCAT(growth, ' %')
            ELSE CONCAT(CAST(growth / 128.0 AS decimal(10, 2)), ' MB') END AS growth_setting
FROM sys.master_files
ORDER BY database_name, type_desc;

-- Liczba VLF w logu każdej dostępnej bazy
SELECT d.name, COUNT(*) AS vlf_count
FROM sys.databases AS d
CROSS APPLY sys.dm_db_log_info(d.database_id) AS l
WHERE d.state_desc = 'ONLINE'
GROUP BY d.name
ORDER BY vlf_count DESC;