Witaj na Zine.net online Zaloguj się | Rejestracja | Pomoc

[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?! ;-)

Opublikowane 15 lipca 2008 21:10 przez brejk

Komentarze:

# re: [PL] Chcę oglądać Twoje logi

15 lipca 2008 22:59 by Mariusz Kędziora

A to czasem nie dopowiedzial dzis Sebastian z tym "chce ogladac twoje nogi" :) Chociaz faktycznie chyba zaczal szukac tego Marek :)

# re: [PL] Chcę oglądać Twoje logi

15 lipca 2008 23:17 by brejk

Nie wiem, Mariusz. Zbyt byłem zajęty labami z MOSSa, żeby dobrze zapamiętać :D

# re: [PL] Chcę oglądać Twoje logi

15 lipca 2008 23:55 by Wojciech Gebczyk

Pawel,

Ja z powodzedniem stosuje usuwanie ldf'a i na tysiace uruchomien aplikacji dziala bezblednie.

Fakt ze po etapie utworzenia bazy danych prawie nic nie zmieniam w niej oraz neii korzystam z transakcji - nie mam takich potrzeb.

Powodem takiego usuwania jest to ze czasami po utworzeniu bazy danych, mam pare setek MB smieci ldfowych i lepiej dla uzytkownika jak nie ma zbednych paru GB na dysku.

Wogole szukam opcji na zupelne usuniecie tego loga jako ze nie potrzebuje transakcji. Jak ktos zna taki sposob to z checia chcialbym poznac go!

Pozdrawiam,

Wojciech Gebczyk

# re: [PL] Chcę oglądać Twoje logi

16 lipca 2008 02:31 by dario-g

A ja od wieków stosuję na developerskiej bazie shrinka. Trwa to trochę, ale przynajmniej bezpiecznie i nie tracę bazki.

Na produkcji nie shrinkuje się, bo jeśli log tak się powiększył to znaczy, że istnieje duże prawdopodobieństwo, że po zmniejszeniu znów będzie chciał/musiał tak urosnąć i system straci tylko czas i zasoby na powiększanie pliku.

Innymi słowy (z doświadczenia) zmniejszanie nie jest rozwiązaniem problemów (nawet nie wiem jakich problemów). Przecież dyski są tanie i coraz tańsze. :)

# re: [PL] Chcę oglądać Twoje logi

16 lipca 2008 06:37 by brejk

Wojtek, jeśli log jest Ci niepotrzebny, to przestaw bazę danych w SIMPLE recovery mode (albo poleceniem:

ALTER DATABASE Test SET RECOVERY SIMPLE

albo w SSMS w opcjach bazy). Wówczas log będzie rósł znacznie wolniej. Natomiast w bazach, w których pojawia się jakakolwiek transakcyjność i które muszą reprezentować jakąś sensowną dostępność dla użytkowników, Twoja metoda jest niedopuszczalna.

# [PL] Mój log jest za duży

25 lipca 2008 09:03 by SQLGEEK

Niedawno pisałem o tym, jak poradzić sobie z problemami, jakie mogą wyniknąć ze stosowania metody zmniejszania

# re: [PL] Chcę oglądać Twoje logi

5 sierpnia 2008 02:09 by damian_widera

serio MCT polecają BRUTE FORCE?????? To nie moze byc prawda! I ktos na serwerze produkcyjnym tak robi?

U mnie raz zrobiła tak Pani Deweloperka i niestety miała pecha ;). Miało to odbre strony, miałem obiadek gratis :)

# re: [PL] Chcę oglądać Twoje logi

14 maja 2009 17:18 by venzel@wp.pl

Mam awarię na SQL2000 - nie działa Twój sposob!

# re: [PL] Chcę oglądać Twoje logi

15 czerwca 2009 09:16 by brejk

@venzel: Niestety, wyraźnie zaznaczałem, że niekoniecznie zawsze będzie działał :-)

# re: [PL] Chcę oglądać Twoje logi

7 grudnia 2011 23:47 by dela

Człowieku jesteś wspaniały.

Dziękuję.

Komentarze anonimowe wyłączone

About brejk

MVP, MCT, SQL Server geek