Error 9002 – pełny log transakcji

Error 9002, opisany jako pełny log transakcji, to alternatywne, potoczne określenie tego samego braku miejsca w dzienniku.

Error 9002 w SQL Server i znaczenie pełnego dziennika transakcji

Error 9002 w Microsoft SQL Server oznacza, że dziennik transakcji bazy danych nie ma wystarczającej ilości wolnego miejsca, aby zapisywać kolejne operacje. Określenie „pełny log transakcji” jest opisem sytuacji, a nie nazwą osobnego mechanizmu. Dziennik transakcji przechowuje informacje potrzebne do odtwarzania zmian, wycofywania niezatwierdzonych operacji oraz utrzymywania spójności bazy po awarii. Gdy SQL Server nie może dopisać kolejnych rekordów do logu, operacje modyfikujące dane mogą zostać zatrzymane, a aplikacja korzystająca z bazy zaczyna zwracać błędy.

Rozmiar pliku dziennika nie jest jedynym czynnikiem decydującym o wystąpieniu błędu. SQL Server może mieć plik logu o dużym rozmiarze, ale jego przestrzeń może być zajęta przez aktywną transakcję, oczekiwany punkt kontroli, replikację, grupę dostępności albo brak kopii zapasowych logu w modelu odzyskiwania Full lub Bulk-logged. Informację o przyczynie oczekiwania można sprawdzić w widoku sys.databases, w kolumnie log_reuse_wait_desc. Wartość tej kolumny wskazuje, dlaczego obszar dziennika nie może zostać ponownie wykorzystany.

Jak SQL Server wykorzystuje plik dziennika transakcji

Każda zmiana wymagająca trwałego zapisu jest najpierw rejestrowana w dzienniku transakcji. Dotyczy to między innymi wstawiania, modyfikowania i usuwania wierszy, zmian struktury oraz operacji wykonywanych w ramach jawnych lub wewnętrznych transakcji. SQL Server stosuje zasadę write-ahead logging: odpowiednie wpisy logu muszą zostać zapisane przed uznaniem zmodyfikowanych stron danych za trwale zapisane na dysku. Dzięki temu po restarcie silnik może przeprowadzić fazę redo i undo.

Plik dziennika może być podzielony na fragmenty zwane plikami wirtualnymi logu, czyli VLF. Przy powiększaniu pliku SQL Server tworzy kolejne VLF według reguł zależnych od wersji silnika i wielkości wzrostu. Wielokrotne, niewielkie autowzrosty mogą doprowadzić do nadmiernej liczby VLF, natomiast zbyt mały plik początkowy i brak planowania wzrostu zwiększają ryzyko częstego powiększania. Autogrowth nie usuwa przyczyny blokującej ponowne wykorzystanie logu; jedynie powiększa plik, o ile na woluminie jest dostępne miejsce.

Stan wykorzystania można analizować między innymi przez DBCC SQLPERF(LOGSPACE) albo widok sys.dm_db_log_space_usage. Należy rozróżnić procent zajętości logu od fizycznego miejsca na dysku. Dziennik zajęty w 80 procentach może być bezpieczny, jeśli pozostała przestrzeń wystarcza dla bieżącej operacji, natomiast log zajęty w 20 procentach może nadal powodować problem, gdy plik nie może się powiększyć z powodu braku miejsca, limitu rozmiaru lub uprawnień systemu plików.

Najczęstsze przyczyny oraz bezpieczne działania diagnostyczne

Jedną z typowych przyczyn Error 9002 w modelu odzyskiwania Full jest brak regularnych kopii zapasowych dziennika transakcji. Po wykonaniu pełnej kopii zapasowej bazy log nie jest automatycznie opróżniany. W modelu Full przestrzeń logu może zostać ponownie wykorzystana dopiero wtedy, gdy wymagane wpisy zostaną objęte poprawną kopią zapasową logu i nie istnieje inna blokada. Polecenie BACKUP LOG wymaga właściwie skonfigurowanego łańcucha kopii oraz dostępnego miejsca docelowego.

Innym powodem może być długo trwająca transakcja. Jeżeli transakcja pozostaje otwarta, SQL Server musi zachować jej wpisy, ponieważ mogą być potrzebne do wycofania zmian. W takiej sytuacji diagnostyka powinna obejmować sys.dm_exec_requests, sys.dm_tran_active_transactions i sys.dm_tran_database_transactions, a także identyfikację sesji blokujących. Samo zakończenie procesu klienta bez zrozumienia skutków może uruchomić długie wycofywanie zmian i czasowo zwiększyć obciążenie dziennika.

Warto również sprawdzić, czy problem nie wynika z mechanizmów wysokiej dostępności lub replikacji. Oczekiwanie log_reuse_wait_desc może wskazywać na przesyłanie lub zastosowanie wpisów w innym miejscu, a także na opóźnienie związane z bazą pomocniczą. Usunięcie pliku logu, ręczne kasowanie pliku .ldf albo używanie DBCC SHRINKFILE jako stałej metody zarządzania rozmiarem nie jest prawidłowym rozwiązaniem. Zmniejszanie pliku może być uzasadnione po jednorazowym, nietypowym wzroście, lecz późniejszy wzrost logu może ponownie wystąpić i pogorszyć jego strukturę.

Przywrócenie działania po zapełnieniu logu

Postępowanie powinno rozpocząć się od ustalenia modelu odzyskiwania, wartości log_reuse_wait_desc, dostępnego miejsca na woluminie oraz rozmiaru i ustawień pliku logu. Jeżeli przyczyną jest brak kopii zapasowych logu, należy najpierw zapewnić poprawny cel kopii i wykonać kopię zgodnie z przyjętą strategią odzyskiwania. Jeżeli blokadą jest aktywna transakcja, trzeba ustalić, czy może zostać bezpiecznie zatwierdzona, anulowana lub zakończona przez właściwy proces. Decyzja zależy od charakteru operacji i wartości danych, dlatego nie powinna sprowadzać się do mechanicznego restartu usługi.

Gdy log nie może się powiększyć z powodu braku miejsca, konieczne jest zwolnienie odpowiedniej przestrzeni na właściwym woluminie albo przeniesienie pliku do lokalizacji zaplanowanej dla tej bazy. Rozmiar pliku należy ustawiać z użyciem kontrolowanego wzrostu, najlepiej w megabajtach lub gigabajtach, zamiast pozostawiać przypadkowe, bardzo małe przyrosty procentowe. Po usunięciu przyczyny można ocenić, czy powiększony plik powinien pozostać w obecnym rozmiarze. Stały rozmiar dopasowany do normalnego obciążenia ogranicza liczbę operacji autowzrostu.

Jeżeli Error 9002 pojawia się ponownie, sam jednorazowy backup lub chwilowe powiększenie pliku nie rozwiązuje problemu. Potrzebna jest korelacja harmonogramu kopii logu z obciążeniem aplikacji, analizą najdłuższych transakcji, miejscem na dysku i wymaganym czasem odtworzenia. W środowisku użytkownika końcowego diagnostyka laptopa lub komputera PC może obejmować sprawdzenie aplikacji bazodanowej, lokalnego magazynu danych, usług SQL Server oraz logów systemowych. Naprawa powinna zachować możliwość odtworzenia danych i nie może polegać na usuwaniu dziennika bez prawidłowej procedury odzyskiwania.