Jak wyczyścić dziennik transakcji SQL Server?


577

Nie jestem ekspertem od SQL i przypominam sobie o tym za każdym razem, gdy muszę zrobić coś poza podstawami. Mam testową bazę danych, która nie jest duża, ale dziennik transakcji zdecydowanie jest. Jak wyczyścić dziennik transakcji?



3
W Managment Studio powinno być polecenie: „Kliknij, aby zmniejszyć dziennik” i gotowe.
francuski

Odpowiedzi:


748

Zmniejszenie pliku dziennika powinno być naprawdę zarezerwowane dla scenariuszy, w których wystąpił nieoczekiwany wzrost, którego nie spodziewasz się ponownie. Jeśli plik dziennika ponownie wzrośnie do tego samego rozmiaru, nie zostanie wiele osiągnięte przez jego tymczasowe zmniejszenie. Teraz, w zależności od celów odzyskiwania bazy danych, są to działania, które powinieneś podjąć.

Najpierw zrób pełną kopię zapasową

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

Jeśli zależy Ci na odzyskaniu punktu w czasie

(I przez odzyskiwanie punktu w czasie, mam na myśli, że zależy ci na możliwości przywrócenia do czegoś innego niż pełna lub różnicowa kopia zapasowa.)

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

ALTER DATABASE testdb SET RECOVERY FULL;

Nawet jeśli regularnie wykonujesz pełne kopie zapasowe, plik dziennika będzie się powiększał aż do momentu utworzenia kopii zapasowej dziennika - służy to ochronie, aby nie niepotrzebnie pożerać miejsca na dysku. Powinieneś wykonywać te kopie zapasowe dziennika dość często, zgodnie z celami odzyskiwania. Na przykład, jeśli masz regułę biznesową, która mówi, że możesz sobie pozwolić na utratę nie więcej niż 15 minut danych w przypadku awarii, powinieneś mieć zadanie, które tworzy kopię zapasową dziennika co 15 minut. Oto skrypt, który wygeneruje nazwy plików ze znacznikami czasu na podstawie bieżącego czasu (ale możesz to również zrobić z planami konserwacji itp., Po prostu nie wybieraj żadnych opcji zmniejszania w planach konserwacji, są one okropne).

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

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

Pamiętaj, że \\backup_share\powinien znajdować się na innym komputerze, który reprezentuje inne podstawowe urządzenie pamięci masowej. Tworzenie kopii zapasowej na tym samym komputerze (lub na innym komputerze, który używa tych samych dysków bazowych lub innej maszyny wirtualnej, która znajduje się na tym samym hoście fizycznym) tak naprawdę nie pomaga, ponieważ jeśli komputer się wysadzi, stracisz bazę danych i jego kopie zapasowe. W zależności od infrastruktury sieci bardziej sensowne może być tworzenie kopii zapasowych lokalnie, a następnie przenoszenie ich do innej lokalizacji za kulisami; w obu przypadkach chcesz je jak najszybciej usunąć z podstawowej maszyny bazy danych.

Teraz, gdy masz już regularne kopie zapasowe dziennika, rozsądne jest zmniejszenie pliku dziennika do czegoś bardziej sensownego niż to, co jest wysadzane do tej pory. Ten sposób nie oznacza, uruchomiony SHRINKFILEw kółko, aż plik dziennika jest 1 MB - nawet jeśli kopii zapasowej dziennika często, to nadal musi pomieścić sumę wszelkich współbieżnych transakcji, które mogą wystąpić. Zdarzenia autogrow pliku dziennika są kosztowne, ponieważ SQL Server musi wyzerować pliki (w przeciwieństwie do plików danych, gdy włączona jest natychmiastowa inicjalizacja pliku), a transakcje użytkownika muszą czekać, aż to nastąpi. Chcesz, aby procedura ta była jak najmniejsza, a na pewno nie chcesz, aby użytkownicy płacili za nią.

Pamiętaj, że może być konieczne dwukrotne utworzenie kopii zapasowej dziennika, zanim możliwe będzie zmniejszenie (dzięki Robert).

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 ponownie rośnie, dobry znak wodny jest prawdopodobnie o 10-50% wyższy niż największy, jaki był . Powiedzmy, że dochodzi do 200 MB i chcesz, aby każde kolejne zdarzenie automatycznego wzrostu wynosiło 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 go w pierwszej kolejności:

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

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

Jeśli jest to testowa baza danych i nie obchodzi Cię odzyskiwanie w określonym momencie, upewnij się, że baza danych znajduje się w SIMPLEtrybie odzyskiwania.

ALTER DATABASE testdb SET RECOVERY SIMPLE;

Przełączenie bazy danych w SIMPLEtryb odzyskiwania sprawi, że SQL Server ponownie użyje części pliku dziennika (zasadniczo wycofując nieaktywne transakcje) zamiast rosnąć, aby prowadzić rejestr wszystkich transakcji (podobnie jak FULLodzyskiwanie do momentu utworzenia kopii zapasowej dziennika). CHECKPOINTzdarzenia pomogą kontrolować dziennik i upewnią się, że nie musi on rosnąć, chyba że wygenerujesz dużo aktywności t-log między CHECKPOINTs.

Następnie należy bezwzględnie upewnić się, że ten wzrost dziennika był naprawdę spowodowany nienormalnym zdarzeniem (powiedzmy, corocznym wiosennym czyszczeniem lub odbudową największych indeksów), a nie normalnym, codziennym użyciem. Jeśli zmniejszysz plik dziennika do absurdalnie małego rozmiaru, a program SQL Server po prostu go powiększy, aby dostosować się do normalnej aktywności, co zyskałeś? Czy byłeś w stanie wykorzystać to miejsce na dysku, które 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
DBCC SHRINKFILE(yourdb_log, 200); -- unit is set in MBs
GO

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

Niektórych rzeczy nie chcesz robić

  • Utwórz kopię zapasową dziennika z TRUNCATE_ONLYopcją, a następnieSHRINKFILE . Po pierwsze, ta TRUNCATE_ONLYopcja jest przestarzała i nie jest już dostępna w bieżących wersjach SQL Server. Po drugie, jeśli jesteś w FULLmodelu odzyskiwania, spowoduje to zniszczenie łańcucha dzienników i będzie wymagać nowej, pełnej kopii zapasowej.

  • Odłącz bazę danych, usuń plik dziennika i podłącz ponownie . Nie mogę podkreślić, jak niebezpieczne to może być. Twoja baza danych może nie zostać przywrócona, może pojawić się jako podejrzana, konieczne może być przywrócenie kopii zapasowej (jeśli taka istnieje) itp. Itp.

  • Użyj opcji „zmniejsz bazę danych” . DBCC SHRINKDATABASEa opcja planu konserwacji, aby zrobić to samo, to złe pomysły, szczególnie jeśli naprawdę potrzebujesz tylko rozwiązać problem z logiem. Wybierz plik, który chcesz dostosować, i dostosuj go niezależnie, używając DBCC SHRINKFILElub ALTER 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 niektórych scenariuszach i spojrzeć na całą wolną przestrzeń! O ile twoja baza danych nie jest tylko do odczytu (i tak, powinieneś oznaczyć ją jako taką za pomocą ALTER DATABASE), absolutnie doprowadzi to do wielu niepotrzebnych zdarzeń wzrostu, ponieważ dziennik musi uwzględniać bieżące transakcje niezależnie od modelu odzyskiwania. Po co tymczasowo zwalniać to miejsce, aby SQL Server mógł je powoli i boleśnie przywrócić?

  • Utwórz drugi plik dziennika . Zapewni to tymczasową ulgę dla dysku, który zapełnił twój dysk, ale jest to jak próba naprawienia nakłutego płuca za pomocą opaski. Powinieneś poradzić sobie z problematycznym plikiem dziennika zamiast dodawać kolejny potencjalny problem. Poza przekierowaniem niektórych działań dziennika transakcji na inny dysk, drugi plik dziennika naprawdę nic dla ciebie nie robi (w przeciwieństwie do drugiego pliku danych), ponieważ tylko jeden z plików może być używany jednocześnie. Paul Randal wyjaśnia również, dlaczego wiele plików dziennika może cię później ugryźć .

Bądź proaktywny

Zamiast zmniejszać plik dziennika do niewielkiej ilości i pozwalać mu na ciągłe automatyczne rozwijanie się w niewielkim tempie, ustaw go na odpowiednio duży rozmiar (taki, który pomieści sumę największego zestawu jednoczesnych transakcji) i ustaw rozsądny autogrow ustawienie awaryjne, aby nie musiało rosnąć wiele razy, aby zaspokoić pojedyncze transakcje, i aby stosunkowo rzadko rosło podczas normalnych operacji biznesowych.

Najgorsze możliwe ustawienia to wzrost o 1 MB lub wzrost o 10%. Zabawne, że są to ustawienia domyślne dla SQL Server (na co narzekałem i prosiłem o bezskuteczne zmiany ) - 1 MB na pliki danych i 10% na pliki dziennika. Ten pierwszy jest o wiele za mały w dzisiejszych czasach, a drugi za każdym razem prowadzi do coraz dłuższych zdarzeń (powiedzmy, 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. - a przy wolnym we / wy, uwierz mi, naprawdę zauważysz tę krzywą).

Dalsza lektura

Proszę, nie zatrzymuj się tutaj; podczas gdy wiele porad dotyczących zmniejszania plików dziennika jest z natury złe, a nawet potencjalnie katastrofalne, istnieją osoby, którym bardziej zależy na integralności danych niż na zwolnieniu miejsca na dysku.

Wpis na blogu, który napisałem w 2009 r., Kiedy zobaczyłem kilka postów „tutaj, jak zmniejszyć plik dziennika” .

Post na blogu, który Brent Ozar napisał cztery lata temu, wskazując na wiele zasobów, w odpowiedzi na artykuł w magazynie SQL Server Magazine, który nie powinien zostać opublikowany .

Wpis na blogu autorstwa Paula Randala wyjaśniający, dlaczego utrzymanie t-log jest ważne i dlaczego nie powinieneś zmniejszać plików danych .

Mike Walsh ma świetną odpowiedź obejmującą niektóre z tych aspektów, w tym powody, dla których możesz nie być w stanie natychmiast zmniejszyć pliku dziennika .


5
Odzyskiwanie w określonym momencie nie jest jedynym powodem korzystania z pełnego modelu odzyskiwania. Głównym powodem jest zapobieganie utracie danych. Twój potencjał utraty danych to długość między kopiami zapasowymi. Jeśli wykonujesz tylko codzienną kopię zapasową, ryzyko utraty danych wynosi 24 godziny. Jeśli następnie dodasz kopie zapasowe dziennika co pół godziny, ryzyko utraty danych wynosi 30 minut. Ponadto kopie zapasowe dziennika są wymagane do wykonania dowolnego fragmentarycznego przywracania (np. Odzyskiwania po uszkodzeniu).
Robert L. Davis,

9
Poza tym jest to najbardziej kompletna i poprawna odpowiedź podana na tej stronie.
Robert L. Davis,

11
Chciałbym również dodać, że czyszczenie dziennika odbywa się poprzez wykonanie kopii zapasowej dziennika (w przypadku pełnego lub zbiorczego odzyskiwania) lub punktu kontrolnego (w prostym odzyskiwaniu). Jeśli jednak znajdujesz się w sytuacji, w której musisz zmniejszyć plik dziennika, to nie wystarczy. Musisz spowodować, aby aktualnie aktywne VLF przełączyły się z powrotem na początek pliku dziennika. Możesz to wymusić w SQL 2008 i nowszych wersjach, wydając dwie kopie zapasowe dziennika lub punkty kontrolne jeden za drugim. Pierwszy usuwa go, a drugi powraca do początku pliku.
Robert L. Davis,

2
@Doug_Ivison, ponieważ w dowolnym momencie zapisy dziennika mogą zostać usunięte. Jaki byłby sens tworzenia kopii zapasowej dziennika, który jest niekompletny? W prostym odzyskiwaniu dziennik jest naprawdę używany tylko do wycofywania transakcji. Gdy transakcja zostanie zatwierdzona lub wycofana, w następnej sekundzie może ona zniknąć z dziennika.
Aaron Bertrand

3
@zinczinc Ok, dziękuję za opinie. Problem, jaki widzę, stawiając odpowiedź na pierwszym miejscu, a wyjaśnienie później, polega na tym, że nigdy nie przeczytają ważnych części. Wykład, z którym utopiłem czytelnika, jest w rzeczywistości o wiele ważniejszy niż odpowiedź na końcu, a tło IMHO, które przedstawiam, jest bardzo ważne przy dokonywaniu tych wyborów. Ale hej, jeśli chcesz przesłać odpowiedź w jednym wierszu, ponieważ uważasz, że jest to lepsze dla PO, skorzystaj z tej części mojej odpowiedzi, aby uzyskać lepszą, z której wszyscy możemy się uczyć.
Aaron Bertrand

201
-- DON'T FORGET TO BACKUP THE DB :D (Check [here][1]) 


USE AdventureWorks2008R2;
GO
-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE AdventureWorks2008R2
SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (AdventureWorks2008R2_Log, 1);
GO
-- Reset the database recovery model.
ALTER DATABASE AdventureWorks2008R2
SET RECOVERY FULL;
GO

Od: DBCC SHRINKFILE (Transact-SQL)

Możesz najpierw wykonać kopię zapasową.


dzięki Maimonides, jest z dokumentów, więc powiedziałbym, że jest najlepszy: P szczególnie, że nie został wymyślony przeze mnie :)
Rui Lima

1
po tym, mój rozmiar dziennika nie zmienił się ani trochę. Czy zrobiłem coś złego?
chester89

1
Takie podejście ustawia typ odzyskiwania na „PEŁNY”, nawet jeśli typ odzyskiwania był wcześniej czymś innym
Vil

1
Działa jak magia! Zauważ, że nazwa pliku dziennika jest w rzeczywistości jego logiczną nazwą (którą można znaleźć w db-> właściwości-> pliki)
Bizhan

2
W tym skrypcie umieściłbym klauzulę BACKUP DATABASE, więc nikt nie zapomina tej części. Mówię to, ponieważ kilka lat temu zmniejszyłem bazę danych na dysku, na którym jest za mało wolnego miejsca. W procesie zmniejszania pliki były coraz większe i zgłaszany był błąd braku miejsca. Wynik: straciłem bazę danych. Na szczęście była to baza danych dziennika, która straciła tolerancję.
Cesar

185

ZASTRZEŻENIE: Przeczytaj uważnie poniższe komentarze i zakładam, że przeczytałeś już zaakceptowaną odpowiedź. Jak powiedziałem prawie 5 lat temu:

jeśli ktoś ma jakieś uwagi do dodania w sytuacjach, gdy NIE jest to odpowiednie lub optymalne rozwiązanie, prosimy o komentarz poniżej


  • Kliknij prawym przyciskiem myszy nazwę bazy danych.

  • Wybierz Zadania → Zmniejsz → Baza danych

  • Następnie kliknij OK!

Zwykle otwieram katalog Eksploratora Windows zawierający pliki bazy danych, aby natychmiast zobaczyć efekt.

Byłem naprawdę zaskoczony, że to zadziałało! Zwykle wcześniej korzystałem z DBCC, ale po prostu próbowałem tego i niczego nie zmniejszyłem, więc wypróbowałem GUI (2005) i zadziałało świetnie - zwalniając 17 GB w 10 sekund

W trybie pełnego odzyskiwania może to nie działać, więc musisz najpierw wykonać kopię zapasową dziennika lub przejść do odzyskiwania prostego, a następnie zmniejszyć plik. [dzięki @onupdatecascade za to]

-

PS: Doceniam to, co niektórzy skomentowali na temat związanych z tym niebezpieczeństw, ale w moim środowisku sam nie miałem żadnych problemów, zwłaszcza że najpierw wykonuję pełną kopię zapasową. Zanim przejdziesz dalej, weź pod uwagę to, jakie jest twoje środowisko i jak wpływa to na twoją strategię tworzenia kopii zapasowych i bezpieczeństwo pracy. Wszystko, co robiłem, to wskazywanie ludziom na funkcję zapewnianą przez Microsoft!


50
W trybie pełnego odzyskiwania może to nie działać, więc musisz najpierw wykonać kopię zapasową dziennika lub przejść do odzyskiwania prostego, a następnie zmniejszyć plik.
onupdatecascade

7
@onupdatecascade - dobre połączenie w sprawie pełnego odzyskiwania. miał inną bazę danych z ogromnym dziennikiem: przełączono na prostą, następnie zmniejszono bazę danych i wróciłem do pełnej. plik dziennika do 500 KB!
Simon_Weaver

26
Przepraszam. Ale ta odpowiedź NIE może być WIĘCEJ błędna. Zmniejszając bazę danych, BĘDZIESZ rozwijać plik dziennika transakcji. W DOWOLNYM momencie przenoszenia danych w bazie danych SQL Server, będziesz musiał się zalogować - nadmuchując plik dziennika. Aby zmniejszyć rozmiar dziennika, ustaw DB na Simple Recovery LUB (jeśli zależy Ci / potrzebujesz zalogowanych danych - i prawie zawsze robisz to w produkcji) - zrób kopię zapasową dziennika. Więcej szczegółów w tych prostych, bezpłatnych filmach: sqlservervideos.com/video/logging-essentials sqlservervideos.com/video/sql2528-log-files
Michael K. Campbell

30
Wow, chwała za uzyskanie ponad 1300 powtórzeń dla tej odpowiedzi, ale to naprawdę okropna rada.
Aaron Bertrand,

8
Oto przesada w celu wykazania, co się dzieje i dlaczego okresowe zmniejszanie jest absolutnie niezbędne: rekord A zmienia się milion razy przed wykonaniem kopii zapasowej. Co jest w dzienniku? 999,999 fragmentów danych, które są nieistotne. Jeśli dzienniki nigdy nie zostaną zmniejszone, nigdy nie wiadomo, jaki jest prawdziwy koszt operacyjny bazy danych. Ponadto najprawdopodobniej gromadzisz cenne zasoby w sieci SAN. Obkurczanie to dobra konserwacja i utrzymuje kontakt z otoczeniem. Pokaż mi kogoś, kto myśli, że nigdy nie powinieneś się kurczyć, a ja pokażę ci kogoś, kto ignoruje swoje otoczenie.
Newclique,

54

Poniżej znajduje się skrypt zmniejszający dziennik transakcji, ale zdecydowanie zalecam utworzenie kopii zapasowej dziennika transakcji przed jego zmniejszeniem.

Jeśli zmniejszysz plik, stracisz mnóstwo danych, które mogą uratować życie w przypadku katastrofy. Dziennik transakcji zawiera wiele przydatnych danych, które można odczytać za pomocą zewnętrznego czytnika dziennika transakcji (można go odczytać ręcznie, ale z dużym wysiłkiem).

Dziennik transakcji jest również konieczny, jeśli chodzi o odzyskiwanie czasu, więc nie wyrzucaj go, ale upewnij się, że wcześniej go utworzyłeś.

Oto kilka postów, w których ludzie korzystali z danych przechowywanych w dzienniku transakcji w celu odzyskania:

 

USE DATABASE_NAME;
GO

ALTER DATABASE DATABASE_NAME
SET RECOVERY SIMPLE;
GO
--First parameter is log file name and second is size in MB
DBCC SHRINKFILE (DATABASE_NAME_Log, 1);

ALTER DATABASE DATABASE_NAME
SET RECOVERY FULL;
GO

Podczas wykonywania powyższych poleceń może pojawić się błąd, który wygląda następująco

„Nie można zmniejszyć pliku dziennika (nazwa pliku dziennika), ponieważ plik dziennika logicznego znajdujący się na końcu pliku jest w użyciu”

Oznacza to, że TLOG jest w użyciu. W takim przypadku spróbuj wykonać to kilka razy z rzędu lub znajdź sposób na ograniczenie aktywności bazy danych.


30

Oto prosty, bardzo nieelegancki i potencjalnie niebezpieczny sposób.

  1. Backup DB
  2. Odłącz DB
  3. Zmień nazwę pliku dziennika
  4. Dołącz DB
  5. Nowy plik dziennika zostanie utworzony ponownie
  6. Usuń zmieniony plik dziennika.

Zgaduję, że nie wykonujesz kopii zapasowych dzienników. (Który obciął dziennik). Radzę zmienić model odzyskiwania z pełnego na prosty . Zapobiegnie to wzdęciom.


15
Z szacunkiem usunięcie / zmiana nazwy / odtworzenie / zastąpienie dziennika jest bardzo złym pomysłem. Kurczenie się musi być mniej ryzykowne, a do tego dość proste.
onupdatecascade

12
+1 - Nieeleganckie czy nie, ta metoda kilkakrotnie wyciągnęła mnie z ciepłej wody z dziennikami bazy danych, które zapełniły cały dysk, tak że nawet polecenie zmniejszania nie może zostać uruchomione.
Shaul Behr

3
Czy w dzienniku nie istnieje ryzyko nieoznaczonych transakcji?
Paul

Może to być również dobre dla mniejszych DB, ale jeśli masz DB 3 lub 4 TB, może nie być najlepszym rozwiązaniem.
Jaques,

Wydaje się to w porządku, jeśli tworzysz system od dłuższego czasu i ładujesz / usuwasz tysiące rekordów w okresie programowania. Jeśli więc chcesz użyć tej bazy danych do wdrożenia w życie, zarejestrowane dane testowe / programistyczne są zbędne i dlatego nie ma znaczenia, czy zostaną utracone, prawda?
JsonStatham,

28

Jeśli nie używasz dzienników transakcji do przywracania (tj. Wykonujesz tylko pełne kopie zapasowe), możesz ustawić tryb odzyskiwania na „Prosty”, a dziennik transakcji bardzo krótko się skurczy i nigdy więcej się nie wypełni.

Jeśli używasz SQL 7 lub 2000, możesz włączyć opcję „obcinaj dziennik w punkcie kontrolnym” na karcie opcji bazy danych. Ma to ten sam efekt.

Nie jest to oczywiście zalecane w środowiskach produkcyjnych, ponieważ nie będzie można przywrócić do określonego momentu.


1
Ustawienie prostego trybu odzyskiwania nie spowoduje magicznego zmniejszenia dziennika transakcji.
Aaron Bertrand,

@Aaron Nie jest sam, nie. Zakładałem, że OP użyje ich testowej bazy danych i dlatego „dziennik transakcji bardzo krótko się zmniejszy”, ale masz rację, ponieważ jest to raczej efekt uboczny: prosty tryb odzyskiwania prawdopodobnie spowoduje, że skończysz się skurczony dziennik transakcji wkrótce
Jonathan

Simple... i nigdy więcej nie wypełniaj” - nieprawda. Widziałem, jak to się dzieje (w ciągu ostatnich 48 godzin) w bazie danych, w której Model odzyskiwania został ustawiony na „SIMPLE”. Wzrost pliku dziennika został ustawiony na „ograniczony”, a my robiliśmy na nim ogromną aktywność ... Rozumiem, że to była niezwykła sytuacja. (W naszej sytuacji, gdy mieliśmy dużo miejsca na dysku, zwiększyliśmy rozmiar pliku dziennika i ustawiliśmy wzrost pliku dziennika na „nieograniczony” ... który, nawiasem mówiąc, błąd interfejsu - po zmianie wyświetla się jako „ograniczony „o maksymalnym rozmiarze 2 097 152 MB.)
Doug_Ivison,

2
@Doug_Ivison Tak, w dzienniku transakcji będą znajdować się otwarte transakcje, ale zostaną one usunięte w trybie prostym po przejściu punktu kontrolnego. Ta odpowiedź jest przeznaczona tylko jako szybkie „moje pudełko do programowania / testowania ma duży dziennik transakcji i chcę, aby zniknęło, więc nie muszę się o to zbyt często przejmować”, a nie przeznaczone do produkcji środowisko. Aby powtórzyć: Nie rób tego podczas produkcji .
Jonathan

1
To wszystko prawda i rozumiem, że było to szybkie podejście tylko do rozwoju. Dlaczego skomentowałem: dopóki nie przydarzyło mi się to, tak naprawdę myślałem, że prosty model odzyskiwania NIGDY nie może się wypełnić ... i myślę, że zajęło mi więcej czasu, aby dowiedzieć się / rozwiązać, podczas gdy zrozumiałem, że niezwykle duże transakcje mogą to zrobić.
Doug_Ivison

9

Ta technika, którą zaleca John, nie jest zalecana, ponieważ nie ma gwarancji, że baza danych zostanie dołączona bez pliku dziennika. Zmień bazę danych z pełnej na prostą, wymuś punkt kontrolny i poczekaj kilka minut. Serwer SQL wyczyści dziennik, który można następnie zmniejszyć za pomocą DBCC SHRINKFILE.


... ale zrobiłem to dziesiątki razy bez problemu. być może mógłbyś wyjaśnić, dlaczego db nie może ponownie dołączyć.
Johnno Nolan,

Czasami (niezbyt często) widziałem, że SQL Server nie może ponownie dołączyć bazy danych do bazy danych, gdy plik dziennika został usunięty. To pozostawia bezużyteczny plik MDF. Istnieje kilka możliwości, które mogą powodować problem. Przychodzą na myśl transakcje oczekujące na wycofanie.
mrdenny,

4
Zgadzam się z tą taktyką, ale powinna być zarezerwowana dla przypadków, w których dziennik wysadził się z powodu jakiegoś nieprzewidzianego i / lub nadzwyczajnego zdarzenia. Jeśli ustawiasz zadanie, aby robić to co tydzień, robisz to bardzo, bardzo źle.
Aaron Bertrand,

2
Bardzo, bardzo prawdziwy Aaron.
mrdenny,

Tak, to właśnie nam się przydarzyło. Chcieliśmy porzucić 20G pliku dziennika, ponieważ właśnie wykonaliśmy kopię zapasową danych przed przeniesieniem bazy danych. W żaden sposób MSSQL nie pozwoliłby nam ponownie dołączyć nowej bazy danych bez ogromnego pliku dziennika.
George M przywraca Monikę

9

Większość odpowiedzi tutaj zakłada, że ​​tak naprawdę nie potrzebujesz pliku dziennika transakcji, jednak jeśli baza danych korzysta z FULLmodelu odzyskiwania i chcesz zachować kopie zapasowe na wypadek konieczności przywrócenia bazy danych, nie musisz obcinaj ani nie usuwaj plik dziennika, jak sugeruje wiele z tych odpowiedzi.

Usunięcie pliku dziennika (poprzez obcięcie go, usunięcie, usunięcie itp.) Spowoduje przerwanie łańcucha kopii zapasowych i uniemożliwi przywrócenie do dowolnego momentu od ostatniej pełnej kopii zapasowej, różnicowej lub dziennika transakcji do momentu następnego pełnego lub tworzona jest różnicowa kopia zapasowa.

Z artykułu Microsoft naBACKUP

Zalecamy, aby nigdy nie używać NO_LOG lub TRUNCATE_ONLY do ręcznego obcinania dziennika transakcji, ponieważ powoduje to przerwanie łańcucha dziennika. Do następnej pełnej lub różnicowej kopii zapasowej bazy danych baza danych nie jest chroniona przed awarią nośnika. Ręcznego obcinania dziennika należy używać tylko w bardzo szczególnych okolicznościach i natychmiast tworzyć kopie zapasowe danych.

Aby tego uniknąć, wykonaj kopię zapasową pliku dziennika na dysk przed jego zmniejszeniem. Składnia wyglądałaby mniej więcej tak:

BACKUP LOG MyDatabaseName 
TO DISK='C:\DatabaseBackups\MyDatabaseName_backup_2013_01_31_095212_8797154.trn'

DBCC SHRINKFILE (N'MyDatabaseName_Log', 200)

3
Zgadzam się z twoją odpowiedzią, z wyjątkiem , 1)części. Problem polega na tym, że jeśli zmniejszysz go do 1 MB, zdarzenia wzrostu prowadzące do normalnego rozmiaru dziennika będą dość kosztowne i będzie ich wiele, jeśli tempo wzrostu pozostanie domyślnie 10%.
Aaron Bertrand,

9

Dziennik transakcji SQL Server musi być odpowiednio utrzymywany, aby zapobiec jego niechcianemu wzrostowi. Oznacza to, że dość często wykonuje się kopie zapasowe dziennika transakcji. W przeciwnym razie ryzykujesz, że dziennik transakcji się zapełni i zaczniesz rosnąć.

Oprócz odpowiedzi na to pytanie polecam przeczytanie i zrozumienie częstych mitów dziennika transakcji. Te odczyty mogą pomóc zrozumieć dziennik transakcji i zdecydować, jakich technik użyć, aby go „wyczyścić”:

Z 10 najważniejszych mitów dziennika transakcji SQL Server :

Mit: Mój SQL Server jest zbyt zajęty. Nie chcę tworzyć kopii zapasowych dziennika transakcji SQL Server

Jedną z największych operacji wymagających dużej wydajności w programie SQL Server jest automatyczne powiększanie pliku dziennika transakcji online. Jeśli kopia zapasowa dziennika transakcji nie będzie wystarczająca, dziennik transakcji online będzie pełny i będzie musiał się powiększać. Domyślna wielkość wzrostu wynosi 10%. Im bardziej zajęta jest baza danych, tym szybciej dziennik transakcji online wzrośnie, jeśli nie zostaną utworzone kopie zapasowe dziennika transakcji. Utworzenie kopii zapasowej dziennika transakcji SQL Server nie blokuje dziennika transakcji online, ale zdarzenie automatycznego wzrostu. Może blokować całą aktywność w dzienniku transakcji online

Z mitów dziennika transakcji :

Mit: Regularne obkurczanie kłód jest dobrą praktyką konserwacyjną

FAŁSZYWE. Wzrost kłód jest bardzo kosztowny, ponieważ nowy fragment musi zostać wyzerowany. Cała aktywność zapisu zatrzymuje się w tej bazie danych, dopóki nie zakończy się zerowanie, a jeśli zapis na dysku jest wolny lub rozmiar autogrowth jest duży, pauza może być ogromna, a użytkownicy zauważą. To jeden z powodów, dla których chcesz uniknąć wzrostu. Jeśli zmniejszysz dziennik, odrośnie on ponownie i po prostu marnujesz pracę dysku na niepotrzebną grę zmniejszania i wzrostu


5

Najpierw sprawdź model odzyskiwania bazy danych. Domyślnie SQL Server Express Edition tworzy bazę danych dla prostego modelu odzyskiwania (jeśli się nie mylę).

Kopia zapasowa dziennika nazwa_bazy_danych Z Truncate_Only:

DBCC ShrinkFile(yourLogical_LogFileName, 50)

SP_helpfile poda logiczną nazwę pliku dziennika.

Odnosić się do:

Odzyskaj z pełnego dziennika transakcji w bazie danych SQL Server

Jeśli baza danych jest w trybie pełnego odzyskiwania i nie wykonujesz kopii zapasowej TL, zmień ją na SIMPLE.


W ten sposób usuwam pliki dziennika z moich skrzynek programistycznych. Środowiska produkcyjne ze wszystkimi powiązanymi strategiami tworzenia kopii zapasowych itp. Zostawiam DBA :-)
Joon

TRUNCATE_ONLYnie jest już opcją w obecnych wersjach SQL Server i nie jest zalecane w wersjach, które ją obsługują (patrz odpowiedź Rachel ).
Aaron Bertrand,

5

Użyj DBCC ShrinkFile ({logicalLogName}, TRUNCATEONLY) polecenia. Jeśli jest to testowa baza danych i próbujesz zaoszczędzić / odzyskać miejsce, to pomoże.

Pamiętaj jednak, że dzienniki TX mają rodzaj minimalnego / stałego rozmiaru, do którego będą dorastać. W zależności od modelu odzyskiwania możesz nie być w stanie zmniejszyć dziennika - jeśli w trybie PEŁNYM i nie wydajesz kopii zapasowych dziennika TX, dziennik nie może zostać zmniejszony - będzie się powiększał na zawsze. Jeśli nie potrzebujesz kopii zapasowych dziennika TX, zmień model odzyskiwania na Prosty .

I pamiętaj, że nigdy w żadnym wypadku nie usuwaj pliku dziennika (LDF)! Będziesz mieć niemal natychmiastowe uszkodzenie bazy danych. Gotowany! Gotowy! Utracone dane! Jeśli pozostanie „nie naprawiony”, główny plik MDF może zostać trwale uszkodzony.

Nigdy nie usuwaj dziennika transakcji - stracisz dane! Część danych znajduje się w dzienniku TX (niezależnie od modelu odzyskiwania) ... jeśli odłączysz i „zmienisz nazwę” pliku dziennika TX, który skutecznie usuwa część Twojej bazy danych.

Dla tych, którzy usunęli Dziennik TX, możesz chcieć uruchomić kilka poleceń checkdb i naprawić uszkodzenie, zanim stracisz więcej danych.

Sprawdź posty na blogu Paula Randala na ten temat, zła rada .

Zasadniczo nie należy również używać pliku kurczącego się na plikach MDF, ponieważ może to poważnie rozdrobnić dane. Więcej informacji znajdziesz w jego sekcji Złe porady („Dlaczego nie powinieneś zmniejszać plików danych”)

Sprawdź stronę internetową Paula - on odpowiada na te pytania. W zeszłym miesiącu omówił wiele z tych zagadnień w swojej serii Myth A Day .


+1 Za bycie pierwszą odpowiedzią, która wspomina, że ​​może to nie być dobry pomysł! OP określa testową bazę danych, ale warto zwrócić uwagę na bardziej ogólny przypadek.
Martin Smith

Powinienem dodać - Jeśli usuniesz dziennik TX - Zaktualizuj CV!
ripvlan,

4

Aby obciąć plik dziennika:

  • Wykonaj kopię zapasową bazy danych
  • Odłącz bazę danych, używając Enterprise Manager lub wykonując: Sp_DetachDB [DBName]
  • Usuń plik dziennika transakcji. (lub zmień nazwę pliku, na wszelki wypadek)
  • Ponownie podłącz bazę danych, używając: Sp_AttachDB [DBName]
  • Po podłączeniu bazy danych tworzony jest nowy plik dziennika transakcji.

Aby zmniejszyć plik dziennika:

  • Kopia zapasowa dziennika [DBName] z No_Log
  • Zmniejsz bazę danych:

    Za pomocą Menedżera przedsiębiorstwa: - Kliknij prawym przyciskiem myszy bazę danych, Wszystkie zadania, Zmniejsz bazę danych, Pliki, Wybierz plik dziennika, OK.

    Za pomocą T-SQL: - Dbcc Shrinkfile ([Log_Logical_Name])

Możesz znaleźć logiczną nazwę pliku dziennika, uruchamiając sp_helpdb lub przeglądając właściwości bazy danych w Enterprise Manager.


Nigdy nie usuwaj dziennika transakcji. Część twoich danych znajduje się w Dzienniku. Usuń go, a baza danych ulegnie uszkodzeniu. Nie mam przedstawiciela, który odmówiłby głosowania.
ripvlan,

4

Zgodnie z moim doświadczeniem na większości serwerów SQL nie ma kopii zapasowej dziennika transakcji. Pełne kopie zapasowe lub różnicowe kopie zapasowe są powszechną praktyką, ale kopie zapasowe dziennika transakcji są naprawdę rzadkie. Tak więc plik dziennika transakcji rośnie wiecznie (aż do zapełnienia dysku). W takim przypadku model odzyskiwania należy ustawić na „ prosty ”. Nie zapomnij również zmodyfikować systemowych baz danych „model” i „tempdb”.

Tworzenie kopii zapasowej bazy danych „tempdb” nie ma sensu, dlatego model odzyskiwania tej bazy danych powinien być zawsze „prosty”.


Samo ustawienie bazy danych na prostą nie zmniejszy pliku dziennika, niezależnie od tego, w jakim jest aktualnie stanie. Może to pomóc zapobiec dalszemu wzrostowi (ale nadal może).
Aaron Bertrand,

4
  1. Zrób kopię zapasową pliku MDB.
  2. Zatrzymaj usługi SQL
  3. Zmień nazwę pliku dziennika
  4. Uruchom usługę

(System utworzy nowy plik dziennika).

Usuń lub przenieś plik dziennika o zmienionej nazwie.


3

Zdarzyło mi się, że plik dziennika bazy danych miał 28 GB.

Co możesz zrobić, aby to zmniejszyć? W rzeczywistości pliki dziennika to te dane, które serwer SQL przechowuje po przeprowadzeniu transakcji. W celu przetworzenia transakcji SQL Server przydziela strony dla tego samego. Ale po zakończeniu transakcji nie są one nagle uwalniane w nadziei, że może dojść do takiej transakcji. To utrzymuje przestrzeń.

Krok 1: Najpierw uruchom tę komendę w zbadanym punkcie kontrolnym zapytania do bazy danych

Krok 2: Kliknij prawym przyciskiem myszy bazę danych Zadanie> Utwórz kopię zapasową Wybierz typ kopii zapasowej jako Dziennik transakcji Dodaj adres docelowy i nazwę pliku, aby zachować dane kopii zapasowej (.bak)

Powtórz ten krok jeszcze raz i podaj inną nazwę pliku

wprowadź opis zdjęcia tutaj

Krok 3: Teraz przejdź do bazy danych Kliknij bazę danych prawym przyciskiem myszy

Zadania> Zmniejszenia> Pliki Wybierz Typ pliku jako akcję Zmniejsz dziennik jako zwolnij nieużywane miejsce

wprowadź opis zdjęcia tutaj

Krok 4:

Sprawdź swój plik dziennika normalnie w SQL 2014, można go znaleźć pod adresem

C: \ Program Files \ Microsoft SQL Server \ MSSQL12.MSSQL2014EXPRESS \ MSSQL \ DATA

W moim przypadku jego pojemność została zmniejszona z 28 GB do 1 MB


2

Spróbuj tego:

USE DatabaseName

GO

DBCC SHRINKFILE( TransactionLogName, 1)

BACKUP LOG DatabaseName WITH TRUNCATE_ONLY

DBCC SHRINKFILE( TransactionLogName, 1)

GO 

TRUNCATE_ONLYnie jest już opcją w obecnych wersjach SQL Server i nie jest zalecane w wersjach, które ją obsługują (patrz odpowiedź Rachel ).
Aaron Bertrand,

Działa dla mnie przy użyciu MSSQL Server 2005 Standard Edition
bksi

2

Baza danych → kliknij prawym przyciskiem myszy Właściwości → plik → dodaj kolejny plik dziennika o innej nazwie i ustaw ścieżkę tak samo jak stary plik dziennika o innej nazwie.

Baza danych automatycznie pobiera nowo utworzony plik dziennika.


1
  1. Backup DB
  2. Odłącz DB
  3. Zmień nazwę pliku dziennika
  4. Dołącz DB (podczas dołączania usuń przemianowaną nazwę .ldf (plik dziennika). Wybierz i usuń, naciskając przycisk Usuń)
  5. Nowy plik dziennika zostanie utworzony ponownie
  6. Usuń zmieniony plik dziennika.

To zadziała, ale zaleca się najpierw wykonać kopię zapasową bazy danych.


1

Niektóre inne odpowiedzi nie działały dla mnie: nie można było utworzyć punktu kontrolnego, gdy db był w trybie online, ponieważ dziennik transakcji był pełny (jak na ironię). Jednak po ustawieniu bazy danych w tryb awaryjny udało mi się zmniejszyć plik dziennika:

alter database <database_name> set emergency;
use <database_name>;
checkpoint;
checkpoint;
alter database <database_name> set online;
dbcc shrinkfile(<database_name>_log, 200);

0

Przykład:

DBCC SQLPERF(LOGSPACE)

BACKUP LOG Comapny WITH TRUNCATE_ONLY

DBCC SHRINKFILE (Company_log, 500)

DBCC SQLPERF(LOGSPACE)

TRUNCATE_ONLYnie jest już opcją w obecnych wersjach SQL Server i nie jest zalecane w wersjach, które ją obsługują (patrz odpowiedź Rachel ).
Aaron Bertrand,

Pytanie nie dotyczy tematu Przepełnienie stosu zdefiniowanego w Centrum pomocy . Proszę nie odpowiadać na takie pytania; zamiast tego powinieneś oznaczyć je jako uwagę, a zostaną one odpowiednio zamknięte lub migrowane.
Toby Speight

0

Nieznacznie zaktualizowana odpowiedź dla MSSQL 2017 i korzystania ze studia zarządzania serwerem SQL. Przeszedłem głównie z tych instrukcji https://www.sqlshack.com/sql-server-transaction-log-backup-truncate-and-shrink-operations/

Miałem ostatnią kopię zapasową bazy danych, więc utworzyłem kopię zapasową dziennika transakcji. Potem wykonałem kopię zapasową dla większej miary. W końcu zmniejszyłem plik dziennika i zwiększyłem rozmiar z 20G do 7 MB, co jest znacznie bardziej zgodne z rozmiarem moich danych. Nie sądzę, aby kopie zapasowe dzienników transakcji były tworzone od czasu zainstalowania 2 lata temu ... więc umieszczenie tego zadania w kalendarzu sprzątania.


-1

Dziennik transakcji DB Zmniejsz do rozmiaru min :

  1. Kopia zapasowa: dziennik transakcji
  2. Zmniejsz pliki: dziennik transakcji
  3. Kopia zapasowa: dziennik transakcji
  4. Zmniejsz pliki: dziennik transakcji

Przeprowadziłem testy na kilku DB: ta sekwencja działa .

Zwykle zmniejsza się do 2 MB .

LUB według skryptu:

DECLARE @DB_Name nvarchar(255);
DECLARE @DB_LogFileName nvarchar(255);
SET @DB_Name = '<Database Name>';               --Input Variable
SET @DB_LogFileName = '<LogFileEntryName>';         --Input Variable
EXEC 
(
'USE ['+@DB_Name+']; '+
'BACKUP LOG ['+@DB_Name+'] WITH TRUNCATE_ONLY ' +
'DBCC SHRINKFILE( '''+@DB_LogFileName+''', 2) ' +
'BACKUP LOG ['+@DB_Name+'] WITH TRUNCATE_ONLY ' +
'DBCC SHRINKFILE( '''+@DB_LogFileName+''', 2)'
)
GO

1
TRUNCATE_ONLYnie jest już opcją w obecnych wersjach SQL Server i nie jest zalecane w wersjach, które ją obsługują (patrz odpowiedź Rachel ).
Aaron Bertrand,
Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.