Log transakcyjny w SQL Server 2005, cz. II

Co administratorzy i deweloperzy wiedzieć powinni

Przeczytaj także: • SQL Server 2005 – usługa SQL Server Browser • SQL Server 2005 Service Pack 2 przedstawia – Logon Trigger

Zawartość strony

	Kontrola i redukcja ilości wirtualnych plików logu (VLF)
	Kontrola i zmniejszanie rozmiaru pliku dziennika
	Nośnik dyskowy dla pliku dziennika transakcji
	Modele odtwarzania baz danych (recovery models)
	Wybór właściwego modelu odtwarzania
	Podsumowanie
	Przeczytaj pozostałe części tego artykułu

Kontrola i redukcja ilości wirtualnych plików logu (VLF)

Co do zasady uznaje się, że ilość wirtualnych plików dziennika nie powinna przekraczać liczby 50. Do sprawdzania ilości VLFów służy polecenie DBCC LOGINFO. Zasada jest bardzo prosta – liczba wierszy zwrócona przez tę komendę dokładnie odpowiada ilości VLFów. Warto też zapamiętać, że DBCC LOGINFO zwraca również status każdego wirtualnego pliku dziennika (kolumna Status). Wartość 0 oznacza, że dany VLF jest nieaktywny (nie zawiera logicznej części logu – VLF 2 na Rysunku 4), natomiast wartość 2 sygnalizuje, że określony VLF jest aktywny (zawiera logiczną część logu – VLF 1, 3, 4 na Rysunku 4). W sytuacji, w której DBCC LOGINFO zwróci dla bieżącej bazy więcej niż 50 rekordów (nie jest to oczywiście sztywna granica), zalecane jest przeprowadzenie następującej trzyetapowej procedury:

-- Full recovery
CHECKPOINT
GO
BACKUP LOG nazwa_bazy TO urządzenie
-- Simple recovery
CHECKPOINT

ALTER DATABASE nazwa_bazy SET SINGLE_USER

DBCC SHRINKFILE(logiczna_nazwa_pliku_dziennika)

ALTER DATABASE nazwa_bazy MODIFY FILE
(
	NAME = logiczna_nazwa_pliku_dziennika,
	SIZE = nowy_rozmiar
)

Do początku strony

Kontrola i zmniejszanie rozmiaru pliku dziennika

Procedura mająca na celu redukcję ilości VLFów, podana we wcześniejszym punkcie, pośrednio prowadzi także do zmniejszenia rozmiarów pliku dziennika. Czasem jednak sam rozmiar tego pliku jest dla nas istotny i zależy nam na swobodnej możliwości jego doboru. Rozmiar pliku dziennika można sprawdzić na kilka sposobów. Dwa najpopularniejsze sposoby zakładają użycie procedury systemowej sp_helpfile lub polecenia DBCC SQLPERF(logspace). Ostatnie polecenie, oprócz informacji o rozmiarze pliku logu, pokazuje również procentowy poziom użycia pliku dziennika – można więc łatwo odczytać, w ilu procentach plik ten jest pusty. Zmniejszanie rozmiarów dziennika transakcji nie jest operacją ani wydajną, ani szczególnie zalecaną. Jeżeli zmniejszymy rozmiar dziennika, a on szybko znowu urośnie (gdyż po prostu takie są potrzeby wynikające z charakteru pracy danej bazy danych), pojawi się niekorzystna fragmentacja pliku na poziomie systemu.

O ile zwiększenie rozmiarów pliku logu można przeprowadzić potencjalnie w każdym przypadku za pomocą polecenia ALTER DATABASE MODIFY FILE, o tyle log nie zawsze będzie chciał zmniejszyć się do żądanego rozmiaru. Przyczyna niemożności ponownego użycia lub obcięcia dziennika transakcji jest możliwa do zidentyfikowania za pomocą kolumn log_reuse_wait i log_reuse_wait_desc widoku katalogowego sys.databases. W przykładzie z Rysunku 1 plik dziennika nie może zostać obcięty, gdyż baza znajduje się w modelu full recovery i konieczne jest uprzednie wykonanie backupu logu. Trzeba też pamiętać, że pełny backup bazy nie powoduje obcięcia dziennika transakcji – robi to wyłącznie backup logu.

Rys. 1. Przykładowy powód niemożności obcięcia dziennika.

Nie uda się zmienić rozmiarów pliku logu w sytuacji, kiedy w danej bazie istnieje otwarta (aktywna) transakcja. Otwarte transakcje można wyszukać za pomocą polecenia DBCC OPENTRAN(nazwa_bazy) lub patrząc na kolumnę open_tran widoku systemowego sysprocesses, lub przeglądając widok sys.dm_tran_active_transactions. Jeżeli określona baza danych jest skonfigurowana jako publisher replikacji transakcyjnej, to obcięcie logu może być blokowane przez aktywną transakcję oznaczoną do replikacji.

Jeżeli żadna z wymienionych sytuacji nie stanie nam na przeszkodzie, to nasze postępowanie zmierzające do zmniejszenia rozmiarów dziennika jest analogiczne jak w przypadku procedury redukującej liczbę VLFów. W zależności od modelu odtwarzania wykonujemy CHECKPOINT (simple recovery model) lub backup logu (inny model). W SQL Server 2005 w modelu full recovery możliwe jest usunięcie nieaktywnej części logu bez wykonywania rzeczywistego backupu:

BACKUP LOG nazwa_bazy WITH NO_LOG
-- lub
BACKUP LOG nazwa_bazy WITH TRUNCATE_ONLY

Powyższe polecenia zostaną usunięte w przyszłych wersjach systemu SQL Server, dlatego nie zaleca się ich używania. Aby model full recovery funkcjonował nadal przy wykonywaniu backupu logu z opcją TRUNCATE_ONLY (NO_LOG), należy koniecznie wykonać pełny backup bazy danych.

Do samego zmniejszania rozmiaru pliku dziennika transakcji używamy polecenia DBCC SHRINKFILE(logiczna_nazwa_pliku_dziennika). DBCC SHRINKFILE akceptuje także opcjonalny parametr docelowego rozmiaru pliku:

-- próba zmniejszenia rozmiaru pliku logu do 5 MB
DBCC SHRINKFILE(testDB_log, 5)

Jeżeli docelowy rozmiar pliku nie zostanie podany (co jest częstą praktyką), plik logu będzie zmniejszany do rozmiaru domyślnego, czyli rozmiaru podanego podczas tworzenia pliku lub określonego przez ostatnią operację ALTER DATABASE MODIFY FILE, o ile taka wystąpiła. Komentarza wymaga opcja TRUNCATEONLY, która może być podana jako trzeci (opcjonalny) parametr polecenia DBCC SHRINKFILE. TRUNCATEONLY powoduje obcięcie i zwrócenie do systemu operacyjnego wolnej przestrzeni wyłącznie z końca pliku – strony logu nie zostają więc przeorganizowane tak, aby zwolnić jak najwięcej przestrzeni. W rzeczywistości jest to domyślny i jedyny tryb możliwy do zastosowania na plikach dziennika transakcji – DBCC SHRINKFILE zadziała w trybie TRUNCATEONLY nawet w sytuacji, w której opcja ta nie została jawnie podana. Używanie jej ma więc sens jedynie przy plikach danych (MDF), gdzie możliwy jest wybór potencjalnego sposobu zwolnienia przestrzeni, natomiast – zgodnie z oficjalną dokumentacją – wykorzystywanie tej opcji na plikach dziennika jest bezcelowe.

Po wykonaniu operacji shrinkowania warto przeprowadzić czynności opisane w punkcie 3 procedury podanej w poprzednim punkcie (ustawienie pożądanego rozmiaru pliku dziennika). Nie zaleca się automatycznego zmniejszania rozmiarów pliku dziennika transakcji przez zastosowanie opcji AUTOSHRINK:

-- zwraca ustawienie opcji AUTOSHRINK dla danej bazy
EXEC sp_dboption 'nazwa_bazy', 'autoshrink';

Zamiennie z poleceniem DBCC SHRINKFILE można używać polecenia DBCC SHRINKDATABASE. Pierwsze z wymienionych poleceń ma jednak większe możliwości i lepiej nadaje się do opisywanych w niniejszym artykule operacji.

Do początku strony

Nośnik dyskowy dla pliku dziennika transakcji

Należy zwrócić uwagę na jeden z istotnych aspektów dziennika transakcji, który można zaobserwować choćby na Rysunku 1 w I części artykułu – log transakcyjny w normalnych warunkach (brak awarii, sytuacja zatwierdzania kolejnych transakcji) jest jedynie zapisywany. Odczyt pojawia się dopiero w sytuacji mniej lub bardziej problematycznej lub niecodziennej, jak np. proces wycofywania transakcji czy database recovery. Z faktu tego jasno wynika konieczność dostarczenia na potrzeby logu nośnika dyskowego zoptymalizowanego pod zapis.

Oczywiście warto też przeznaczyć na plik logu oddzielny dysk (macierz) tak, aby głowice dysków mogły bez przeskoków, w sposób ciągły zapisywać do dziennika kolejne porcje bajtów. Konfiguracja macierzy przeznaczona na dziennik transakcji od zawsze była tematem ożywionych dyskusji i wielu nieporozumień. W Tabeli 2 zaprezentowane zostały ogólne informacje dotyczące zastosowania wybranych konfiguracji RAID (ang. Redundant Array of Independent Devices/Disks, nadmiarowa macierz niezależnych dysków) jako nośników dla dziennika transakcji SQL Server 2005, co do których zgodna jest znaczna część społeczności profesjonalistów. Należy jasno podkreślić, że są to dane ogólne, podczas gdy rzeczywistość wymaga czasem o wiele bardziej szczegółowego podejścia, włączając w to dogłębną analizę danego przypadku.

Jak widać, na potrzeby dziennika transakcji najrozsądniejszym rozwiązaniem wydaje się konfiguracja RAID 10. Jest to również – jak to najczęściej bywa – rozwiązanie najdroższe, bo wymagające co najmniej 4 fizycznych dysków. W sytuacji, w której RAID 10 przekracza możliwości finansowe, zalecane zwykle są: RAID 0, RAID 1 i RAID 5. Oczywiście każda macierz redundantna będzie lepszym rozwiązaniem niż pojedynczy dysk, ale trzeba dokładnie rozważyć zalety i wady każdej z zastosowanych konfiguracji względem konkretnego przypadku. Niejednokrotnie okazuje się bowiem, że zastosowanie macierzy o określonym poziomie (np. RAID 5) skutkuje problemami związanymi z wydajnością operacji zapisu.

Do początku strony

Modele odtwarzania baz danych (recovery models)

SQL Server 2005 oferuje trzy modele odtwarzania (ang. recovery models) poszczególnych baz danych: full (pełny) , bulk-logged i simple (prosty). Modele odtwarzania mają istotny wpływ na funkcjonowanie mechanizmów backupu i odtwarzania baz danych, a także – zmieniają skutki operacji checkpoint. Standardowo każda nowa baza danych dziedziczy model odtwarzania z bazy model. Można w uproszczeniu powiedzieć, że model odtwarzania wyznacza granicę akceptowalnej utraty danych i określa, jakimi metodami można przywracać określoną bazę danych do stanu sprzed awarii.

Prosty model odtwarzania (simple recovery model) loguje transakcje w minimalnym zakresie. Wszystkie nieaktywne transakcje są usuwane z logu podczas operacji checkpoint, co eliminuje konieczność obcinania dziennika transakcji przez backup logu. Z drugiej strony trzeba pamiętać, że takie zachowanie zwiększa ryzyko utraty danych w momencie awarii. W prostym modelu odtwarzania, w sytuacji wystąpienia krytycznej awarii, administrator może jedynie odzyskać bazę z najbardziej aktualnego backupu bazy danych (pełnego lub różnicowego). Backupy logu transakcyjnego nie są dostępne, gdyż operacja checkpoint każdorazowo obcina wszystkie nieaktywne transakcje.

Pełny model odtwarzania (full recovery model) gwarantuje, że wszystkie nieaktywne transakcje pozostają w dzienniku do momentu wykonania backupu logu. Model ten umożliwia backupowanie zarówno samych danych, jak i – dziennika transakcji. Jesteśmy więc w stanie nie tylko przywrócić bazę danych z backupu pełnego lub różnicowego, ale także odtworzyć ją do określonego punktu w czasie (np. momentu awarii). Także wszelkie operacje typu masowego (ang. bulk load), czyli m.in. SELECT INTO, BULK INSERT, również CREATE INDEX i operacje na danych typu varbinary(max) są w pełni logowane, więc zawsze możliwe do odtworzenia.

Co istotne, baza danych z zadeklarowanym pełnym modelem odtwarzania faktycznie będzie funkcjonować w tym modelu dopiero w momencie wykonania pełnego backupu bazy. Do tego momentu baza danych będzie zachowywała się analogicznie, jakby była w trybie simple recovery. Poniższe zapytania pozwala jednoznacznie stwierdzić, które bazy rzeczywiście pracują w modelu full recovery (czyli też – czy log będzie obcinany podczas operacji checkpoint):

SELECT DB_NAME(dbs.database_id),
dbs.recovery_model_desc,
rec.last_log_backup_lsn
FROM sys.database_recovery_status rec
INNER JOIN sys.databases dbs
ON (rec.database_id = dbs.database_id);

Jeżeli w kolumnie last_log_backup_lsn dla danej bazy znajduje się NULL, tzn. że log będzie obcinany podczas operacji checkpoint i niezależnie od ustawionego modelu odtwarzania baza pracuje tak, jakby była w simple recovery model.

Bulk-logged recovery model stanowi pewien kompromis między wyżej wymienionymi modelami. Jest mu jednak znacznie bliżej do modelu pełnego. Jedyna zasadnicza różnica między modelem full a bulk-logged polega na tym, że omawiany model w sposób minimalny (jak model prosty) loguje operacje typu bulk load. Nie są więc one w pełni odtwarzalne. Pozostałe transakcje logowane są w trybie pełnym (analogicznie więc jak w modelu full recovery). Pozwala to na zwiększenie wydajności operacji typu masowego, które zwykle nie muszą być szczegółowo logowane, przy jednoczesnym zachowaniu szczegółowego logowania pozostałych transakcyjnych operacji. Poniższy skrypt Transach-SQL demonstruje, ile rekordów trafi do dziennika transakcji podczas operacji typu bulk-load w modelu full i w modelu bulk-logged.

CREATE DATABASE full_vs_bulk_logged
GO
BACKUP DATABASE full_vs_bulk_logged TO DISK='C:\full_vs_bulk_logged.bak' WITH INIT
-- baza znajduje się w FULL RECOVERY MODEL
GO
USE full_vs_bulk_logged;
-- oczyszczenie dziennika transakcji przez wykonanie backupu poprzedzonego CHECKPOINTem
CHECKPOINT
GO
BACKUP LOG full_vs_bulk_logged TO DISK='C:\full_vs_bulk_logged.log' WITH INIT
GO
-- operacja typu BULK LOAD - ładowanie danych do tabeli test_load
SELECT * INTO test_load FROM OPENROWSET (BULK 'C:\full_vs_bulk_logged.bak', SINGLE_BLOB) blk;
GO
-- ilość rekordów dziennika
SELECT count(*) as full_recovery_count FROM fn_dblog(NULL,NULL);
GO
DROP TABLE test_load
GO
/**** przełączenie FULL -> BULK-LOGGED ****/
ALTER DATABASE full_vs_bulk_logged SET RECOVERY BULK_LOGGED
-- baza znajduje siê teraz w BULK-LOGGED RECOVERY MODEL
GO
-- oczyszczenie dziennika transakcji przez wykonanie backupu poprzedzonego CHECKPOINTem
CHECKPOINT
GO
BACKUP LOG full_vs_bulk_logged TO DISK='C:\full_vs_bulk_logged.log' WITH INIT
GO
-- operacja typu BULK LOAD - ładowanie danych do tabeli test_load
SELECT * INTO test_load FROM OPENROWSET (BULK 'C:\full_vs_bulk_logged.bak', SINGLE_BLOB) blk;
GO
-- ilość rekordów dziennika
SELECT count(*) as bulk_logged_recovery_count FROM fn_dblog(NULL,NULL);
GO
-- clean-up
USE [master];
GO
DROP DATABASE full_vs_bulk_logged

Wynik powyższego skryptu jest następujący:

Rys. 2. Obsługa operacji bulk load w różnych modelach odtwarzania.

Skrypt, który może wydawać się trochę skomplikowany, w rzeczywistości robi bardzo prostą rzecz – dwukrotnie wczytuje do nowopowstałej tabeli binarny plik (w tym przypadku jest to backup bazy, ale dla celów demonstracyjnych nie ma to znaczenia). Pierwsza operacja SELECT INTO odbywa się w modelu full recovery, następnie baza zostaje przełączona w model bulk-logged i identyczna operacja SELECT INTO przeprowadzana jest ponownie. Pozostałe komendy skryptu mają za zadanie oczyścić dziennik i wyświetlić jego ilość rekordów. Jak widać na Rysunku 2, wyniki różnią się znacznie, oznacza to, że w trybie bulk_logged zalogowane zostało o wiele mniej informacji, co jest zgodne z powyższymi teoretycznymi rozważaniami. Analogiczny wynik uzyskuje się, jeżeli druga operacja SELECT INTO odbywać się będzie w modelu simple recovery.

Do początku strony

Wybór właściwego modelu odtwarzania

Wybór właściwego recovery model jest bardzo istotny i powinien wynikać z głębokiej świadomości i przemyślenia tematu. Tabela 3 prezentuje najważniejsze cechy wszystkich modeli odtwarzania, mające pomóc w podjęciu właściwej decyzji co do wyboru jednego z nich.

Można też zaprojektować system bazodanowy tak, aby uwzględniał i automatycznie przeprowadzał zmiany modelu odtwarzania – np. w ciągu dnia działał w trybie pełnym, natomiast podczas nocnych operacji utrzymaniowych lub/i powtarzalnego zasilania – w trybie bulk-logged, a nawet i simple. Brak przemyślenia tematu co do wyboru modelu odtwarzania skutkuje m.in. niepożądanym zwiększeniem się rozmiarów dziennika transakcji lub brakiem możliwości odzyskania bazy danych do określonego momentu sprzed awarii.

Do początku strony

Podsumowanie

W artykule przedstawiłem podstawowe kwestie związane z tematyką dziennika transakcji w systemie SQL Server 2005 wraz z licznymi zaleceniami dotyczącymi jego obsługi. Doświadczenie pokazuje, że log transakcyjny bywa często zaniedbywanym komponentem, o którym administrator przypomina sobie dopiero w sytuacji wystąpienia problemu. Pozostaje mieć nadzieję, że publikacje takie jak ta przyczynią się do zmiany tej sytuacji, gdyż dziennik transakcji to w rzeczywistości serce każdej bazy danych, która nie działa w trybie READ_ONLY i na pewno należy mu się więcej uwagi.

Do początku strony

Przeczytaj pozostałe części tego artykułu

Marcin Guzowski Zawodowo zajmuje się inżynierią oprogramowania, w szczególności rozwiązaniami data-centric. Obecnie jest szefem zespołu pracującego nad usługami inteligentnej automatyzacji danych (m.in. deduplikacji, standaryzacji, kojarzenia danych podobnych) w Dziale Badawczo-Rozwojowym w firmie Anica System S.A. Praktycznie od początku swojej przygody z bazami danych intensywnie wykorzystuje SQL Server jako podstawową platformę bazodanową, także do zastosowań stosunkowo nietypowych i wymagających maksymalizacji wydajności działania.

Do początku strony