autor: Sergey Gigoyan | aktualizacja: 2015-11-18 | Komentarze (9) | powiązane: więcej > administracja bazami danych
Problem
SQL Server pozwala na użycie więcej niż jednego pliku dziennika transakcji, ale pojawia się pytanie, czy jest to konieczne i jakie są korzyści z posiadania wielu plików dziennika transakcji. Istnieje błędne przekonanie wśród niektórych programistów, że posiadanie plików dziennika multipletransaction może zwiększyć wydajność, ponieważ SQL Server może używać ich równolegle. SQL Server używa obecnie tylko jednego pliku dziennika transakcji i w tym przypadku nie ma równoległości. Czasami więcej niż jeden plik dziennika może być przydatny w celu rozwiązywania problemów. Tak więc zwykle nie ma potrzeby posiadania więcej niż jednego pliku dziennika.Rozważmy przypadek, gdy nasza baza danych ma więcej niż jeden plik dziennika i powinniśmy zachować tylko jeden.Ta wskazówka ma na celu opisanie, jak prawidłowo usunąć niepotrzebne pliki dziennika i zachować tylko jeden.
rozwiązanie
przed rozpoczęciem zilustrowania, jak usunąć niepotrzebne pliki dziennika, opiszmy krótko, jak działa SQL Server z plikami dziennika: gdy baza danych ma więcej niż jeden plik dziennika, SQL Server zapisuje do pierwszego,dopóki nie jest pełny, następnie przełącza się na drugi i tak dalej. Po tym, jak ostatni plik dziennika stanie się pełny, SQL Server powróci do pierwszego i cykl będzie kontynuowany.Jednak, jak wspomnieliśmy, czasami może być wymagane więcej niż jeden plik dziennika. Na przykład, gdy dysk, na którym znajduje się plik dziennika, staje się pełny i musimy utworzyć drugi plik dziennika w innej lokalizacji,ale po rozwiązaniu problemu powinniśmy usunąć drugi plik dziennika, ponieważ nie ma sensu mieć więcej niż jeden plik dziennika.Załóżmy teraz, że mamy bazę danych z dwoma plikami dziennika, a naszym zadaniem jest usunięcie drugiego.Poniższy skrypt tworzy bazę danych TestDB z dwoma plikami dziennika i tabelą TestTable. Aby uruchomić go samodzielnie, trzeba będzie wymienić”D:\SQL danych” z istniejącą ścieżką folderu.
USE GOCREATE DATABASE ON PRIMARY ( NAME = N'TestDB', FILENAME = N'D:\SQL DATA\TestDB.mdf' , SIZE = 4096KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ) LOG ON ( NAME = N'TestDB_log1', FILENAME = N'D:\SQL DATA\TestDB_log1.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%), ( NAME = N'TestDB_log2', FILENAME = N'D:\SQL DATA\TestDB_log2.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)GOUSE TestDBGOCREATE TABLE TestTable(ID INT IDENTITY(1,1),Value BIGINT)--Change recovery model for TestDB to FULL (if your model database is in FULL recovery model, TestDB has been already created in this mode)ALTER DATABASE TestDB SET RECOVERY FULL
teraz przyjrzyjmy się fizycznej strukturze dziennika transakcji: wewnętrznie plik dziennika transakcji składa się z wirtualnych plików dziennika (VLF), które są jednostką zarządzania w pliku dziennika transakcji. Oznacza to, że gdy silnik bazy danych rośnie lub kurczy plik dziennika, robi to z kompletnymi VLF-ami (na przykład nie może zmniejszyć połowy VLF). Rozmiar wirtualnych plików dziennika oraz ich numer w fizycznym pliku dziennika nie jest stały i jest dynamicznie zarządzany przez silnik bazy danych. Do wewnętrznego monitorowania pliku dziennika transakcji używamy polecenia „DBCC LOGINFO”, które dostarcza informacji o wirtualnych plikach dziennika. W poniższym skrypcie użyliśmy tego polecenia dla naszej bazy danych:
--Getting log files infoDBCC LOGINFO('TestDB')
a wynik jest następujący:
FileID to identyfikatory plików dziennika dla naszej bazy danych. Status wskazuje, czy VLF jest wielokrotnego użytku (możliwe wartości: 0-Tak, 2-nie). Jak widzimy, istnieje tylko jeden VLF o statusie = 2. Teraz, kiedy będziemy wstawiać dane do TestTable i monitorować, jak rosną pliki dziennika:
USE TestDBGO--Checking log information before insertionSELECT file_id, name, type_desc, physical_name, size, max_sizeFROM sys.database_files--Inserting data into TestTable;WITH ValueTable AS(SELECT 1 nUNION ALL SELECT n+ 1FROM ValueTableWHERE n
W tym przykładzie widzimy, że oba pliki dziennika rosły i teraz są VLFs z „Status=2” w drugim pliku dziennika (FileID=3) również:
teraz musimy usunąć TestDB_log2.plik ldf. Zauważ, że możemy usunąć tylko drugorzędne pliki dziennika. Usunięcie podstawowego pliku dziennika nie jest dozwolone przez SQL Server.Każda baza danych ma tylko jeden podstawowy plik dziennika, a pierwszy plik dziennika, który jest tworzony w skrypcie tworzenia bazy danych, jest uważany za podstawowy.Jeśli spróbujemy usunąć drugi plik dziennika:
USE masterGO--Remove TestDB_log2 fileALTER DATABASE TestDB REMOVE FILE TestDB_log2
otrzymamy następującą wiadomość:
plik dziennika transakcji możemy usunąć tylko wtedy, gdy jest pusty, dlatego najpierw musimy opróżnić it.To zrób to, powinniśmy zrobić kopię zapasową dziennika transakcji. Ponieważ nasza baza danych „TestDB” jest nowo utworzona i nie ma pełnych kopii zapasowych, musimy wykonać pełną kopię zapasową bazy danych TestDB, po czym możemy wykonać kopię zapasową dziennika transakcji:
--Full backupBACKUP DATABASE TestDB TO DISK =N'D:\SQL DATA\TestDB.bak'--Transaction log backupBACKUP LOG TestDB TO DISK =N'D:\SQL DATA\TestDB.trn'
kopia zapasowa dziennika transakcji obcina plik dziennika (istnieją pewne wyjątki, które są poza zakresem tego protokołu). Obcinanie dziennika usuwa nieaktywne wirtualne pliki dziennika z początku dziennika logicznego i zwalnia miejsce w pliku dziennika. Jednak obcinanie nie zmniejsza rozmiaru fizycznego pliku dziennika. Uwalnia w nim tylko przestrzeń, którą można ponownie wykorzystać. Uruchom ponownie „DBCC LOGINFO” :
jak widać w „TestDB_log2 nie ma wirtualnych plików dziennika.LDF ” plik o statusie = 2 i teraz nasz plik dziennika jest pusty i gotowy do usunięcia:
--Remove TestDB_log2 fileALTER DATABASE TestDB REMOVE FILE TestDB_log2
usunięcie się powiodło.
jednak gdy ponownie sprawdzimy dziennik informacji, zobaczymy, że plik dziennika logicznego nadal istnieje:
--Checking log informationSELECT file_id, name, type_desc, physical_name, size, max_sizeFROM sys.database_files
jeśli wykonamy kolejną kopię zapasową dziennika, plik zostanie usunięty:
--Transaction log backupBACKUP LOG TestDB TO DISK =N'D:\SQL DATA\TestDB.trn'--Checking log informationSELECT file_id, name, type_desc, physical_name, size, max_sizeFROM sys.database_files
kolejne kroki
- pamiętaj, aby określić użycie dziennika transakcji i usunąć niepotrzebny plik dziennika.
- Sprawdź te zasoby:
- Administracja bazą danych / dziennik transakcji
- https://technet.microsoft.com/en-us/library/ms191433(v=sql.105).aspx
- https://technet.microsoft.com/en-us/library/ms179355(v=sql.105).aspx
- https://technet.microsoft.com/en-us/library/ms345414(v=sql.105).aspx
o autorze
Sergey Gigoyan jest profesjonalistą baz danych z ponad 10-letnim doświadczeniem, koncentrując się na projektowaniu baz danych, rozwoju, dostrajaniu wydajności, optymalizacji, wysokiej dostępności, projektowaniu BI i DW.Zobacz wszystkie moje porady
artykuł Ostatnia aktualizacja: 2015-11-18