SQLGEEK

[PL] Chcę oglądać Twoje logi

Temat zmniejszania rozmiaru pliku dziennika transakcji bazy i wszelkich katastrof z tym procesem związanych wraca na forum mojego ulubionego portalu - WSS.pl - jak bumerang. Jedną z metod zmniejszania loga wykorzystywanych namiętnie i bez opamiętania przez użytkowników jest metoda “brute force” – czyli metoda oparta o usunięcie pliku loga i podłączenie tylko pliku danych do instancji SQL Server przy użyciu procedury systemowej sp_attach_single_file_db. Czasem działa…

Upadek mitu

Do czasu… Niekiedy może skończyć się katastrofą zwizualizowaną na przykład takim komunikatem błędu:

File activation failure. The physical file name "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\Test_log.ldf" may be incorrect.
The log cannot be rebuilt because the database was not cleanly shut down.
Msg 1813, Level 16, State 2, Line 1
Could not open new database 'test'. CREATE DATABASE is aborted.

Tak najczęściej weryfikuje się mity – na własnej skórze. Niestety.

Co, jak już się dałem złapać na mit?

Czyli jednak to niekoniecznie jest dobra metoda… Ok, ale co, jeśli już log został usunięty i nie ma szybkiej (lub wręcz żadnej) możliwości przywrócenia go?

Wtedy trzeba wykonać następujące kroki:

1. Stworzyć bazę o nazwie identycznej z nazwą świeżo “zepsutej” bazy, lokalizując pliki w innym katalogu niż pliki bazy “zepsutej” (żeby nie trzeba było kopiować “osieroconego” pliku .mdf bazy, której log nieszczęśliwie skasowaliśmy). Najlepiej od razu ograniczyć dostęp do nowej bazy dla jednego użytkownika. W SSMS można to ustawić podczas tworzenia bazy danych w zakładce Options okna New Database. Można też wykonać polecenie ALTER DATABASE: 
   
   ALTER DATABASE Test SET SINGLE_USER WITH ROLLBACK IMMEDIATE
   
   
   
2. Przenieść bazę w tryb OFFLINE:
   
   ALTER DATABASE Test SET OFFLINE WITH ROLLBACK IMMEDIATE

    

3. Podmienić plik .mdf bazy na plik bazy “zepsutej”.
4. Przenieść bazę w tryb ONLINE: 
   
   ALTER DATABASE Test SET ONLINE
   
   
   
5. Przenieść bazę w tryb EMERGENCY:
   
   ALTER DATABASE Test SET EMERGENCY
   
   Przy tej okazji zapewne SQL Server uraczy nas komunikatami błędów w stylu:
   
   Msg 5173, Level 16, State 1, Line 1
   One or more files do not match the primary file of the database. If you are attempting to attach a database, retry the operation with the correct files.  If this is an existing database, the file may be corrupted and should be restored from a backup.
   Log file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\Test_log.ldf' does not match the primary file.  It may be from a different database or the log may have been rebuilt previously.
   Msg 945, Level 14, State 2, Line 1
   Database 'Test' cannot be opened due to inaccessible files or insufficient memory or disk space.  See the SQL Server errorlog for details.
   Msg 5069, Level 16, State 1, Line 1
   ALTER DATABASE statement failed.
   
   
   
6. Wykonać polecenie DBCC CHECKDB (tu czas trwania i komunikaty zwracane przez SQL Server mogą być różne, ale koniec końców baza powinna być w statusie SINGLE_USER): 
   
   DBCC CHECKDB(Test, REPAIR_ALLOW_DATA_LOSS)
   
   
   
7. Przywrócić dostęp do bazy użytkownikom: 
   
   ALTER DATABASE Test SET MULTI_USER
   

Po całym tym procesie należy też wykonać oczywiście pełny backup bazy.

Wnioski i sugestie

Z podobnych historii płyną liczne wnioski:

1. Metoda na zmniejszenie pliku dziennika transakcji bazy polegająca na usuwaniu tegoż pliku jest niebezpieczna i zdecydowanie niezalecana. Mówiąc po mojemu – za takie rzeczy ucinamy rączki :-)
2. Tą i podobne metody pokazują i czasem polecają (sic!) szczęśliwi ludzie, którzy powinni chyba częściej grywać w totka. Niestety, ponoć także niektórzy MCT należą do tych farciarzy i uczą swoich kursantów takich metod na szkoleniach. A zatem…
3. Nawet, jak coś bierzemy za pewnik, “bo tak powiedział jeden trener”, weryfikujmy zasłyszane “dobrze znane prawdy” empirycznie. Unikniemy rozczarowań i przykrych niespodzianek w najmniej oczekiwanych momentach.
4. Do ewentualnego zmniejszania pliku dziennika transakcji używamy zestawu – backup dziennika transakcji + DBCC SHRINKFILE. A tak w ogóle, nim zmniejszysz plik loga, zadaj sobie pytanie, po co właściwie chcesz to zrobić? :-)
5. Przy bazie danych działającej w trybie FULL (recovery mode) niezbędna jest odpowiednia strategia backupowania loga (inaczej – trzeba go backupować, jeśli nie chcemy mieć problemów – narastającego wiecznie pliku logów czy długiego czasu podnoszenia bazy po restarcie serwera).

Inni mówią i piszą o logu

Bardzo interesująco o logu opowiadali swego czasu na konferencji Microsoft Technology Summit 2006 Marcin Szeliga i Maciej Pilecki (“Strojenie SQL Server 2005”). O złym nawyku usuwania loga wspomniał też wymieniony Maciej Pilecki w swojej sesji o mitach na konferencji Communities to Communities 2008 (“Największe mity na temat SQL Server”). Bardzo dobry artykuł o zasadach funkcjonowania dziennika transakcji i metodach utrzymania go napisał dla Technet Polska Marcin Guzowski (“Log transakcyjny w SQL Server 2005”). Firma Microsoft w swoich artykułach także opisuje, jak radzić sobie z rosnącymi plikami dziennika transakcji (KB873235, KB272318).

Suma sumarum

Na zakończenie chciałbym zaznaczyć, że opisana metoda ratowania się z opresji po usunięciu pliku dziennika transakcji nie jest wynikiem moich własnych przeżyć. Po prostu przeprowadziłem w ramach testów małą symulację i okazało się, że opisana wyżej procedura pozwoliła mi przywrócić bazę danych online. Ze swojej strony nie gwarantuję, że procedura ta pomoże każdemu. Ale – z drugiej strony - po przeczytaniu niniejszej notki, powinieneś już wiedzieć, jak uniknąć podobnych sytuacji.

Mam nadzieję, że opisany problem w przyszłości będzie pojawiał się coraz rzadziej i ludzie zaczną jednak stosować najlepsze praktyki w odniesieniu do dziennika transakcji. I nie będzie nam już dane urządzać konkursu pt. “Kto ma największego loga?”… :-)

I jeszcze jedno. Tytuł notki to oczywiście parafraza tytułu znanej piosenki znanego zespołu Czarno-Czarni ;-) Zasugerował mi go Marek Adamczuk. Szczypta ironii wobec poruszonego wyżej tematu pasuje jak ulał.

Jeśli masz jakieś komentarze lub może nie zgadzasz się z tym, co napisałem, komentuj tę notkę. Przecież może właśnie podjąłem próbę stworzenia kolejnego mitu na temat SQL Server?! ;-)