W jaki sposób zmniejszanie pliku dziennika programu SQL Server wpływa na wydajność?

Mam bazę danych SQL Server 2008, która ma plik danych o wielkości około 2 GB, ale plik dziennika ma ponad 8 GB. W przypadku baz danych sprzed 2008 r. Mogłem skorzystać z „Dziennika kopii zapasowych” i TRUNCATE_ONLYopcji, ale nie jest to już dostępne w przypadku baz danych z 2008 r. I późniejszych.

Mam skrypt, który obcina plik dziennika:

USE [MyDatabase]
GO
ALTER DATABASE [MyDatabase] SET RECOVERY SIMPLE WITH NO_WAIT
DBCC shrinkfile('MyDatabase_log', 1)
ALTER DATABASE [MyDatabase] SET RECOVERY FULL WITH NO_WAIT
GO

To całkowicie obcina plik dziennika, ale moje pytanie brzmi: czy to wpływa na wydajność?

Codziennie wykonuję dwie Pełne kopie zapasowe, więc dziennik nie powinien być tak naprawdę potrzebny w zakresie przenoszenia danych.

Naprawdę polecam lekturę Znaczenie właściwego zarządzania rozmiarem dziennika transakcji autorstwa Paula S. Randala.

Kwintesencja polega na tym, że istnieją tylko dwa naprawdę dobre sposoby obsługi dzienników transakcji :

1. Albo zastosuj regularne kopie zapasowe pliku LOG, a plik LOG ponownie wykorzysta swoje miejsce po każdej kopii zapasowej LOG i nie wzrośnie w nieskończoność, lub

2. Użyj modelu odzyskiwania SIMPLE i nie musisz przejmować się rozmiarem pliku LOG, ponieważ wykonujesz regularne pełne kopie zapasowe.

Co dotyczy obcinanie pliku dziennika i wydajność jest to, że zawsze możesz liczyć na spadek wydajności, gdy plik LOG ma zostać zwiększona (cytat z wyżej połączonego blogu):

Jeśli zmniejszysz dziennik, będzie on odradzał się ponownie - prawdopodobnie powodując fragmentację VLF i zdecydowanie powodując wstrzymanie obciążenia podczas wzrostu dziennika, ponieważ dziennik nie może użyć natychmiastowej inicjalizacji [...]

Aktualizacja: Nie pomyl obcinania pliku LOG ​​ze zmniejszeniem pliku DATA. Zmniejszenie pliku danych jest naprawdę złe. Zobacz, dlaczego nie powinieneś zmniejszać plików danych, aby uzyskać szczegółowe informacje.

OK, najpierw tak, dziennik jest konieczny nawet przy codziennych pełnych kopiach zapasowych, jeśli chcesz odzyskać w razie problemu. Tworzymy kopię dziennika transakcji co 15 minut. Problem polega na tym, że nie tworzysz kopii zapasowej dziennika transakcji i dlatego dziennik rośnie tak oburzająco. Prawie nigdy nie powinieneś zmniejszać dziennika transakcji, jeśli wykonujesz prawidłowe kopie zapasowe dziennika transakcji.

Przed obcięciem dziennika musisz wykonać kopię zapasową bazy danych. Sugeruję robienie tego poza godzinami pracy, aby żadne nowe dane nie były wstawiane między kopią zapasową a obcięciem. Następnie skonfiguruj odpowiednie kopie zapasowe dziennika transakcji, abyś nigdy więcej nie miał tego problemu.

Jeśli chodzi o wpływ na wydajność, dobrze bez znajomości szczegółów sprzętu i użytkowania systemu, trudno byłoby powiedzieć.

Jak szybko rośnie dziennik transakcji? Jeśli jest dość szybki, wpłyniesz na wydajność, zmniejszając go prawie do zera, ponieważ musi on poświęcić czas na jego odrodzenie. Nie oznacza to, że nie powinieneś go zmniejszać od czasu do czasu, ale musisz pomyśleć o problemie z wielkością, a nie tylko zmniejszać do minimum. Czy perf hit jest ogromny? Prawdopodobnie nie, ale zależy to od obciążenia serwera (liczby transakcji itp.).

Jedną z rzeczy, które uważam za problem, jest „wykonuję 2 pełne kopie zapasowe dziennie, więc dziennik nie powinien być tak naprawdę konieczny, jeśli chodzi o przenoszenie danych”. Dziennik jest niezwykle ważny dla punktów pomiędzy twoimi pełnymi kopiami zapasowymi. Nawet dwa razy dziennie nie eliminuje potrzeby pliku dziennika do odzyskiwania po awarii, chyba że jest to baza danych tylko do odczytu (gdyby tak było, nie zobaczyłby jednak ogromnego wzrostu pliku dziennika).