Udostępnij za pomocą

Architektura dziennika transakcji programu SQL Server i przewodnik zarządzania

Dotyczy:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)SQL Database w Microsoft Fabric

Każda baza danych programu SQL Server ma dziennik transakcji, który rejestruje wszystkie transakcje i modyfikacje bazy danych wprowadzone przez każdą transakcję. Dziennik transakcji jest krytycznym składnikiem bazy danych i, jeśli wystąpi awaria systemu, dziennik transakcji może być wymagany do przywrócenia bazy danych do spójnego stanu. Ten przewodnik zawiera informacje na temat architektury fizycznej i logicznej dziennika transakcji. Zrozumienie architektury może zwiększyć efektywność zarządzania dziennikami transakcji.

Architektura logiczna dziennika transakcji

Dziennik transakcji programu SQL Server działa logicznie tak, jakby dziennik transakcji był ciągiem rekordów dziennika. Każdy rekord dziennika jest identyfikowany przez numer sekwencji dziennika (LSN). Każdy nowy rekord dziennika jest zapisywany na logicznym końcu dziennika przy użyciu nazwy LSN wyższej niż LSN rekordu przed nim. Rekordy dziennika są przechowywane w sekwencji szeregowej podczas ich tworzenia, tak że jeśli LSN2 jest większy niż LSN1, zmiana opisana przez rekord dziennika związany z LSN2 nastąpiła po zmianie opisanej przez rekord dziennika związany z LSN1. Każdy rekord dziennika zawiera identyfikator transakcji, do którego należy. Dla każdej transakcji wszystkie rekordy dziennika skojarzone z transakcją są indywidualnie połączone w łańcuchu przy użyciu wskaźników wstecznych, które przyspieszają wycofywanie transakcji.

Podstawowa struktura LSN to jest [VLF ID:Log Block ID:Log Record ID]. Aby uzyskać więcej informacji, zapoznaj się z sekcjami VLF i bloków logów.

Oto przykład LSN: 00000031:00000da0:0001, gdzie 0x31 jest identyfikatorem VLF, 0xda0 jest identyfikatorem bloku dziennika i 0x1 jest pierwszym rekordem dziennika w tym bloku dziennika. Aby zapoznać się z przykładami LSN, przyjrzyj się danym wyjściowym sys.dm_db_log_info DMV i sprawdź kolumnę vlf_create_lsn.

Rekordy modyfikacji danych rejestrują albo wykonaną operację logiczną, albo obrazy danych przed i po zmianie. Przed wykonaniem operacji obraz jest kopią danych; po obrazie jest kopią danych po wykonaniu operacji.

Kroki odzyskiwania operacji zależą od typu rekordu dziennika:

  • Zarejestrowano operację logiczną

    • Aby powtórzyć operację logiczną, operacja jest wykonywana ponownie.
    • Aby wycofać operację logiczną, wykonywana jest odwrotna operacja logiczna.

  • Przed i po zarejestrowaniu obrazu

    • Aby przeprowadzić operację do przodu, stosowany jest obraz końcowy.
    • Aby wycofać operację, stosuje się wcześniejszy obraz.

Wiele typów operacji jest rejestrowanych w dzienniku transakcji. Te operacje obejmują:

  • Początek i koniec każdej transakcji.

  • Każda modyfikacja danych (wstawianie, aktualizowanie lub usuwanie). Modyfikacje obejmują zmiany dokonywane przez systemowe procedury składowane lub instrukcje języka definicji danych (DDL) w dowolnej tabeli, łącznie z tabelami systemowymi.

  • Każdy zakres i alokacja lub dealokacja stron.

  • Tworzenie lub usuwanie tabeli lub indeksu.

Operacje wycofywania są również rejestrowane. Każda transakcja rezerwuje miejsce w dzienniku transakcji, aby upewnić się o istnieniu wystarczającej ilości miejsca dziennika do obsługi cofnięcia spowodowanego jawną instrukcją wycofywania lub w przypadku wystąpienia błędu. Ilość zarezerwowanego miejsca zależy od operacji wykonywanych w transakcji, ale ogólnie jest równa ilości miejsca używanego do rejestrowania każdej operacji. To zarezerwowane miejsce jest zwalniane po zakończeniu transakcji.

Sekcja pliku dziennika z pierwszego rekordu dziennika, który musi być obecny w celu pomyślnego wycofania całego bazy danych do ostatniego zapisanego rekordu dziennika, jest nazywana aktywną częścią dziennika, aktywnego dziennika lub ogona dziennika. Jest to sekcja dziennika wymagana do pełnego odzyskiwania bazy danych. Żadna część aktywnego dziennika nigdy nie może być skrócona. Numer sekwencji dziennika (LSN) tego pierwszego rekordu jest znany jako minimalny LSN odzyskiwania (MinLSN). Aby uzyskać więcej informacji na temat operacji obsługiwanych przez dziennik transakcji, zobacz Dziennik transakcji.

Różnicowe kopie zapasowe i kopie zapasowe dzienników przyczyniają się do przesunięcia przywróconej bazy danych do późniejszego punktu w czasie, co odpowiada wyższemu LSN.

Architektura fizyczna dziennika transakcji

Dziennik transakcji bazy danych mapuje co najmniej jeden plik fizyczny. Koncepcyjnie plik dziennika jest ciągiem rekordów dziennika. Fizycznie sekwencja rekordów dziennika jest wydajnie przechowywana w zestawie plików fizycznych, które implementują dziennik transakcji. Dla każdej bazy danych musi istnieć co najmniej jeden plik dziennika.

Pliki dziennika wirtualnego (VLFS)

Aparat bazy danych programu SQL Server dzieli każdy plik dziennika fizycznego wewnętrznie na kilka plików dziennika wirtualnego (VLFS). Pliki dziennika wirtualnego nie mają stałego rozmiaru i nie ma stałej liczby plików dziennika wirtualnego dla fizycznego pliku dziennika. Aparat bazy danych wybiera rozmiar plików dziennika wirtualnego dynamicznie podczas tworzenia lub rozszerzania plików dziennika. Aparat bazy danych próbuje zachować kilka plików wirtualnych. Rozmiar plików wirtualnych po rozszerzonym pliku dziennika jest sumą rozmiaru istniejącego dziennika i rozmiaru nowego pliku przyrostowego. Nie można skonfigurować ani ustawić rozmiaru ani liczby plików dziennika wirtualnego przez administratorów.

Tworzenie wirtualnego pliku dziennika

Tworzenie pliku dziennika wirtualnego (VLF) jest zgodne z tą metodą:

  • W programie SQL Server 2014 (12.x) i nowszych wersjach, jeśli następne zwiększenie rozmiaru jest mniejsze niż 1/8 bieżącego rozmiaru fizycznego dziennika, utwórz 1 VLF, który pokrywa ten rozmiar.
  • Jeśli następny wzrost jest większy niż 1/8 bieżącego rozmiaru dziennika, użyj metody stosowanej przed rokiem 2014, czyli:
    • Jeśli wzrost jest mniejszy niż 64 MB, utwórz 4 VDF, które obejmują rozmiar wzrostu (na przykład dla wzrostu 1 MB, utwórz 4 VDF o rozmiarze 256 KB).
      • W usłudze Azure SQL Database i począwszy od programu SQL Server 2022 (16.x) (wszystkie wersje) logika jest nieco inna. Jeśli wzrost jest mniejszy lub równy 64 MB, aparat bazy danych tworzy tylko jeden VLF, aby pokryć rozmiar wzrostu.
    • Jeśli wzrost wynosi od 64 MB do 1 GB, utwórz 8 VDF, które obejmują rozmiar wzrostu (na przykład dla wzrostu 512 MB, utwórz 8 VDF o rozmiarze 64 MB).
    • Jeśli wzrost jest większy niż 1 GB, utwórz 16 VDF, które obejmują rozmiar wzrostu, na przykład dla wzrostu 8 GB, utwórz 16 VF o rozmiarze 512 MB).

Jeśli pliki dziennika rosną do dużego rozmiaru w wielu małych przyrostach, kończą się one wieloma wirtualnymi plikami dziennika. Może to spowolnić uruchamianie bazy danych, operacje tworzenia kopii zapasowych dzienników i przywracania oraz powodować replikację transakcyjną/ciągłe opóźnienie ponownego włączania. Z drugiej strony, jeśli pliki dziennika są ustawione na duży rozmiar z kilkoma lub tylko jednym przyrostem, zawierają one niewiele bardzo dużych plików dziennika wirtualnego. Aby uzyskać więcej informacji na temat prawidłowego szacowania wymaganego rozmiaru i ustawienia automatycznego zwiększania dziennika transakcji, zobacz sekcję Zalecenia w temacieZarządzanie rozmiarem pliku dziennika transakcji.

Zalecamy utworzenie plików logów zbliżonych do wymaganego rozmiaru końcowego, przy użyciu przyrostów niezbędnych do uzyskania optymalnej dystrybucji VLF i stosunkowo dużej wartości growth_increment.

Zapoznaj się z poniższymi wskazówkami, aby określić optymalną dystrybucję VLF dla bieżącego rozmiaru dziennika transakcji:

  • Wartość rozmiaruSIZE ustawiona przez argument ALTER DATABASE jest początkowym rozmiarem pliku dziennika.
  • Wartość growth_increment (nazywana również wartością autogrowth), którą ustawia argument FILEGROWTH z ALTER DATABASE, określa, ile miejsca jest dodawane do pliku za każdym razem, gdy potrzebne jest nowe miejsce.

Aby uzyskać więcej informacji na temat FILEGROWTH i SIZE argumentów ALTER DATABASE, zobacz ALTER DATABASE (Transact-SQL) File and Filegroup Options (Opcje plików i grup plików).

Tip

Aby określić optymalną dystrybucję VLF dla aktualnego rozmiaru dziennika transakcji we wszystkich bazach danych w określonym wystąpieniu oraz wymagane przyrosty w celu osiągnięcia żądanej wielkości, zobacz ten skrypt Fixing-VLFs na GitHubie.

Co się stanie, gdy masz zbyt wiele VLF?

Podczas początkowych etapów procesu odzyskiwania bazy danych program SQL Server odnajduje wszystkie pliki VLF we wszystkich plikach dziennika transakcji i tworzy listę tych plików VLF. Ten proces może zająć dużo czasu w zależności od liczby plików VFS znajdujących się w określonej bazie danych. Im więcej VLF, tym dłuższy proces. Baza danych może mieć dużą liczbę segmentów VLF, jeśli częste automatyczne zwiększanie dziennika transakcji lub ręczne zwiększanie występuje w małych przyrostach. Gdy liczba vlF osiągnie zakres kilkuset tysięcy, można napotkać niektóre lub większość następujących objawów:

  • Odzyskiwanie co najmniej jednej bazy danych trwa bardzo długo podczas uruchamiania programu SQL Server.
  • Przywracanie bazy danych trwa bardzo długo.
  • Próba dołączenia bazy danych zajmuje bardzo dużo czasu.
  • Podczas próby skonfigurowania dublowania bazy danych występują komunikaty o błędach 1413, 1443 i 1479 wskazujące limit czasu.
  • Podczas próby przywrócenia bazy danych występują błędy związane z pamięcią, takie jak 701.
  • Replikacja transakcyjna lub przechwytywanie zmian danych może mieć znaczne opóźnienie.

Podczas badania dziennika błędów programu SQL Server można zauważyć, że przed fazą analizy procesu odzyskiwania bazy danych może upłynąć znaczna ilość czasu. Przykład:

2022-05-08 14:42:38.65 spid22s Starting up database 'lot_of_vlfs'.
2022-05-08 14:46:04.76 spid22s Analysis of database 'lot_of_vlfs' (16) is 0% complete (approximately 0 seconds remain). Phase 1 of 3. This is an informational message only. No user action is required.

Ponadto program SQL Server może rejestrować błąd MSSQLSERVER_9017 podczas przywracania bazy danych z dużą liczbą plików VFS:

Database %ls has more than %d virtual log files which is excessive. Too many virtual log files can cause long startup and backup times. Consider shrinking the log and using a different growth increment to reduce the number of virtual log files.

Aby uzyskać więcej informacji, zobacz MSSQLSERVER_9017.

Napraw bazy danych z dużą liczbą plików VLF

Aby zachować łączną liczbę plików VDF na rozsądną kwotę, taką jak maksymalnie kilka tysięcy, można zresetować plik dziennika transakcji, aby zawierał mniejszą liczbę plików VDF, wykonując następujące kroki:

  1. Ręczne zmniejszanie plików dziennika transakcji.

  2. Zwiększ pliki do wymaganego rozmiaru ręcznie w jednym kroku, używając następującego skryptu języka T-SQL:

    ALTER DATABASE <database name> MODIFY FILE (NAME='Logical file name of transaction log', SIZE = <required size>);

    Note

    Ten krok jest również możliwy w programie SQL Server Management Studio przy użyciu strony właściwości bazy danych.

Po ustawieniu nowego układu pliku dziennika transakcji z mniejszą liczbą VLF, przejrzyj i wprowadź niezbędne zmiany w ustawieniach automatycznego zwiększania rozmiaru dziennika transakcji. To ustawienie sprawdza, czy plik dziennika nie napotka tego samego problemu w przyszłości.

Przed wykonaniem dowolnej z tych operacji upewnij się, że masz prawidłową kopię zapasową z możliwością przywracania w przypadku wystąpienia problemów później.

Aby określić optymalną dystrybucję VLF dla bieżącego rozmiaru dziennika transakcji wszystkich baz danych w danym wystąpieniu oraz wymagane przyrosty wzrostu w celu osiągnięcia wymaganego rozmiaru, można użyć następującego skryptu GitHub, aby naprawić VLF.

Bloki dziennika

Każdy VLF zawiera co najmniej jeden blok dziennika. Każdy blok dziennika składa się z rekordów dziennika (wyrównanych do granicy 4-bajtowej). Blok dziennika ma zmienny rozmiar i zawsze jest całkowitą wielokrotnością 512 bajtów (minimalny rozmiar sektora obsługiwanego przez SQL Server), z maksymalnym rozmiarem 60 KB. Blok dziennika jest podstawową jednostką operacji we/wy na potrzeby rejestrowania transakcji.

Podsumowując, blok dziennika jest kontenerem rekordów dzienników używanych jako podstawowa jednostka rejestrowania transakcji podczas zapisywania rekordów dziennika na dysku.

Każdy blok dziennika w VLF jest jednoznacznie adresowany przez przesunięcie bloku. Pierwszy blok zawsze ma przesunięcie bloku, które wskazuje poza pierwsze 8 KB w VLF.

Ogólnie rzecz biorąc, VLF jest zawsze wypełniany blokami dziennika. Istnieje możliwość, że ostatni blok dziennika w VLF jest pusty (na przykład nie zawiera żadnych rekordów dziennika). Dzieje się tak, gdy rekord dziennika, który ma być zapisany, nie pasuje do bieżącego bloku dziennika, a także wtedy, gdy miejsce pozostawione na VLF jest niewystarczające do przechowywania tego rekordu dziennika. W takim przypadku tworzony jest pusty blok logu, który wypełnia wirtualny plik dziennika. Rekord dziennika jest wstawiany do pierwszego bloku w kolejnym VLF.

Cyklicznego charakteru dziennika transakcji

Dziennik transakcji jest plikiem zawijanym. Rozważmy na przykład bazę danych z jednym fizycznym plikiem dziennika podzielonym na cztery pliki VDF. Po utworzeniu bazy danych plik dziennika logicznego rozpoczyna się na początku pliku dziennika fizycznego. Nowe rekordy dziennika są dodawane na końcu dziennika logicznego i rozszerzane w kierunku końca dziennika fizycznego. Obcinanie logów zwalnia wszystkie wirtualne logi, których wszystkie rekordy znajdują się przed minimalnym numerem sekwencji loga odzyskiwania (MinLSN). MinLSN to numer sekwencji najstarszego rekordu dziennika, który jest wymagany do pomyślnego wycofania bazy danych w całości. Dziennik transakcji w przykładowej bazie danych będzie wyglądać podobnie do tego na poniższym diagramie.

Diagram ilustrujący sposób dzielenia pliku dziennika fizycznego na dzienniki wirtualne.

Gdy koniec dziennika logicznego osiągnie koniec fizycznego pliku dziennika, nowe rekordy dziennika są zawijane na początku pliku dziennika fizycznego.

Diagram ilustrujący sposób zawijania dziennika transakcji logicznych w fizycznym pliku dziennika.

Ten cykl powtarza się bez końca, o ile koniec dziennika logicznego nigdy nie osiągnie początku dziennika logicznego. Jeśli stare rekordy dziennika są obcinane wystarczająco często, aby zawsze pozostawić wystarczające miejsce dla wszystkich nowych rekordów dziennika generowanych do następnego punktu kontrolnego, dziennik nigdy się nie zapełnia. Jeśli jednak koniec dziennika logicznego osiągnie początek dziennika logicznego, wystąpi jedna z dwóch rzeczy:

Jeśli dziennik zawiera wiele fizycznych plików dziennika, dziennik logiczny przechodzi przez wszystkie pliki dziennika fizycznego, zanim opakowuje się z powrotem na początku pierwszego fizycznego pliku dziennika.

Important

Aby uzyskać więcej informacji na temat zarządzania rozmiarem dziennika transakcji, zobacz Zarządzanie rozmiarem pliku dziennika transakcji.

Obcinanie dziennika

Obcięcie dziennika jest niezbędne, aby zapobiec zapełnieniu dziennika. Obcinanie dziennika usuwa nieaktywne pliki wirtualne z logicznego dziennika transakcji bazy danych SQL Server, zwalniając w ten sposób miejsce w dzienniku logicznym do ponownego wykorzystania przez fizyczny dziennik transakcji. Jeśli dziennik transakcji nigdy nie zostanie obcięty, w końcu wypełni wszystkie miejsca na dysku przydzielone do fizycznych plików dziennika. Jednak przed obcięciem dziennika należy przeprowadzić operację punktu kontrolnego. Punkt kontrolny zapisuje bieżące strony zmodyfikowane w pamięci (znane jako zanieczyszczone strony) i informacje dziennika transakcji z pamięci do dysku. Po wykonaniu punktu kontrolnego nieaktywna część dziennika transakcji jest oznaczona jako wielokrotnego użytku. Następnie obcinanie dziennika może zwolnić nieaktywną część. Aby uzyskać więcej informacji na temat punktów kontrolnych, zobacz Punkty kontrolne bazy danych (SQL Server).

Na poniższych diagramach przedstawiono dziennik transakcji przed obcięciem i po nim. Pierwszy diagram przedstawia dziennik transakcji, który nigdy nie został obcięty. Obecnie cztery pliki dziennika wirtualnego są używane przez dziennik logiczny. Dziennik logiczny rozpoczyna się od początku pierwszego wirtualnego pliku dziennika i kończy się w dzienniku wirtualnym 4. Rekord MinLSN znajduje się w dzienniku wirtualnym 3. Dziennik wirtualny 1 i dziennik wirtualny 2 zawierają tylko nieaktywne rekordy dziennika. Te rekordy można skrócić. Dziennik wirtualny 5 jest nadal nieużywany i nie jest częścią bieżącego dziennika logicznego.

Diagram przedstawiający sposób wyświetlania dziennika transakcji przed skróceniem.

Drugi diagram pokazuje, jak log wygląda po obcięciu. Dziennik wirtualny 1 i dziennik wirtualny 2 zostały zwolnione do ponownego użycia. Dziennik logiczny rozpoczyna się teraz na początku dziennika wirtualnego 3. Dziennik wirtualny 5 jest nadal nieużywany i nie jest częścią bieżącego dziennika logicznego.

Diagram przedstawiający sposób wyświetlania dziennika transakcji po jego obcinaniu.

Obcięcie dziennika odbywa się automatycznie po następujących zdarzeniach, z wyjątkiem opóźnień z jakiegoś powodu:

  • W ramach prostego modelu odzyskiwania po punkcie kontrolnym.
  • W ramach modelu pełnego odzyskiwania lub modelu odzyskiwania rejestrowanego zbiorczo po utworzeniu kopii zapasowej dziennika, jeśli punkt kontrolny wystąpił od poprzedniej kopii zapasowej.

Przycinanie logów może być opóźnione przez różne czynniki. W przypadku długiego opóźnienia w obcinaniu dziennika dziennik transakcji może się wypełnić. Aby uzyskać informacje, zobacz Czynniki, które mogą opóźniać obcinanie dziennika i Rozwiązywanie problemów z pełnym dziennikiem transakcji (błąd programu SQL Server 9002).

Dziennik zapisu z wyprzedzeniem transakcji

W tej sekcji opisano rolę dziennika transakcji z wyprzedzeniem zapisu w rejestrowaniu modyfikacji danych na dysku. Serwer SQL używa algorytmu zapisywania dziennika z wyprzedzeniem (WAL), który gwarantuje, że żadne modyfikacje danych nie są zapisywane na dysku przed zapisaniem powiązanego rekordu dziennika na dysku. Zachowuje właściwości ACID dla transakcji.

Aby uzyskać więcej informacji na temat operacji WAL, zobacz Podstawy we/wy programu SQL Server.

Aby zrozumieć, w jaki sposób rejestrowanie z wyprzedzeniem działa w odniesieniu do dziennika transakcji, ważne jest, aby wiedzieć, jak zmodyfikowane dane są zapisywane na dysku. Program SQL Server przechowuje pamięć podręczną buforu (nazywaną również pulą), w której odczytuje strony danych, gdy dane muszą być pobierane. Gdy strona jest modyfikowana w pamięci podręcznej buforu, nie jest natychmiast zapisywana z powrotem na dysku; Zamiast tego strona jest oznaczona jako zanieczyszczona. Strona danych może zawierać więcej niż jeden zapis logiczny, zanim zostanie fizycznie zapisany na dysku. Dla każdego zapisu logicznego w pamięci podręcznej dziennika jest wstawiany rekord dziennika transakcji, który rejestruje modyfikację. Rekordy dziennika muszą być zapisywane na dysku, zanim skojarzona brudna strona zostanie usunięta z pamięci podręcznej i zapisana na dysku. Proces punktu kontrolnego okresowo skanuje pamięć podręczną buforu pod kątem ze stronami z określonej bazy danych i zapisuje wszystkie zanieczyszczone strony na dysku. Punkty kontrolne oszczędzają czas podczas późniejszego odzyskiwania, tworząc punkt, w którym zagwarantowano zapisanie wszystkich zanieczyszczonych stron na dysku.

Zapisywanie zmodyfikowanej strony danych z pamięci podręcznej buforu na dysk jest nazywane opróżnianie strony. Program SQL Server ma logikę, która uniemożliwia opróżnianie brudnej strony przed zapisaniem skojarzonego rekordu dziennika. Rekordy dziennika są zapisywane na dysku, gdy bufory dziennika są opróżniane. Dzieje się tak za każdym razem, gdy transakcja zostanie zatwierdzona lub bufory dziennika staną się pełne.

Kopie zapasowe dziennika transakcji

W tej sekcji przedstawiono pojęcia dotyczące tworzenia kopii zapasowych i przywracania (stosowania) dzienników transakcji. W ramach pełnych i rejestrowanych zbiorczo modeli odzyskiwania wykonywanie rutynowych kopii zapasowych dzienników transakcji (kopii zapasowych dzienników) jest niezbędne do odzyskania danych. Możesz utworzyć kopię zapasową dziennika, gdy jest uruchomiona pełna kopia zapasowa. Aby uzyskać więcej informacji na temat modeli odzyskiwania, zobacz Tworzenie kopii zapasowych i przywracanie baz danych programu SQL Server.

Przed utworzeniem pierwszej kopii zapasowej dziennika należy utworzyć pełną kopię zapasową, taką jak kopia zapasowa bazy danych lub pierwsza w zestawie kopii zapasowych plików. Przywracanie bazy danych przy użyciu tylko kopii zapasowych plików może stać się złożone. W związku z tym zalecamy rozpoczęcie od pełnej kopii zapasowej bazy danych, gdy będzie to możliwe. Następnie konieczne jest regularne tworzenie kopii zapasowej dziennika transakcji. Minimalizuje to nie tylko narażenie na utratę pracy, ale także umożliwia obcięcie dziennika transakcji. Zazwyczaj, dziennik transakcji jest skracany po każdej typowej kopii zapasowej dziennika.

Aby ograniczyć liczbę kopii zapasowych dziennika, które należy przywrócić, niezbędne jest rutynowe tworzenie kopii zapasowych danych. Można na przykład zaplanować cotygodniową pełną kopię zapasową bazy danych i codzienne różnicowe kopie zapasowe baz danych.

Pomyśl o wymaganym czasie odzyskiwania i punkcie odniesienia odzyskiwania podczas implementowania strategii odzyskiwania, a w szczególności o pełnym i różnicowym harmonogramie tworzenia kopii zapasowych bazy danych.

Aby uzyskać więcej informacji na temat kopii zapasowych dziennika transakcji, zobacz Tworzenie kopii zapasowych dziennika transakcji (SQL Server).

Częstotliwość tworzenia kopii zapasowych i wymagania biznesowe

Należy wykonać wystarczająco częste kopie zapasowe dzienników, aby obsługiwać wymagania biznesowe, w szczególności tolerancję utraty pracy, taką jak może być spowodowana uszkodzonym magazynem dzienników.

Odpowiednia częstotliwość tworzenia kopii zapasowych dziennika zależy od tolerancji narażenia na utratę pracy zrównoważonej przez liczbę kopii zapasowych dzienników, które można przechowywać, zarządzać nimi i potencjalnie przywracać. Pomyśl o wymaganym celu czasu odzyskiwania (RTO) i celu punktu odzyskiwania (RPO) podczas implementowania strategii odzyskiwania, a w szczególności częstotliwość tworzenia kopii zapasowej dziennika.

Wykonanie kopii zapasowej dziennika co 15 do 30 minut może być wystarczające. Jeśli twoja firma wymaga zminimalizowania narażenia na utratę pracy, rozważ częstsze tworzenie kopii zapasowych dziennika. Częstsze kopie zapasowe dzienników mają dodatkową zaletę, zwiększając częstotliwość zmniejszania dzienników, co skutkuje mniejszymi plikami dziennika.

Łańcuch logów

Ciągła sekwencja kopii zapasowych dzienników jest nazywana łańcuchem dzienników. Łańcuch dzienników rozpoczyna się od pełnej kopii zapasowej bazy danych. Zazwyczaj nowy łańcuch dzienników jest uruchamiany tylko wtedy, gdy kopia zapasowa bazy danych jest tworzona po raz pierwszy lub po przełączeniu modelu odzyskiwania z prostego odzyskiwania do pełnego lub zbiorczego odzyskiwania. Jeśli nie zdecydujesz się zastąpić istniejących zestawów kopii zapasowych podczas tworzenia pełnej kopii zapasowej bazy danych, istniejący łańcuch dzienników pozostaje nienaruszony. Dzięki łańcuchowi dzienników można przywrócić bazę danych z dowolnej pełnej kopii zapasowej bazy danych z zestawu nośników, a następnie wszystkie kolejne kopie zapasowe dzienników aż do punktu odzyskiwania. Punkt odzyskiwania może być końcem ostatniej kopii zapasowej dziennika lub określonym punktem odzyskiwania w dowolnym z kopii zapasowych dziennika. Aby uzyskać więcej informacji, zobacz Tworzenie kopii zapasowych dziennika transakcji (SQL Server).

Aby przywrócić bazę danych do punktu awarii, łańcuch dzienników musi być spójny. Oznacza to, że nieprzerwana sekwencja kopii zapasowych dziennika transakcji musi sięgać do momentu awarii. Moment, w którym należy rozpocząć tę sekwencję dziennika, zależy od rodzaju przywracanych kopii zapasowych danych: bazy danych, częściowych albo plików. W przypadku bazy danych lub częściowej kopii zapasowej sekwencja kopii zapasowych dziennika musi rozciągać się od końca bazy danych lub częściowej kopii zapasowej. W przypadku zestawu kopii zapasowych plików, sekwencja kopii zapasowych dziennika musi sięgać od początku pełnego zestawu kopii zapasowych plików. Aby uzyskać więcej informacji, zobacz Apply Transaction Log Backups (SQL Server).

Przywracanie kopii zapasowych dziennika

Przywracanie kopii zapasowej dziennika przekazuje zmiany, które zostały zarejestrowane w dzienniku transakcji, aby odtworzyć dokładny stan bazy danych w momencie rozpoczęcia operacji tworzenia kopii zapasowej dziennika. Podczas przywracania bazy danych należy przywrócić kopie zapasowe dziennika utworzone po przywróceniu pełnej kopii zapasowej bazy danych lub od początku pierwszej kopii zapasowej pliku, która została przywrócona. Zazwyczaj po przywróceniu najnowszych danych lub różnicowej kopii zapasowej należy przywrócić serię kopii zapasowych dziennika do momentu osiągnięcia punktu odzyskiwania. Następnie odzyskasz bazę danych. Spowoduje to wycofanie wszystkich transakcji, które były niekompletne podczas uruchamiania odzyskiwania i powoduje przeniesienie bazy danych do trybu online. Po odzyskaniu bazy danych nie można przywrócić więcej kopii zapasowych. Aby uzyskać więcej informacji, zobacz Apply Transaction Log Backups (SQL Server).

Punkty kontrolne i aktywna część dziennika

Punkty kontrolne przenoszą brudne strony danych z bufora podręcznego bieżącej bazy danych na dysk. Minimalizuje to aktywną część dziennika, która musi zostać przetworzona podczas pełnego odzyskiwania bazy danych. Podczas pełnego odzyskiwania wykonywane są następujące typy akcji:

  • Rekordy dziennika modyfikacji, które nie zostały opróżnione na dysk, zanim system zatrzymany zostanie wycofany.
  • Wszystkie modyfikacje skojarzone z niekompletnymi transakcjami, takie jak transakcje, dla których nie COMMIT ma rekordu dziennika lub ROLLBACK nie są rejestrowane, są cofane.

Operacja punktu kontrolnego

Punkt kontrolny wykonuje następujące procesy w bazie danych:

  • Zapisuje rekord w pliku dziennika z oznaczeniem początku punktu kontrolnego.

  • Przechowuje informacje rejestrowane dla punktu kontrolnego w łańcuchu rekordów dziennika punktów kontrolnych.

    Jedną z informacji zarejestrowanych w punkcie kontrolnym jest numer sekwencji dziennika (LSN) pierwszego rekordu dziennika, który musi być obecny w celu pomyślnego wycofania całej bazy danych. Ten LSN nazywany jest minimalnym LSN odzyskiwania (MinLSN). Wartość MinLSN jest minimalną wartością:

    • LSN początku punktu kontrolnego.
    • LSN (Logiczny Numer Sekwencyjny) początku najstarszej aktywnej transakcji.
    • LSN początku najstarszej transakcji replikacji, której jeszcze nie dostarczono do bazy danych dystrybucji.

    Rekordy punktu kontrolnego zawierają również listę wszystkich aktywnych transakcji, które zmodyfikowały bazę danych.

  • Jeśli baza danych używa prostego modelu odzyskiwania, oznacza ponowne użycie miejsca, które poprzedza MinLSN.

  • Zapisuje wszystkie zanieczyszczone strony dziennika i danych na dysku.

  • Zapisuje rekord oznaczający koniec punktu kontrolnego do pliku dziennika.

  • Zapisuje LSN rozpoczynający ten łańcuch do strony rozruchowej bazy danych.

Działania, które powodują punkt kontrolny

Punkty kontrolne występują w następujących sytuacjach:

  • Instrukcja CHECKPOINT jest jawnie wykonywana. Punkt kontrolny występuje w bieżącej bazie danych dla połączenia.

  • Operacja z minimalnym logowaniem jest wykonywana w bazie danych; na przykład operacja kopiowania zbiorczego jest wykonywana w bazie danych, która korzysta z modelu odzyskiwania o minimalnym logowaniu (Bulk-Logged).

  • Pliki bazy danych zostały dodane lub usunięte przy użyciu polecenia ALTER DATABASE.

  • Wystąpienie programu SQL Server jest zatrzymywane przez instrukcję SHUTDOWN lub przez zatrzymanie usługi PROGRAMU SQL Server (MSSQLSERVER). Każda akcja powoduje utworzenie punktu kontrolnego w każdej bazie danych w wystąpieniu programu SQL Server.

  • Wystąpienie programu SQL Server okresowo generuje automatyczne punkty kontrolne w każdej bazie danych, aby skrócić czas potrzebny na odzyskanie bazy danych przez wystąpienie.

  • Wykonywana jest kopia zapasowa bazy danych.

  • Wykonywane jest działanie wymagające zamknięcia bazy danych. Może się to zdarzyć, gdy AUTO_CLOSE opcja to ON i ostatnie połączenie użytkownika z bazą danych jest zamknięte. Innym przykładem jest zmiana opcji bazy danych, która wymaga ponownego uruchomienia bazy danych.

Automatyczne punkty kontrolne

Aparat bazy danych programu SQL Server generuje automatyczne punkty kontrolne. Interwał między automatycznymi punktami kontrolnymi zależy od ilości używanego miejsca w dzienniku i czasu, który upłynął od ostatniego punktu kontrolnego. Przedział czasu między automatycznymi punktami kontrolnymi może być bardzo zmienny i długi, jeśli w bazie danych wprowadzono kilka modyfikacji. Automatyczne punkty kontrolne mogą również występować często, jeśli wiele danych jest modyfikowanych.

Użyj opcji konfiguracji serwera interwału odzyskiwania, aby obliczyć interwał między automatycznymi punktami kontrolnymi dla wszystkich baz danych na wystąpieniu serwera. Ta opcja określa maksymalny czas, jaki mechanizm bazy danych ma wykorzystać do odzyskiwania bazy danych podczas ponownego uruchomienia systemu. Silnik bazodanowy szacuje, ile rekordów dziennika może przetworzyć w okresie odzyskiwania podczas operacji odzyskiwania.

Przedział czasowy między automatycznymi punktami kontrolnymi zależy również od modelu odzyskiwania.

  • Jeśli baza danych korzysta z pełnego lub dziennika zbiorczego modelu odzyskiwania, automatyczny punkt kontrolny jest generowany za każdym razem, gdy liczba rekordów dziennika osiągnie liczbę szacowaną przez silnik bazy danych, którą może przetworzyć w czasie określonym w opcji interwału odzyskiwania.

  • Jeśli baza danych korzysta z prostego modelu odzyskiwania, automatyczny punkt kontrolny jest generowany za każdym razem, gdy liczba rekordów dziennika osiągnie mniejszą z tych dwóch wartości:

    • Dziennik jest wypełniony w 70 procentach.
    • Liczba rekordów dziennika osiąga poziom, który silnik bazy danych szacuje, że może przetworzyć w czasie określonym przez opcję interwału odzyskiwania.

Aby uzyskać informacje o ustawianiu interwału odzyskiwania, zobacz Konfiguracja serwera: interwał odzyskiwania (min).

Tip

-k Opcja zaawansowanej konfiguracji programu SQL Server umożliwia administratorowi bazy danych ograniczanie aktywności we/wy punktu kontrolnego na podstawie wydajności podsystemu we/wy dla niektórych typów punktów kontrolnych. Opcja -k konfiguracji ma zastosowanie do automatycznych punktów kontrolnych i wszelkich innych punktów kontrolnych bez ograniczeń.

Automatyczne punkty kontrolne obcinają nieużywaną sekcję dziennika transakcji, jeśli baza danych korzysta z prostego modelu odzyskiwania. Jednakże, jeśli baza danych korzysta z modelu odzyskiwania pełnego lub z rejestracją zbiorczą, dziennik nie jest obcinany przez automatyczne punkty kontrolne. Aby uzyskać więcej informacji, zobacz Dziennik transakcji.

Instrukcja CHECKPOINT udostępnia teraz opcjonalny argument checkpoint_duration, który określa żądany okres czasu (w sekundach), aby punkty kontrolne zostały zakończone. Aby uzyskać więcej informacji, zobacz CHECKPOINT.

Dziennik aktywny

Sekcja pliku dziennika z minLSN do ostatniego zapisanego rekordu dziennika jest nazywana aktywną częścią dziennika lub aktywnym dziennikem. Jest to sekcja dziennika wymagana do pełnego odzyskiwania bazy danych. Żadna część aktywnego dziennika nigdy nie może być skrócona. Wszystkie rekordy dziennika muszą zostać obcięte z części dziennika przed minLSN.

Na poniższym diagramie przedstawiono uproszczoną wersję dziennika końca transakcji z dwoma aktywnymi transakcjami. Rekordy punktów kontrolnych zostały skompaktowane do pojedynczego rekordu.

Diagram ilustrujący dziennik końca transakcji z dwoma aktywnymi transakcjami i kompaktowym rekordem punktu kontrolnego.

LSN 148 jest ostatnim rekordem w dzienniku transakcji. W momencie przetworzenia zarejestrowanego punktu kontrolnego w LSN 147, Tran 1 został zatwierdzony, a Tran 2 była jedyną aktywną transakcją. To sprawia, że pierwszy rekord dziennika dla Tran 2 jest najstarszym rekordem dziennika dla transakcji aktywnej w momencie ostatniego punktu kontrolnego. Dzięki temu LSN 142, rekord rozpoczęcia transakcji dla Tran 2, jest MinLSN.

Długotrwałe transakcje

Aktywny dziennik musi zawierać każdą część wszystkich niezatwierdzonych transakcji. Aplikacja, która uruchamia transakcję i nie zatwierdza jej ani nie cofa, uniemożliwia silnikowi bazy danych przejście do najmniejszegoLSN. Taka sytuacja może spowodować dwa typy problemów:

  • Jeśli system zostanie zamknięty po dokonaniu wielu niezatwierdzonych modyfikacji transakcji, faza odzyskiwania podczas kolejnego uruchomienia może trwać znacznie dłużej niż czas określony w opcji przedział odzyskiwania.
  • Dziennik może być bardzo duży, ponieważ dziennik nie może być obcięty obok minLSN. Dzieje się tak nawet wtedy, gdy baza danych korzysta z prostego modelu odzyskiwania, w którym dziennik transakcji jest obcięty na każdym automatycznym punkcie kontrolnym.

Odzyskiwanie długotrwałych transakcji i problemów opisanych w tym artykule można uniknąć, korzystając z przyspieszonego odzyskiwania bazy danych, funkcji dostępnej od programu SQL Server 2019 (15.x) i usługi Azure SQL Database.

Transakcje replikacji

Agent czytnika dzienników monitoruje dziennik transakcji każdej bazy danych skonfigurowanej do replikacji transakcyjnej i kopiuje transakcje oznaczone do replikacji z dziennika transakcji do bazy danych dystrybucji. Aktywny dziennik musi zawierać wszystkie transakcje oznaczone do replikacji, ale nie zostały jeszcze dostarczone do bazy danych dystrybucji. Jeśli te transakcje nie są replikowane w odpowiednim czasie, mogą zapobiec skróceniu dziennika. Aby uzyskać więcej informacji, zobacz Replikacja transakcyjna.

Treści powiązane

  • Last updated on