Error 9002 – pełny dziennik transakcji

Error 9002 sygnalizuje brak wolnego miejsca w dzienniku transakcji, uniemożliwiający dalsze zapisywanie operacji.

Co oznacza komunikat SQL Server 9002

Błąd 9002 w Microsoft SQL Server oznacza, że dziennik transakcji bazy danych osiągnął rozmiar, przy którym nie może zapisać kolejnych informacji o operacjach. Dziennik transakcji, czyli transaction log, rejestruje między innymi rozpoczęcie i zatwierdzenie transakcji, modyfikacje stron danych oraz informacje potrzebne do wycofania lub odtworzenia zmian. Jest używany zarówno podczas normalnej pracy, jak i w procedurach odzyskiwania po awarii. Gdy w pliku dziennika nie ma dostępnego miejsca, operacje wymagające kolejnych wpisów mogą zostać zatrzymane.

Sam komunikat nie wskazuje jeszcze, dlaczego dziennik się zapełnił. Przyczyną może być na przykład długo trwająca transakcja, brak wykonywanych kopii zapasowych dziennika w modelu odzyskiwania full lub bulk-logged, nieudana replikacja, opóźniona synchronizacja mechanizmu Always On albo zbyt mały plik logu. W modelu simple SQL Server może okresowo zwalniać nieużywane fragmenty dziennika po zakończeniu punktu kontrolnego, ale nie oznacza to automatycznego zmniejszania fizycznego rozmiaru pliku na dysku.

Dziennik transakcji a model odzyskiwania bazy

Rozpoznanie modelu odzyskiwania jest jednym z pierwszych elementów analizy. W modelu simple ponowne wykorzystanie części dziennika zależy przede wszystkim od punktów kontrolnych i aktywnych transakcji. W modelach full i bulk-logged dziennik nie jest regularnie oznaczany jako możliwy do ponownego użycia wyłącznie przez checkpoint. Potrzebna jest kopia zapasowa dziennika, która utrwala jego zawartość i pozwala SQL Serverowi zwolnić starsze, już niepotrzebne fragmenty, o ile nie są one nadal wymagane.

Właściwość log_reuse_wait_desc w widoku sys.databases podaje powód, dla którego przestrzeń dziennika nie może zostać ponownie wykorzystana. Wartości takie jak ACTIVE_TRANSACTION, LOG_BACKUP, AVAILABILITY_REPLICA, REPLICATION lub DATABASE_MIRRORING wskazują różne mechanizmy blokujące obcinanie logu. Nie należy zmieniać modelu odzyskiwania ani wykonywać przypadkowego DBCC SHRINKFILE przed ustaleniem tej przyczyny. Zmiana modelu może zaburzyć założony łańcuch kopii zapasowych, a zmniejszanie pliku usuwa tylko nieużywaną przestrzeń na końcu pliku i nie rozwiązuje problemu, który nadal zajmuje aktywną część dziennika.

Ustalenie miejsca i przyczyny zapełnienia logu

Analiza powinna objąć zarówno rozmiar pliku dziennika, jak i faktyczne wykorzystanie przydzielonego miejsca. Informacje o plikach bazy można pobrać z sys.database_files oraz sys.master_files, natomiast widok sys.dm_db_log_space_usage pozwala sprawdzić wykorzystanie przestrzeni dziennika w bieżącej bazie. Do ustalenia najstarszej aktywnej transakcji służy między innymi DBCC OPENTRAN. W nowszych wersjach SQL Server przydatne są również widoki i funkcje dynamiczne dotyczące transakcji, sesji oraz zapytań, ponieważ pozwalają powiązać długotrwałą transakcję z konkretnym połączeniem lub operacją.

Jeżeli powodem jest ACTIVE_TRANSACTION, trzeba sprawdzić, czy aplikacja nie pozostawiła otwartej transakcji, czy długie polecenie modyfikujące dane nadal działa oraz czy nie wystąpiło oczekiwanie na blokadę. Przyczyna LOG_BACKUP zwykle oznacza brak regularnego łańcucha kopii zapasowych dziennika albo problem z wykonywaniem zadania backupu. Gdy problem dotyczy repliki dostępności, replikacji lub dublowania bazy, samo zwiększenie pliku może tylko odsunąć awarię. Należy ocenić stan odpowiedniego mechanizmu, opóźnienie przetwarzania i dostępność miejsca po stronie, która nie nadąża z odbieraniem lub utrwalaniem zmian.

Ważne jest również rozróżnienie między brakiem miejsca w systemie plików a osiągnięciem maksymalnego rozmiaru pliku logu. Plik może mieć możliwość automatycznego wzrostu, ale wzrost zostanie zablokowany, gdy wolumin nie ma wolnej przestrzeni, ustawiono ograniczenie MAXSIZE albo bieżąca operacja wymaga więcej miejsca, niż można przydzielić. Automatyczny wzrost nie powinien zastępować planowania rozmiaru pliku. Częste, małe przyrosty powodują dodatkowe operacje inicjalizacji i mogą prowadzić do niekorzystnego układu wirtualnych plików dziennika, czyli VLF.

Bezpieczne przywrócenie możliwości zapisu

Postępowanie zależy od ustalonego log_reuse_wait_desc. Gdy problemem jest brak kopii zapasowej dziennika, należy przywrócić poprawne zadanie backupu i zweryfikować jego wynik, lokalizację oraz wolne miejsce. W przypadku aktywnej transakcji trzeba doprowadzić ją do zatwierdzenia albo wycofania, jeżeli pozwala na to stan aplikacji i procedura operacyjna. Przy długiej operacji masowej często bezpieczniejsze jest kontrolowane zakończenie procesu niż wielokrotne powiększanie pliku bez usunięcia przyczyny.

Jeśli baza nie ma już dostępnej przestrzeni, można po ocenie ryzyka powiększyć istniejący plik dziennika lub dodać plik logu na innym woluminie. Rozmiar i przyrost powinny być dobrane do obserwowanego obciążenia, największych transakcji oraz wymagań odzyskiwania. Po usunięciu przyczyny ewentualne zmniejszenie pliku należy traktować jako czynność wyjątkową, a nie standardową naprawę. Zbyt mały log będzie ponownie rósł, co może powodować cykliczne błędy 9002. Diagnostyka powinna zakończyć się sprawdzeniem kopii zapasowej, stanu bazy, miejsca na woluminach, czasu trwania transakcji i monitoringu wykorzystania dziennika.

W środowisku, w którym błąd powtarza się podczas pracy programu, trzeba prześledzić pełny przepływ operacji: otwarcie połączenia, rozpoczęcie transakcji, wykonywane modyfikacje, obsługę wyjątków oraz zatwierdzenie lub wycofanie zmian. Nieobsłużony wyjątek, brak instrukcji rollback albo połączenie pozostawione w stanie transakcji może powodować narastanie logu niezależnie od poprawnie działających kopii zapasowych. Dopiero po połączeniu obserwacji SQL Servera z zachowaniem aplikacji można ustalić, czy źródłem problemu jest konfiguracja bazy, zadanie administracyjne, infrastruktura, czy logika programu.