Jak najlepiej utrzymywać rozmiary plików dziennika SQL

Jestem trochę nowym DBA i zarządzam instancją SQL Server 2012, która ma spory zakres aktywności. Pracuję w trybie pełnego odzyskiwania, ponieważ potrzebujemy odzyskiwania w określonym czasie.

Obecnie robię pełną kopię zapasową baz danych i dzienników codziennie o 5 rano. Niektóre pliki dzienników szybko się zwiększyły do ​​300 GB, a nawet po wykonaniu kopii zapasowej nie zmniejszają rozmiaru. Mogę zmusić je do zmniejszenia rozmiaru, uruchamiając coś podobnego do:

BACKUP LOG db1 TO DISK = '\\server\share\db1_log1.trn';
DBCC ShrinkFile([db1_log], 0);

BACKUP LOG db1 TO DISK = '\\server\share\db1_log2.trn';
DBCC ShrinkFile([db1_log], 0);

BACKUP LOG db1 TO DISK = '\\server\share\db1_log3.trn';
DBCC ShrinkFile([db1_log], 0);

Kiedy sprawdzam LSN plików kopii zapasowej, widzę coś takiego:

RESTORE headeronly FROM DISK = N'\\server\share\db1_log1.trn'
FirstLSN:  15781000014686200001
SecondLSN: 15802000000665000001

RESTORE headeronly FROM DISK = N'\\server\share\db1_log2.trn'
FirstLSN:  15802000000665000001
SecondLSN: 15805000000004100001

RESTORE headeronly FROM DISK = N'\\server\share\db1_log3.trn'
FirstLSN:  15805000000004100001
SecondLSN: 15808000000004200001

Nie sądzę, że zrywam łańcuch dziennika, zmniejszając pliki dziennika. Czytając o tym, sądzę, że szkodzę mojej wydajności, ponieważ te skurczone pliki dziennika muszą się odrodzić.

Pytania:

1. Dlaczego plik dziennika nie kurczy się po moich kopiach zapasowych? Czy to dlatego, że istnieją niezaangażowane transakcje?
2. Na początku myślałem, że powinienem zmniejszać pliki dziennika po każdej kopii zapasowej o 5:00 rano. Po przeczytaniu, jak to źle wpływa na wydajność, teraz uważam, że muszę regularnie wykonywać kopie zapasowe dziennika co kilka godzin w ciągu dnia. Czy to jest poprawne?
3. Moja normalna pełna kopia zapasowa bazy danych / dzienników odbywa się codziennie o 5:00 rano, a czasem zajmuje 3 godziny. Jeśli zaplanuję tworzenie kopii zapasowych dziennika co godzinę, co się stanie, gdy kopia zapasowa dziennika zderzy się z kopią zapasową o 5:00 rano?

1. Dlaczego plik dziennika nie kurczy się po moich kopiach zapasowych? Czy to dlatego, że istnieją niezaangażowane transakcje?

Rzeczywisty plik dziennika NTFS nie „kurczy się” z kopii zapasowej dziennika transakcji, ale VLF (wirtualne pliki dziennika) w dzienniku transakcji są oznaczone do ponownego użycia (ponieważ są teraz tworzone i archiwizowane na nośniku), umożliwiając owijanie wykorzystanie dziennika transakcji do wystąpienia. Jeśli kopia zapasowa dziennika transakcji nie jest tworzona lub nie występuje ona wystarczająco często, nie będzie dostępnych VLF, co spowoduje wzrost dziennika transakcji (pod warunkiem, że ustawiony jest autogrowth) w celu uwzględnienia dodatkowych wpisów dziennika transakcji.

2.Na początku myślałem, że powinienem zmniejszać pliki dziennika po każdej kopii zapasowej o 5:00 rano. Po przeczytaniu, jak to źle wpływa na wydajność, teraz uważam, że muszę regularnie wykonywać kopie zapasowe dziennika co kilka godzin w ciągu dnia. Czy to jest poprawne?

Rutynowe i zaplanowane kurczenie się plików nie jest dobrym pomysłem. Tylko wtedy, gdy musisz odzyskać bardzo potrzebne miejsce, powinieneś rozważyć DBCC SHINKFILE. Ponadto, gdy stale powiększasz dziennik transakcji, możesz utrudniać inne rzeczy, takie jak odzyskiwanie bazy danych. Przy zbyt dużej liczbie VLF w dzienniku transakcji (powszechny problem, gdy dziennik transakcji jest powiększany tylko o niewielki przyrost pamięci), czas na odzyskanie bazy danych może być dłuższy niż jest to pożądane.

3. Moja normalna pełna kopia zapasowa bazy danych / dzienników odbywa się codziennie o 5:00 rano, a czasem zajmuje 3 godziny. Jeśli zaplanuję tworzenie kopii zapasowych dziennika co godzinę, co się stanie, gdy kopia zapasowa dziennika zderzy się z kopią zapasową o 5:00 rano?

Nic się nie wydarzy, to całkowicie legalna operacja. Zobacz poniższy wykres z MSDN . Tam, gdzie jest czarna kropka, te dwie operacje nie mogą wystąpić jednocześnie. Jak widać, kopia zapasowa bazy danych i dziennik transakcji są dozwolone jednocześnie.

Na wynos tutaj powinieneś częściej tworzyć kopie zapasowe dziennika transakcji. Wzrost plików NTFS to nie jedyny problem, na który możesz natknąć się, nie tworząc częstszej kopii zapasowej dziennika transakcji. Jeśli wystąpi awaria pamięci, a dziennik transakcji zostanie utracony, można przywrócić tylko do momentu, w którym utworzono kopię zapasową ostatniego dziennika transakcji. Jeśli dziennik transakcji zostanie utracony, nie będzie można wykonać kopii zapasowej ogona dziennika i przywrócić go do momentu wystąpienia awarii. W twoim przypadku możesz potencjalnie stracić dane o wartości 24 godzin. Ale jeśli wykonasz kopię zapasową dzienników transakcji co, powiedzmy, 30 minut, maksymalna utrata danych wyniesie 30 minut. W takim przypadku, jeśli Twój dziennik transakcji zniknął, a masz pełną kopię zapasową i nienaruszony łańcuch dziennika, możesz przywrócić ją do ostatniej kopii zapasowej dziennika.

Dokumentacja TechNet na temat obcinania dziennika transakcji

Głównym problemem, z którym masz do czynienia, jest tworzenie kopii zapasowych dzienników raz dziennie. Zachowanie silnika polega na tym, że rekordy dziennika (zajęte miejsce) w pliku dziennika zostaną usunięte dopiero po pomyślnym utworzeniu kopii zapasowej dziennika. To miejsce jest odzyskiwane, gdy pojawia się punkt kontrolny , ale jeśli twoja baza danych jest w trybie pełnego / masowego odzyskiwania, rekordy dziennika zostaną usunięte tylko wtedy, gdy zostanie pomyślnie utworzona kopia zapasowa.

Kopie zapasowe dziennika są przeznaczone do użycia w połączeniu z pełnymi kopiami zapasowymi i powinny być uruchamiane w regularnych odstępach czasu między pełnymi kopiami zapasowymi. Ten przedział może być dowolnym okresem, chociaż zwykle wykonuję kopie zapasowe dziennika co 15 minut. Interwał zależy od celu punktu przywracania (RPO) i ilości danych, które możesz utracić w przypadku odzyskania.

Jeśli wykonujesz regularne kopie zapasowe dziennika, nie powinieneś wykonywać regularnych zmniejszeń plików, ponieważ będziesz zarządzał przestrzenią plików dziennika, zanim będzie ona musiała się powiększać.

Mam taki sam problem jak ty wcześniej. Mój plik dziennika zawsze rośnie, zamiast tego korzystam z pełnej kopii zapasowej bazy danych każdej nocy. Oto moje rozwiązanie:

1. wykonaj kopię zapasową bieżącego pliku dziennika.

2. Ustaw bazę danych na Proste odzyskiwanie

   • Rozpoczęcie pełnego odzyskiwania -> Plik dziennika nie usuwa zatwierdzonej transakcji, zmienia jedynie porządek danych -> Plik Shink nie wpływa na wiele -> i odwrotnie w przypadku prostego odzyskiwania.

3. Pomiń plik dziennika do 1 MB lub mniej (to zależy od Ciebie)

4. Przywróć bazę danych do pełnego odzyskiwania.

Mam nadzieję, że to pomoc

Phong Tran