Dlaczego dziennik transakcji stale rośnie lub zabrakło miejsca?


207

Ten wydaje się być powszechnym pytaniem na większości forów i całej sieci, jest zadawany tutaj w wielu formatach, które zazwyczaj brzmią tak:

W SQL Server -

  • Jakie są powody, dla których dziennik transakcji rośnie tak duży?
  • Dlaczego mój plik dziennika jest tak duży?
  • Jakie są sposoby zapobiegania występowaniu tego problemu?
  • Co mam zrobić, gdy sprawdzam przyczynę i chcę umieścić plik dziennika transakcji w zdrowym rozmiarze?
  0

Naprawdę krótka odpowiedź to: umieść bazę danych w trybie ** Prosty ** (nie ** Pełny **).Jeśli nie wykonujesz wielu kopii zapasowych dziennika transakcji w ciągu dnia pomiędzy pełną nocną kopią zapasową: nie potrzebujesz ** trybu pełnego **. 07 gru. 162016-12-07 14:48:40

  0

@IanBoyd - Na pewno to najprostsza odpowiedź.Kluczem jest jednak dotarcie do tego, co to znaczy.Uderzyłem w to w odpowiedzi.Niestety, zbyt wielu ludzi nigdy nie odnosi się do tego problemu lub po prostu przechodzi do prostoty bez zrozumienia dlaczego.Wyedytuję jednak moją odpowiedź, aby nieco wcześniej włączyć tryb prosty. 07 gru. 162016-12-07 14:53:55

270

Krótsza odpowiedź:

Prawdopodobnie masz uruchomioną już długą transakcję (konserwacja indeksu? Duża partia usuwa lub aktualizuje?) Lub jesteś w „domyślnym” (więcej poniżej, co oznacza domyślnie) tryb odzyskiwaniaFulli nie wzięlikopia zapasowa dziennika(lub nie biorą ich zbyt często).

Jeśli jest to problem z modelem odzyskiwania, prostą odpowiedzią może być Przełącz naSimpletryb odzyskiwania, jeśli nie potrzebujesz odzyskiwania czasu i regularnych kopii zapasowych dziennika.Jednak wielu ludzi odpowiada na nie bez zrozumienia modeli odzyskiwania.Czytaj dalej, aby zrozumieć, dlaczego to ma znaczenie, a następnie zdecyduj, co robisz.Możesz także zacząć robić kopie zapasowe dziennika i pozostać w nimFullpoprawa.

Mogą istnieć inne powody, ale są one najczęstsze.Ta odpowiedź zaczyna zanurzać się w dwa najczęstsze powody i podaje pewne podstawowe informacje na temat powodów i przyczyn zaistniałych przyczyn, a także wyjaśnia inne przyczyny.


Dłuższa odpowiedź:Jakie scenariusze mogą powodować dalszy wzrost dziennika?Przyczyn jest wiele, ale zazwyczaj są to dwa następujące wzorce: istnieje nieporozumienie dotyczące modeli odzyskiwania lub transakcje są długotrwałe.Przeczytaj więcej szczegółów.

Top powód 1/2: Nie rozumiem modeli odzyskiwania

(Będąc wTryb pełnego odzyskiwaniai nie bioręRejestrowanie kopii zapasowych- To jest najczęstszy powód - ogromna większość osób doświadczających tego problemu to.)

Chociaż ta odpowiedź nie jest głębokim nurkowaniem w modelach odzyskiwania SQL Server, temat modeli odzyskiwania ma krytyczne znaczenie dla tego problemu.

W SQL Server są trzyrecovery models:

  • Full,
  • Bulk-Loggedi
  • Simple.

ZignorujemyBulk-Loggedna razie powiemy, że jest to model hybrydowy i większość ludzi, którzy są w tym modelu, jest z jakiegoś powodu i rozumie modele odzyskiwania.

Dwie rzeczy, na których nam zależy i ich zamieszanie, są przyczyną większości przypadków osób mających ten problemSimpleiFull.

Intermission: Recovery in general

Zanim porozmawiamy o modelach regeneracji: porozmawiajmy o ogólnym wyzdrowieniu.Jeśli chcesz zagłębić się w ten temat, po prostu przeczytajPaul Randal's blogi tyle postów, ile chcesz.Na to pytanie jednak:

  1. Odzyskiwanie awaryjne/ponowne uruchomienie
    Jest przeznaczony jeden cel pliku dziennika transakcjiawaryjne/ponowne uruchomienie odzyskiwania.Aby przetoczyć i wycofać pracę, która została wykonana (przewijanie do przodu/ponawianie) przed awarią lub ponownym uruchomieniem i pracą, która została uruchomiona, ale nie została zakończona po awarii lub ponownym uruchomieniu (cofnięcie/cofnięcie).Zadaniem dziennika transakcji jest sprawdzenie, czy transakcja została rozpoczęta, ale nigdy nie została zakończona (wycofanie lub zawieszenie/ponowne uruchomienie nastąpiło przed zatwierdzeniem transakcji).W tej sytuacji zadaniem dziennika jest powiedzenie„Hej… to się nigdy nie skończyło, cofnijmy to”podczas regeneracji.Zadaniem dziennika jest również sprawdzenie, czy coś skończyłeś i że aplikacja kliencka została poinformowana, że ​​została ukończona (nawet jeśli nie została jeszcze wzmocniona w twoim pliku danych) i powiedz„Hej… to się naprawdę wydarzyło, rzućmy to dalej, zróbmy to tak, jak aplikacje myślą, że to było”po restarcie.Teraz jest więcej, ale to jest główny cel.

  2. Odzyskiwanie punktu w czasie
    Innym celem pliku dziennika transakcji jest umożliwienie nam odzyskania pliku dopunkt w czasiez powodu „oops” w bazie danych lub w celu zagwarantowania punktu odzyskiwania w przypadku awarii sprzętu obejmującej dane i/lub pliki dziennika bazy danych.Jeśli ten dziennik transakcji zawiera rekordy transakcji, które zostały uruchomione i zakończone w celu odzyskania, SQL Server może, a następnie używa tych informacji, aby uzyskać bazę danych do miejsca, w którym znajdowała się przed wystąpieniem problemu.Ale to nie zawsze jest dla nas dostępna opcja.Aby to działało, musimy mieć naszą bazę danych po prawej stroniemodel odzyskiwaniai musimy wziąćkopie zapasowe dziennika.

Modele odzyskiwania

Na modele odzyskiwania:

  • Prosty model odzyskiwania
    Tak więc z powyższym wprowadzeniem najłatwiej jest o tym mówićSimple Recoverynajpierw model.W tym modelu mówisz SQL Server:„Czuję się dobrze, gdy używasz pliku dziennika transakcji do awarii i ponownego uruchomienia odzyskiwania ...”(Naprawdę nie masz wyboru. SpójrzACID propertiesi to powinno mieć sens szybko.)„... ale gdy już nie potrzebujesz go do tego celu odzyskiwania/ponownego uruchamiania, idź ponownie i użyj pliku dziennika”.

    SQL Server nasłuchuje tego żądania w Simple Recovery i zachowuje tylko informacje potrzebne do przywrócenia po awarii/ponownego uruchomienia.Po upewnieniu się, że SQL Server może odzyskać dane, ponieważ dane są zahartowane do pliku danych (mniej więcej), dane, które zostały zahartowane, nie są już potrzebne w dzienniku i są oznaczane do obcięcia - co oznacza, że ​​zostaną ponownie użyte.

  • Pełny model odzyskiwania
    ZFull Recovery, mówisz SQL Serverowi, że chcesz odzyskać dane do określonego punktu w czasie, o ile plik dziennika jest dostępny lub do określonego punktu w czasie, który jest objęty kopią zapasową dziennika.W tym przypadku, gdy SQL Server osiągnie punkt, w którym byłoby bezpiecznie obcinać plik dziennika w prostym modelu odzyskiwania, nie zrobi tego.ZamiastPozwala to na dalszy wzrost pliku dziennikai pozwoli mu rosnąć,dopóki nie wykonasz kopii zapasowej dziennika(lub zabraknie miejsca na dysku pliku dziennika) w normalnych okolicznościach.

Przełączenie z Simple na Full ma Gotcha.

Są tutaj zasady i wyjątki.Poniżej omówimy długoterminowe transakcje.

Ale jedno zastrzeżenie, o którym należy pamiętać w trybie pełnego odzyskiwania, to: Jeśli po prostu przełączysz się naFull Recoverytryb, ale nigdy nie wykonuj początkowej pełnej kopii zapasowej, SQL Server będzieniehonoruj ​​swoją prośbę, aby byćFull RecoveryModel.Twój dziennik transakcji będzie nadal działał tak, jak wSimpledopóki nie przełączysz się na pełny model odzyskiwania I weź swój pierwszyFull Backup.

Pełny model odzyskiwania bez kopii zapasowych dziennika jest zły.

To najczęstszy powód niekontrolowanego wzrostu kłód?Odpowiedź: Przebywanie w trybie pełnego odzyskiwania bez tworzenia kopii zapasowych dziennika.

To się stałowszystkoczas dla ludzi.

Dlaczego to taki powszechny błąd?

Dlaczego tak się dzieje cały czas?Ponieważ każda nowa baza danych otrzymuje ustawienie modelu odzyskiwania początkowego, patrząc na bazę danych modelu.

Ustawienie modelu odzyskiwania początkowego modelu jest zawszeFull Recovery Model- dopóki ktoś tego nie zmieni.Można więc powiedzieć, że „domyślny model odzyskiwania” toFull.Wiele osób nie zdaje sobie z tego sprawy i uruchamia bazy danychFull Recovery Modelbez kopii zapasowych dziennika i dlatego plik dziennika transakcji jest znacznie większy niż to konieczne.Dlatego ważne jest, aby zmienić ustawienia domyślne, gdy nie działają one dla Twojej organizacji i jej potrzeb)

Pełny model odzyskiwania ze zbyt małą liczbą kopii zapasowych dziennika jest zły.

Możesz także wpaść w kłopoty, nie robiąc wystarczająco dużo kopii zapasowych dziennika.
Wykonywanie kopii zapasowej dziennika dziennie może brzmieć dobrze, przywraca wymaganie mniej poleceń przywracania, ale pamiętając o powyższej dyskusji, ten plik dziennika będzie rósł i wzrastał, dopóki nie wykonasz kopii zapasowych dziennika.

Jak mogę się dowiedzieć, jakiej częstotliwości kopii zapasowej dziennika potrzebuję?

Musisz wziąć pod uwagę częstotliwość tworzenia kopii zapasowych dziennika, mając na uwadze dwie rzeczy:

  1. Potrzeby odzyskiwania- Mam nadzieję, że to będzie pierwsze.W przypadku, gdy dysk zawierający dziennik transakcji ulegnie uszkodzeniu lub wystąpi poważne uszkodzenie, które ma wpływ na kopię zapasową dziennika, ile danych można utracić?Jeśli liczba ta nie przekracza 10-15 minut, musisz wykonać kopię zapasową dziennika co 10-15 minut, koniec dyskusji.
  2. Loguj wzrost- Jeśli Twoja organizacja może stracić więcej danych ze względu na możliwość łatwego odtworzenia tego dnia, możesz mieć kopię zapasową dziennika znacznie rzadziej niż 15 minut.Może twoja organizacja jest w porządku co 4 godziny.Ale musisz sprawdzić, ile transakcji generujesz w ciągu 4 godzin.Czy pozwolenie, aby dziennik wzrastał w ciągu tych czterech godzin, spowoduje utworzenie zbyt dużego pliku dziennika?Czy to oznacza, że ​​kopie zapasowe dziennika trwają zbyt długo?

Główny powód 2/2: Długie transakcje

(„Mój model odzyskiwania jest w porządku! Dziennik wciąż rośnie!)

Może to być również przyczyną niekontrolowanego i nieograniczonego wzrostu kłód.Bez względu na model odzyskiwania, ale często pojawia się jako„Ale jestem w prostym modelu odzyskiwania - dlaczego mój dziennik wciąż rośnie ?!”

Powód jest prosty: jeśli SQL używa tego dziennika transakcji do celów odzyskiwania, tak jak to opisano powyżej, to musi powrócić do początku transakcji.

Jeśli masz transakcję, która zajmuje dużo czasu lub zawiera wiele zmian, dziennik nie może obcinać na punkcie kontrolnym żadnej ze zmian, które są nadal w otwartych transakcjach lub które rozpoczęły się od momentu rozpoczęcia transakcji.

Oznacza to, że duże usunięcie, usunięcie milionów wierszy w jednej instrukcji usuwania jest jedną transakcją, a dziennik nie może wykonać żadnego obcięcia, dopóki nie zostanie wykonane całe usunięcie.WFull Recovery Model, to usunięcie jest rejestrowane i może to być wiele rekordów dziennika.To samo z optymalizacją indeksu działa podczas okien konserwacji.Oznacza to również, że kiepskie zarządzanie transakcjami i nieuwzględnianie i zamykanie otwartych transakcji może naprawdę zaszkodzić tobie i plikowi dziennika.

Co mogę zrobić z tymi długotrwałymi transakcjami?

Możesz zapisać się tutaj przez:

  • Odpowiednio dobrać rozmiar pliku dziennika, aby uwzględnić najgorszy scenariusz - na przykład konserwację lub znane duże operacje.A kiedy rozwijasz plik dziennika, powinieneś się tym zająćguidance(i dwa linki, które ci wysyła) Kimberly Tripp.Właściwa wielkość jest tutaj bardzo krytyczna.
  • Oglądanie wykorzystania transakcji.Nie zaczynaj transakcji na serwerze aplikacji i nie rozpocznij długich rozmów z SQL Server i ryzykuj pozostawienie jednego otwarcia zbyt długo.
  • Oglądaćtransakcje dorozumianew instrukcjach DML.Na przykład:UPDATE TableName Set Col1 = 'New Value'to transakcja.Nie umieściłemBEGIN TRANtam i nie muszę, to wciąż jedna transakcja, która po zakończeniu automatycznie zatwierdza.Więc jeśli wykonujesz operacje na dużej liczbie wierszy, rozważ podzielenie tych operacji na łatwiejsze do zarządzania fragmenty i nadanie logowi czasu na odzyskanie.Lub rozważ odpowiedni rozmiar, aby sobie z tym poradzić.Lub może zajrzeć do zmiany modeli odzyskiwania w oknie masowego ładowania.

Czy te dwa powody odnoszą się również do Log Shipping?

Krótka odpowiedź: tak.Dłuższa odpowiedź poniżej.

Pytanie:„Używam wysyłania dziennika, więc moje kopie zapasowe dziennika są zautomatyzowane ... Dlaczego nadal widzę wzrost dziennika transakcji?”

Odpowiedź: czytaj dalej.

Co to jest Log Shipping?

Przesyłanie dziennika jest tym, co brzmi - wysyłasz kopie zapasowe dziennika transakcji na inny serwer do celów DR.Trochę inicjalizacji, ale potem proces jest dość prosty:

  • Zadanie do wykonania kopii zapasowej dziennika na jednym serwerze,
  • zadanie do skopiowania kopii zapasowej dziennika i
  • zadanie przywrócenia go bez odzyskiwania (alboNORECOVERYlubSTANDBY) na serwerze docelowym.

Istnieje również kilka zadań do monitorowania i alarmowania, jeśli sprawy nie idą zgodnie z planem.

W niektórych przypadkach możesz chcieć przywrócić dziennik tylko raz dziennie lub co trzeci dzień lub raz w tygodniu.W porządku.Jeśli jednak wprowadzisz tę zmianę we wszystkich zadaniach (w tym w kopii zapasowej dziennika i zadaniach kopiowania), oznacza to, że cały czas czekasz na wykonanie kopii zapasowej dziennika.Oznacza to, że będziesz miał dużo wzrostu logów - ponieważ jesteśw trybie pełnego odzyskiwania bez kopii zapasowych dziennika- i prawdopodobnie oznacza to również duży plik dziennika do skopiowania.Należy modyfikować tylko harmonogram zadania przywracania i pozwolić, aby kopie zapasowe dziennika i kopie odbywały się z większą częstotliwością, w przeciwnym razie będzie to wynikać z pierwszego problemu opisanego w tej odpowiedzi.


Ogólne rozwiązywanie problemów za pomocą kodów stanu

Istnieją inne powody niż te dwa, ale są one najpowszechniejsze.Niezależnie od przyczyny: istnieje sposób na przeanalizowanie przyczyny tego niewyjaśnionego wzrostu logu/braku obcięcia i zobaczenia, czym one są.

Przez wysłanie zapytania dosys.databasesWidok katalogu zawiera informacje opisujące powód, dla którego plik dziennika może oczekiwać na obcięcie/ponowne użycie.

Jest kolumna o nazwielog_reuse_waitz identyfikatorem wyszukiwania kodu przyczyny i alog_reuse_wait_desckolumna z opisem przyczyny oczekiwania.Z artykułu zamieszczonego w książkach internetowych wynika większość przyczyn (tych, które najprawdopodobniej zobaczysz, i tych, które możemy wyjaśnić z powodów. Brakujące są albo nieużywane, albo do użytku wewnętrznego) z kilkoma notatkami o oczekiwaniukursywa:

  • 0 = Nic
    Jak to brzmi .. Nie powinienem czekać

  • 1 = Punkt kontrolny
    Oczekiwanie na pojawienie się punktu kontrolnego.Powinno się to zdarzyć i powinno być dobrze - ale są przypadki, w których można szukać późniejszych odpowiedzi lub zmian.

  • 2 = Kopia zapasowa dziennika
    Czekasz na wykonanie kopii zapasowej dziennika.Albo masz je zaplanowane, a stanie się to wkrótce, albo masz opisany pierwszy problem, a teraz wiesz, jak to naprawić

  • 3 = Aktywna kopia zapasowa lub przywracanie
    Operacja tworzenia kopii zapasowej lub przywracania jest uruchomiona w bazie danych

  • 4 = Aktywna transakcja
    Istnieje aktywna transakcja, która musi zostać zakończona (tak czy inaczej -ROLLBACKlubCOMMIT) przed utworzeniem kopii zapasowej dziennika.To drugi powód opisany w tej odpowiedzi.

  • 5 = Dublowanie bazy danych
    Zwierciadło staje w tyle lub ma pewne opóźnienia w sytuacji dublowania z wysoką wydajnością lub dublowanie jest z jakiegoś powodu wstrzymane

  • 6 = Replikacja
    Mogą wystąpić problemy z replikacją, które mogłyby to spowodować - tak jak w przypadku agenta czytnika dziennika, baza danych myśląca, że ​​jest oznaczona do replikacji, która już nie istnieje i jest z innych powodów.Możesz również zobaczyć ten powód i jest to całkowicie normalne, ponieważ patrzysz na właściwy czas, tak jak transakcje są czytane przez czytnik dziennika

  • 7 = Tworzenie migawki bazy danych
    Tworzysz migawkę bazy danych, zobaczysz to, jeśli spojrzysz na właściwy moment w trakcie tworzenia migawki

  • 8 = Skanowanie dziennika
    Do tej pory nie natknąłem się na problem związany z tym działaniem.Jeśli spojrzysz wystarczająco długo i wystarczająco często, zobaczysz, że tak się dzieje, ale nie powinno to być przyczyną nadmiernego wzrostu dzienników transakcji, które widziałem.

  • 9 = Replika pomocnicza grup AlwaysOn stosuje rekordy dziennika transakcji tej bazy danych do odpowiedniej dodatkowej bazy danych.O najczystszym opisie jeszcze ..

+1

Podziały stron zwiększą rejestrację.Istotnym powodem (w moim doświadczeniu), o którym nie wspomniano o dużym wzroście, który może wymagać częstego kurczenia się, co zostało rozwiązane w wielu moich przypadkach, byłoby użycie właściwych opcji indeksowania, w tym właściwego mgr FillFactor.Używam następujących ustawień, z dokładną obserwacją.Ustawienia FF: (0/100) tabele z wysokimi odczytami/niskimi zapisami, (90) dla nieznacznie zmodyfikowanych, (80) średnimi odczytami/niskimi medami, (70) wysokopisy, (60) Trudno mi to osiągnąć poziom lub coś innego może być błędne.Następnie użyj odpowiednio harmonogramów zarządzania indeksami, dopasowując wolumen danych. 08 paź. 152015-10-08 19:31:03


98

Ponieważ nie jestem naprawdę zadowolony z żadnej odpowiedziover on Stack Overflow, w tym najmocniej zasugerowana sugestia, a ponieważ jest kilka rzeczy, które chciałbym poruszyć, a odpowiedź Mike'a nie, pomyślałem, że dostanę tu również swój wkład.Tam też umieściłem kopię tej odpowiedzi.

Zmniejszenie rozmiaru pliku dziennika powinno być zarezerwowane dla scenariuszy, w których napotkał nieoczekiwany wzrost, którego nie spodziewasz się ponownie.Jeśli plik dziennika wzrośnie ponownie do tego samego rozmiaru, niewiele się osiągnie, zmniejszając go tymczasowo.Teraz, w zależności od celów odzyskiwania bazy danych, są to działania, które należy podjąć.

Najpierw wykonaj pełną kopię zapasową

Nigdy nie wprowadzaj żadnych zmian w bazie danych bez upewnienia się, że możesz przywrócić ją, jeśli coś pójdzie nie tak.

Jeśli zależy Ci na przywróceniu punktu w czasie

(A dzięki odzyskiwaniu punkt-w-czas mam na myśli, że zależy Ci na przywróceniu czegoś innego niż pełna lub różnicowa kopia zapasowa).

Prawdopodobnie twoja baza danych jest włączonaFULLTryb odzyskiwania.Jeśli nie, upewnij się, że:

ALTER DATABASE yourdb SET RECOVERY FULL;

Nawet jeśli robisz regularne pełne kopie zapasowe, plik dziennika będzie rósł i wzrastał, dopóki nie wykonaszlogkopia zapasowa - jest to dla twojej ochrony, aby niepotrzebnie nie zajmować miejsca na dysku.Powinieneś wykonywać te kopie zapasowe dość często, zgodnie z celami odzyskiwania.Na przykład, jeśli masz regułę biznesową, która mówi, że możesz pozwolić sobie na utratę nie mniej niż 15 minut danych w przypadku katastrofy, powinieneś mieć zadanie, które tworzy kopię zapasową dziennika co 15 minut.Oto skrypt, który wygeneruje nazwy plików ze znacznikami czasu w oparciu o bieżący czas (ale możesz to zrobić również w przypadku planów konserwacji itp., Po prostu nie wybieraj żadnych opcji zmniejszania w planach konserwacji, są okropne).

DECLARE @path NVARCHAR(255) = N'\\backup_share\log\yourdb_' 
    + CONVERT(CHAR(8), GETDATE(), 112) + '_'
    + REPLACE(CONVERT(CHAR(8), GETDATE(), 108),':','')
    + '.trn';

BACKUP LOG foo TO DISK = @path WITH INIT, COMPRESSION;

Zauważ, że\\backup_share\powinien znajdować się na innej maszynie, która reprezentuje inne urządzenie pamięci masowej.Tworzenie kopii zapasowych na tej samej maszynie (lub na innej maszynie, która używa tych samych podstawowych dysków lub innej maszyny wirtualnej, która znajduje się na tym samym hoście fizycznym) tak naprawdę nie pomaga, ponieważ jeśli maszyna wybuchnie, utracisz bazę danychijego kopie zapasowe.W zależności od infrastruktury sieciowej bardziej sensowne może być tworzenie kopii zapasowych lokalnie, a następnie przenoszenie ich do innej lokalizacji za kulisami;w każdym przypadku chcesz je jak najszybciej usunąć z głównej maszyny bazy danych.

Teraz, gdy masz regularne kopie zapasowe dziennika, powinno być rozsądne zmniejszenie pliku dziennika do czegoś bardziej rozsądnego niż cokolwiek, co jest do tej pory wywoływane.To robinieoznacza bieganieSHRINKFILEw kółko, dopóki plik dziennika nie będzie wynosił 1 MB - nawet jeśli często tworzysz kopię zapasową dziennika, nadal musi uwzględniać sumę dowolnych jednoczesnych transakcji, które mogą wystąpić.Zdarzenia autogrow pliku dziennika są kosztowne, ponieważ SQL Server musi zerować pliki (w przeciwieństwie do plików danych, gdy włączona jest natychmiastowa inicjalizacja plików), a transakcje użytkownika muszą czekać, aż to nastąpi.Chcesz robić to w jak najmniejszym stopniu, rozwijając się i kurcząc, a na pewno nie chcesz, aby użytkownicy płacili za to.

Zauważ, że może być konieczne wykonanie kopii zapasowej dziennika dwa razy, zanim możliwe będzie zmniejszenie (dzięki Robert).

Więc musisz wymyślić praktyczny rozmiar pliku dziennika.Nikt tutaj nie może ci powiedzieć, co to jest, nie wiedząc dużo więcej o swoim systemie, ale jeśli często zmniejszałeś plik dziennika i rośnie on ponownie, dobry znak wodny jest prawdopodobnie o 10-50% wyższy niż największy. .Powiedzmy, że dochodzi do 200 MB i chcesz, aby kolejne zdarzenia autogrowth miały 50 MB, a następnie możesz dostosować rozmiar pliku dziennika w ten sposób:

USE [master];
GO
ALTER DATABASE Test1 
    MODIFY FILE
    (NAME = yourdb_log, SIZE = 200MB, FILEGROWTH = 50MB);
GO

Pamiętaj, że jeśli plik dziennika ma obecnie> 200 MB, może być konieczne uruchomienie tego pierwszego:

USE yourdb;
GO
DBCC SHRINKFILE(yourdb_log, 200);
GO

Jeśli nie zależy ci na odzyskiwaniu punktu w czasie

Jeśli jest to testowa baza danych, a nie zależy ci na przywracaniu systemu w czasie, powinieneś upewnić się, że baza danych jest włączonaSIMPLETryb odzyskiwania.

ALTER DATABASE yourdb SET RECOVERY SIMPLE;

Umieszczenie bazy danych wSIMPLEtryb odzyskiwania sprawi, że SQL Server ponownie wykorzysta fragmenty pliku dziennika (zasadniczo wycofując nieaktywne transakcje) zamiast rosnąć, aby zachować rejestrwszystkotransakcje (jakFULLodzyskiwanie trwa do momentu utworzenia kopii zapasowej dziennika).CHECKPOINTwydarzenia pomogą kontrolować dziennik i upewnić się, że nie musi on rosnąć, chyba że generujesz dużo aktywności dziennika międzyCHECKPOINTs.

Następnie powinieneś mieć całkowitą pewność, że ten wzrost logów był naprawdę spowodowany nieprawidłowym zdarzeniem (powiedzmy corocznym czyszczeniem wiosną lub przebudowywaniem największych indeksów), a nie ze względu na normalne, codzienne użytkowanie.Jeśli zmniejszysz plik dziennika do śmiesznie małego rozmiaru, a SQL Server po prostu musi go zwiększyć, aby dostosować się do normalnej aktywności, co zyskałeś?Czy udało ci się wykorzystać tę przestrzeń dyskową, którą zwolniłeś tylko tymczasowo?Jeśli potrzebujesz natychmiastowej poprawki, możesz uruchomić następujące czynności:

USE yourdb;
GO
CHECKPOINT;
GO
CHECKPOINT; -- run twice to ensure file wrap-around
GO
-- 200 MB
DBCC SHRINKFILE(yourdb_log, 200);
GO

W przeciwnym razie ustaw odpowiednią wielkość i tempo wzrostu.Zgodnie z przykładem w przypadku odzyskiwania w punkcie czasowym można użyć tego samego kodu i logiki, aby określić odpowiedni rozmiar pliku i ustawić rozsądne parametry autogrowth.

Niektóre rzeczy, których nie chcesz robić

  • Utwórz kopię zapasową dziennika za pomocąTRUNCATE_ONLYopcja, a następnieSHRINKFILE.Po pierwsze, toTRUNCATE_ONLYopcja została uznana za przestarzałą i nie jest już dostępna w obecnych wersjach SQL Server.Po drugie, jeśli jesteśFULLmodel odzyskiwania, to zniszczy łańcuch logów i wymaga nowej, pełnej kopii zapasowej.

  • Odłącz bazę danych, usuń plik dziennika i ponownie dołącz.Nie mogę podkreślić, jak niebezpieczne może to być.Twoja baza danych może nie powrócić, może być podejrzana, może będziesz musiał wrócić do kopii zapasowej (jeśli ją masz) itd. Itd.

  • Użyj opcji „zmniejsz bazę danych”.DBCC SHRINKDATABASEa opcja planu konserwacji, aby zrobić to samo, to złe pomysły, zwłaszcza jeśli naprawdę wystarczy rozwiązać problem z logiem.Wyceluj w plik, który chcesz dostosować i wyreguluj go niezależnie, używającDBCC SHRINKFILElubALTER DATABASE ... MODIFY FILE(przykłady powyżej).

  • Zmniejsz plik dziennika do 1 MB.Wygląda to kusząco, ponieważ, hej, SQL Server pozwoli mi to zrobić w pewnych scenariuszach i obejrzy całą przestrzeń, którą uwalnia!Jeśli twoja baza danych nie jest tylko do odczytu (i tak jest, powinieneś ją oznaczyć jako takąALTER DATABASE), to po prostu doprowadzi do wielu niepotrzebnych zdarzeń wzrostu, ponieważ dziennik musi uwzględniać bieżące transakcje, niezależnie od modelu odzyskiwania.Jaki jest sens tymczasowego zwolnienia tego miejsca, aby SQL Server mógł go cofać powoli i boleśnie?

  • Utwórz drugi plik dziennika.Zapewni to tymczasową ulgę dla dysku, który wypełnił twój dysk, ale to jest jak próba naprawienia przekłutego płuca za pomocą opaski.Powinieneś zająć się problematycznym plikiem dziennika bezpośrednio, zamiast tylko dodawać kolejny potencjalny problem.Inny niż przekierowanie niektórych działań dziennika transakcji na inny dysk, drugi plik dziennika naprawdę nie robi nic dla ciebie (w przeciwieństwie do drugiego pliku danych), ponieważ tylko jeden plik może być kiedykolwiek użyty w danym momencie.Paul Randal also explains why multiple log files can bite you later.

Bądź proaktywny

Zamiast zmniejszać swój plik dziennika do niewielkiej ilości i pozwalać mu na ciągłą autogrowację w małym tempie, ustaw go na dość duży rozmiar (taki, który pomieści sumę największego zestawu jednoczesnych transakcji) i ustaw rozsądny autogrow ustawienie awaryjne, tak aby nie musiało rosnąć wiele razy, aby zaspokoić pojedyncze transakcje, i aby relatywnie rzadko zdarzało się, aby wzrastał w trakcie normalnych operacji biznesowych.

Najgorsze możliwe ustawienia to wzrost o 1 MB lub wzrost o 10%.Zabawne jest to, że są to ustawienia domyślne dla SQL Server (na które skarżyłem się iasked for changes to no avail) - 1 MB dla plików danych i 10% dla plików dziennika.Ten pierwszy jest o wiele za mały w dzisiejszych czasach, a ten drugi za każdym razem prowadzi do dłuższych i dłuższych zdarzeń (powiedzmy, że Twój plik dziennika to 500 MB, pierwszy wzrost to 50 MB, następny wzrost to 55 MB, następny wzrost to 60,5 MB) , itd. itd. - i na powolne I/O, wierz mi, naprawdę zauważysz tę krzywą).

Dalsza lektura

Proszę, nie zatrzymuj się tutaj;Podczas gdy większość porad, które można znaleźć na temat kurczenia się plików dziennika, jest z natury zła, a nawet potencjalnie katastrofalna, są ludzie, którzy bardziej dbają o integralność danych niż zwalniają miejsce na dysku.


21

Możesz również zobaczyć zawartość swojego pliku dziennika.Aby to zrobić, możesz użyć nieudokumentowanychfn_dbloglub czytnik dziennika transakcji, taki jakApexSQL Log.

Nie pokazuje reorganizacji indeksu, ale pokazuje wszystkie zdarzenia DML i różne zdarzenia DDL:ALTER,CREATE,DROP, włącz/wyłącz wyzwalacz, przyznaj/cofnij uprawnienia, zmień nazwę obiektu.

ApexSQLLogProject.temp - ApexSQL.log

Zastrzeżenie: Pracuję dla ApexSQL jako inżynier wsparcia


1

Jest to najczęściej spotykany problem dla prawie wszystkich administratorów baz danych, w których dzienniki rosną i wypełniają dysk.

• Jakie są powody, dla których dziennik transakcji rośnie tak duży?

  1. Długa aktywna transakcja
  2. Wysokie transakcje rejestrowania, takie jak przebudowa indeksu, reorganizacja, wstawianie zbiorcze, usuwanie itp.
  3. Każda replikacja typu HA, skonfigurowana dublowanie, która przechowuje dziennik i nie pozwala na zwolnienie przestrzeni dziennika

• Dlaczego mój plik dziennika jest tak duży?

Sprawdź kolumnę log_reuse_wait_des c w tabeli sys.databases aby dowiedzieć się, co trzyma dzienniki przed obcięciem:

select name, log_reuse_wait_desc 
from sys.databases

• Jakie są sposoby zapobiegania występowaniu tego problemu?

Kopie zapasowe dziennika pomogą kontrolować wzrost dziennika, chyba że jest coś, co powstrzymuje dzienniki przed ponownym użyciem.

• Co mam zrobić, gdy znajduję się na dobrej drodze do znalezienia przyczyny i chcę umieścić plik dziennika transakcji w zdrowym rozmiarze?

Jeśli zidentyfikowałeś, co faktycznie go powoduje, spróbuj to naprawić, jak wyjaśniono poniżej.

https://www.brentozar.com/archive/2016/03/my-favorite-system-column-log_reuse_wait_desc/

Odpowiednio zaplanowane tworzenie kopii zapasowych dzienników jest najlepszym sposobem radzenia sobie ze wzrostem dzienników, chyba że w nietypowej sytuacji.