SQLShack

ten artykuł bada wykorzystanie wielu plików dziennika transakcji SQL Server i proces usuwania wtórnego pliku dziennika transakcji.

wprowadzenie

domyślnie baza danych SQL Server zawiera podstawowy plik danych i plik dziennika transakcji. Jest to dobra praktyka konfigurowania wielu plików danych i dzielenia danych na wiele plików danych. Możemy mieć te pliki danych w oddzielnych dyskach pamięci, aby mieć wiele IO. Pomaga w zarządzaniu danymi, poprawia wydajność, planuje metody tworzenia kopii zapasowych zgodnie z filegroups.

możemy mieć wiele plików dziennika transakcji dla bazy danych, jak również.

SQL Server używa wielu plików dziennika transakcji w kolejności sekwencyjnej. Załóżmy, że mamy dwa pliki dziennika transakcji. SQL Server używa jednego pliku dziennika na raz, a gdy jest pełny, SQL Server używa innego pliku dziennika. Ponieważ SQL Server nie używa go równolegle, nie uzyskujemy z niego żadnych korzyści wydajnościowych. Idealnie, powinniśmy mieć tylko jeden plik dziennika transakcji na bazę danych.

Załóżmy, że mamy produkcyjną bazę danych i otrzymujemy krytyczny alarm, że na dysku kończy się miejsce. Po śledztwie dowiedzieliśmy się, że ten dysk zawiera plik dziennika transakcji. Ze względu na aktywne transakcje, ten plik dziennika transakcji SQL Server jest pełny. Próbowaliśmy zmniejszyć plik dziennika, ale nie zadziałał. Wykonujemy również kopię zapasową dziennika transakcji, ale z powodu aktywnych transakcji nie mógł zwolnić potrzebnego miejsca. Dodanie innego pliku dziennika transakcji na oddzielnym dysku z wolnym miejscem rozwiąże ten problem.

ponieważ SQL Server używa trybu szeregowego do zapisu danych w pliku dziennika transakcji, powinniśmy usunąć dodatkowy plik dziennika później. Przyjrzyjmy się procesowi usuwania dodatkowego pliku dziennika.

Utwórz bazę danych z wieloma plikami dziennika transakcji SQL Server

Połącz się z instancją SQL w SQL Server Management Studio. Kliknij prawym przyciskiem myszy węzeł Bazy danych w okienku Eksploratora obiektów i kliknij polecenie Nowa baza danych:

otworzy się okno nowej bazy danych. Określ nazwę bazy danych i dodaj kolejny plik dziennika transakcji. W celach testowych wyłączyliśmy automatyczny wzrost plików dziennika transakcji. Początkowy Rozmiar pliku dziennika transakcji wynosi 8 MB:

po ustawieniu wszystkich ustawień kliknij przycisk OK, aby utworzyć bazę danych w domyślnym katalogu danych/plików dziennika. Utworzyć tabelę i wstawić dane do:

Zobacz status Wirtualnego pliku dziennika w plikach dziennika transakcji SQL Server

w artykule Architektura dziennika transakcji SQL Server zbadaliśmy wnętrze pliku dziennika transakcji. Każdy plik dziennika transakcji składa się z wielu wirtualnych plików dziennika. Plik dziennika transakcji jest kombinacją wielu wirtualnych plików dziennika (VLF). Poniższy zrzut ekranu pokazuje fizyczną i logiczną architekturę pliku dziennika:

SQL Server statystyki bazy danych z minimalnym VLF na podstawie początkowego rozmiaru dziennika i pliku automatycznego wzrostu (w oparciu o konfigurację automatycznego wzrostu). Na poniższym obrazku widzimy plik dziennika transakcji SQL Server:

w przypadku pojedynczego pliku dziennika transakcji SQL Server używa okrągłej ścieżki Wirtualnego pliku dziennika. Możemy sprawdzić liczbę wirtualnych plików dziennika i ich status za pomocą następujących:

1. DBCC LOGINFO (’Database’) – jest to stara instrukcja i działa ze wszystkimi wersjami SQL Server
2. dynamiczny widok zarządzania sys.dm_db_log_info (DBID). Jest on dostępny z SQL Server 2016 SP2 lub nowszego

każde z poleceń może być użyte do sprawdzenia VLF. W tym artykule użyjemy dynamicznego widoku zarządzania (DMV):

na powyższym zrzucie ekranu sprawdziliśmy, że nasza przykładowa baza danych zawiera dwa pliki dziennika transakcji (file_id 2 i file_id 3).

• File_id 2 ma aktywny VLF (vlf_active=1 i vlf_status=2)
• File_id 3 nie ma aktywnego VLF (vlf_active = 0 i vlf_status=2)

wstawmy jeszcze kilka rekordów do tabeli, aby aktywny VLF się zmienił:

monitorowanie wykorzystania przestrzeni dziennika transakcji i stan VLF będzie odbywać się za pomocą DMV (sys. dm_db_log_space_usage):

• status VLF

  1 2	wybierz DB_name() jako DatabaseName,File_ID jako transaction_log_file_ID, vlf_active , vlf_status z sys.dm_db_log_info (DB_ID (’MultileLogFiles’));

• wykorzystanie dziennika transakcji (używane i wolne miejsce)

  1 2 3 4 5

  wybierz total_log_size_in_bytes*1.0/1024/1024 total_log_size_in_MB, used_log_space_in_bytes*1.0/1024/1024 used_log_space_in_MB, (total_log_size_in_bytes-used_log_space_in_bytes)*1.0/1024/1024 jako free_log_space_in_MB z sys. dm_db_log_space_usage;

otrzymujemy następujące wyniki powyższych zapytań:

• po lewej stronie widzimy, że gdy główny plik dziennika (file_id 2) stanie się pełny, przechodzi do następnego pliku dziennika (file_id 3). W tym momencie Mamy aktywny VLF zarówno w podstawowych, jak i drugorzędnych plikach dziennika. W bazie danych z modelem pełnego odzyskiwania potrzebujemy kopii zapasowej dziennika transakcji tak, aby oznaczała VLF jako nieaktywny
• wyjście po prawej stronie (za pomocą DMV) pokazuje plik dziennika transakcji, z którego użyto miejsca 8.51 MB. Jak pamiętacie, ustawiliśmy rozmiar 8 MB dla każdego pliku dziennika transakcji i wyłączyliśmy automatyczny wzrost dziennika. Gdy główny plik dziennika jest pełny (8 MB), SQL Server przełączy się na wtórny plik dziennika transakcji

Usuń wtórny plik dziennika transakcji SQL Server

aby usunąć wtórny plik dziennika transakcji (ID pliku 3), użyjemy edytowanej instrukcji Alter database.

dodamy klauzulę usuń plik i określimy plik, który chcemy usunąć:

wykonanie tej instrukcji spowoduje następujący komunikat o błędzie:

uwaga: nie można usunąć aktywnego pliku dziennika transakcji.

poprzednio widzieliśmy, że gdy główny plik dziennika staje się pełny, SQL Server używa wtórnego pliku dziennika. Musimy zrobić drugi dziennik transakcji pusty, abyśmy mogli go usunąć.

w bazie danych SQL z modelem pełnego odzyskiwania używamy kopii zapasowych dzienników transakcji, dzięki czemu SQL Server może obciąć dzienniki. W tym celu możemy potrzebować wielu kopii zapasowych dziennika w zależności od rozmiaru dziennika transakcji, aktywnego VLF i aktywnej transakcji.

wykonajmy pełną kopię zapasową bazy danych i dziennika transakcji:

• pełna kopia zapasowa bazy danych

  1	backup bazy danych MultipleLogFiles to disk= ” C:\Temp\MultipleLogFiles.bak’

• kopia zapasowa dziennika transakcji

  1	backup Log MultipleLogFiles to disk= ” C:\Temp\MultipleLogFiles_log.trn’

po zakończeniu tworzenia kopii zapasowej sprawdź stan VLF. Aktywny VLF powinien znajdować się w pierwszym pliku dziennika(podstawowym), abyśmy mogli usunąć drugi plik dziennika. Sprawdziliśmy, że aktywny jest tylko główny plik dziennika (file_id 2) (VLF Status 2):

teraz dodatkowy plik dziennika transakcji powinien zostać usunięty bez żadnych problemów. Ponownie wykonajmy polecenie edited Alter database. Drugi plik dziennika transakcji zostanie usunięty:

po usunięciu wtórnego pliku dziennika transakcji sprawdź go za pomocą interfejsu graficznego oraz T-SQL z widokiem systemowym sys.database_files:

usunięty plik dziennika transakcji jest nadal obecny, jak na poniższym zrzucie ekranu:

teraz kliknij prawym przyciskiem myszy bazę danych i zobacz istniejące pliki. W GUI widzimy również usunięty plik dziennika transakcji. Ale dlaczego?

ponownie wykonajmy edytowaną instrukcję Alter database i sprawdź, czy SQL Server ponownie usunie plik dziennika transakcji.

otrzymaliśmy komunikat, że SQL Server nie może znaleźć podanego pliku dziennika:

SQL Server usuwa plik dziennika transakcji po kolejnej kopii zapasowej dziennika. Wykonajmy kolejną kopię zapasową dziennika i sprawdźmy, czy plik dziennika transakcji nadal istnieje:

Sprawdź plik dziennika transakcji zarówno w GUI, jak i metodach T-SQL. Widzimy, że usunięty plik dziennika transakcji nie pojawia się teraz.

wniosek

w tym artykule zbadaliśmy użycie wtórnego dziennika transakcji SQL Server i proces jego usuwania. Należy unikać używania wielu plików dziennika transakcji, zwłaszcza w produkcyjnej bazie danych. Przed planowaniem jakiejkolwiek aktywności należy wykonać kopię zapasową bazy danych i wykonać ją w godzinach nieproduktywnych.

• Autor
• Ostatnie posty

jako trener MCSA certified I Microsoft Certified Trainer w Gurgaon w Indiach, z 13-letnim doświadczeniem, Rajendra pracuje dla wielu dużych firm, koncentrując się na optymalizacji wydajności, monitorowaniu, wysokiej dostępności oraz strategiach i wdrażaniu disaster recovery. Jest autorem setek autorytatywnych artykułów na temat SQL Server, Azure, MySQL, Linux, Power BI, Performance tuning, AWS/Amazon RDS, Git i powiązanych technologii, które do tej pory obejrzało ponad 10 milionów czytelników.
jest twórcą jednego z największych darmowych internetowych zbiorów artykułów na jeden temat. jego 50-częściowa seria na SQL Server Always On Availability Groups. W oparciu o swój wkład w społeczność SQL Server został wyróżniony różnymi nagrodami, w tym prestiżowym „najlepszym autorem roku” nieprzerwanie w 2020 i 2021 roku w sqlshack.
Raj zawsze interesuje się nowymi wyzwaniami, więc jeśli potrzebujesz pomocy Konsultacyjnej na dowolny temat poruszany w jego pismach, możesz się z nim skontaktować w [email protected]
Zobacz wszystkie posty, których autorem jest Rajendra Gupta

najnowsze posty Rajendra Gupta (Zobacz wszystkie)

• użyj szablonów ARM, aby wdrożyć instancje Azure container za pomocą obrazów SQL Server Linux-21 grudnia 2021 r.
• dostęp do pulpitu zdalnego dla AWS RDS SQL Server z niestandardowym Amazon RDS-14 grudnia 2021 r.
• przechowuj pliki SQL Server w trwałej pamięci masowej dla wystąpień Azure Container – grudzień 10, 2021