Ponieważ nie jestem do końca zadowolony z żadnej z odpowiedzi na Stack Overflow , w tym z najbardziej pozytywnie ocenionej sugestii, a ponieważ jest kilka rzeczy, na które chciałbym się odnieść, odpowiedź Mike'a nie, pomyślałam, że udzielę mój wkład tutaj również. Tam też umieściłem kopię tej odpowiedzi.
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 yourdb 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 pozwolić sobie na utratę nie mniej 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\yourdb_'
+ CONVERT(CHAR(8), GETDATE(), 112) + '_'
+ REPLACE(CONVERT(CHAR(8), GETDATE(), 108),':','')
+ '.trn';
BACKUP LOG foo TO DISK = @path WITH INIT, COMPRESSION;
Należy pamiętać, że \\backup_share\powinien znajdować się na innym komputerze, który reprezentuje inne podstawowe urządzenie pamięci masowej. Tworzenie kopii zapasowych 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 dzienników, 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, a chcesz, aby wszelkie kolejne zdarzenia automatycznego wzrostu wynosił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 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 yourdb 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
-- 200 MB
DBCC SHRINKFILE(yourdb_log, 200);
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ć ponownie uruchomiona, 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 określonych 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 to jest jak próba naprawienia nakłutego płuca opaską. 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 równoczesnych 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, 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ź powyżej, obejmującą również niektóre z tych aspektów, w tym powody, dla których możesz nie być w stanie natychmiast zmniejszyć pliku dziennika .
