Niedawno pisałem o tym, jak poradzić sobie z problemami, jakie mogą wyniknąć ze stosowania metody zmniejszania pliku dziennika transakcji bazy danych w SQL Server polegającej na usunięciu pliku loga.
Tym razem skupię się na metodzie najbardziej prawidłowej i właściwie jedynej zalecanej – wykorzystującej kombinację poleceń BACKUP LOG + DBCC SHRINKFILE.
Do dzieła!
Na początek utwórzmy mały poligon, na którym pokażemy w obrazkach, jak to się robi.
CREATE DATABASE TestLogDB
GO
ALTER DATABASE TestLogDB SET RECOVERY FULL
GO
USE TestLogDB
GO
CREATE TABLE TestLogTable
(
col1 int identity(1,1),
col2 nvarchar(4000) DEFAULT (REPLICATE(N'a',4000))
)
GO
BACKUP DATABASE TestLogDB
TO DISK = N'C:\TestLogDBFull.bak'
GO
INSERT TestLogTable DEFAULT VALUES
GO 1000W nowo utworzonej bazie TestLogDB, której ustawiam recovery model na FULL (celowo robię to jawnie, ponieważ nie na każdym serwerze będzie to domyślne ustawienie dal nowych baz danych – patrz Express Edition lub sytuacja, w której ktoś zmienił opcję dla bazy model). Pełny backup bazy robię, gdyż tylko tak baza wchodzi w rzeczywisty FULL recovery model (backup jest punktem zaczepienia do rejestrowania zmian w logu). Wreszcie tworzę prostą tabelę i wypełniam ją rekordami (tu używam mechanizmu dostępnego w SSMS – wsad może wykonać się określoną ilość razy).
Zobaczmy teraz, jak wygląda log bazy. Na początek jego rozmiar:
DBCC SQLPERF(LOGSPACE)
GO
Database Name Log Size (MB) Log Space Used (%) Status
------------- ------------- ------------------ -----------
TestLogDB 9,929688 95,65794 0
Czyli log urósł (na początku miał 1MB) i na dodatek jest prawie pełny (czyli za moment prawdopodobnie znów się zwiększy).
A więc masz za dużego (?) loga bazy i chcesz go zmniejszyć :-)
Zmniejszamy loga
Zobaczmy VLF-y – czyli Virtual Log Files – czyli logiczne pliki dziennika transakcji, na które podzielony jest plik .ldf (użyję do tego nieudokumentowanego polecenia DBCC LOGINFO):
DBCC LOGINFO(N'TestLogDB')
GO
FileId FileSize StartOffset FSeqNo Status Parity CreateLSN
----------- -------------------- -------------------- ----------- ----------- ------ -----------------
2 253952 8192 27 2 64 0
2 253952 262144 28 2 64 0
2 270336 516096 29 2 64 0
2 262144 786432 30 2 64 29000000024900007
2 262144 1048576 31 2 64 30000000023100007
...
2 253952 8585216 56 2 64 55000000027200011
2 253952 8839168 57 2 64 55000000027200011
2 344064 9093120 58 2 64 55000000027200011
2 253952 9437184 59 2 64 58000000039300011
2 253952 9691136 60 2 64 58000000039300011
2 475136 9945088 0 0 0 58000000039300011
Poza tym, że jest ich trochę (w moim przykładzie było 35, a na samym początku, przed wstawieniem rekordów były ledwie 3) można o nich powiedzieć jeszcze jedno – prawie wszystkie są zajęte (status = 2 oznacza zajęty VLF, zaznaczyłem je na czerwono). Wolny pozostał tylko ostatni (status = 0 oznacza wolny VLF, zaznaczyłem go na zielono).
Co zrobić, żeby “opróżnić” log? Jak nakazują dobre praktyki i prawie wszystkie “guide’y”, należy wykonać backup loga.
BACKUP LOG TestLogDB
TO DISK = N'C:\TestLogDBLog.bak'
Backup zrobiony, zobaczmy ponownie rozmiar loga:
DBCC SQLPERF(LOGSPACE)
GO
Database Name Log Size (MB) Log Space Used (%) Status
------------- ------------- ------------------ -----------
TestLogDB 9,929688 24,69512 0
Rozmiar bez zmian, ale tym razem log jest zajęty w niewielkiej części – czyli nowe transakcje mają się gdzie logować.
Zobaczmy VLF-y:
DBCC LOGINFO(N'TestLogDB')
GO
FileId FileSize StartOffset FSeqNo Status Parity CreateLSN
----------- -------------------- -------------------- ----------- ----------- ------ -----------------
2 253952 8192 27 0 64 0
2 253952 262144 28 0 64 0
2 270336 516096 29 0 64 0
2 262144 786432 30 0 64 29000000024900007
2 262144 1048576 31 0 64 30000000023100007
...
2 253952 8585216 56 0 64 55000000027200011
2 253952 8839168 57 0 64 55000000027200011
2 344064 9093120 58 0 64 55000000027200011
2 253952 9437184 59 0 64 58000000039300011
2 253952 9691136 60 2 64 58000000039300011
2 475136 9945088 0 0 0 58000000039300011
Liczba VLF-ów się nie zmieniła (u mnie 35). Natomiast zmieniły się statusy. Widać, że zaznaczone na pomarańczowo VLF-y mają status 0, czyli są wolne i mogą przyjąć nowe wpisy – dziennik transakcji może być wypełniany od początku (angielskie określenie na takiego zachowania to log reuse), jeśli na początku pliku .ldf znajdują się wolne VLF-y. Dwa ostatnie VLF-y w pliku pozostały bez zmian.
Czas na zmniejszenie loga. Spróbujmy użyć polecenia DBCC SHRINKFILE.
DBCC SHRINKFILE(N'TestLogDB_log')
GO
Cannot shrink log file 2 (TestLogDB_log) because all logical log files are in use.
DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages
------ ----------- ----------- ----------- ----------- --------------
18 2 1214 96 1208 96
Hmmm, ale o co chodzi? :-)
Zobaczmy, czy log się zmniejszył (miał ponad 9MB).
DBCC SQLPERF(LOGSPACE)
GO
Database Name Log Size (MB) Log Space Used (%) Status
------------- ------------- ------------------ -----------
TestLogDB 9,476563 28,36459 0
A zatem praktycznie bez zmian… Można by pomyśleć, że coś źle robimy… Zobaczmy VLF-y.
DBCC LOGINFO(N'TestLogDB')
GO
FileId FileSize StartOffset FSeqNo Status Parity CreateLSN
----------- -------------------- -------------------- ----------- ----------- ------ ---------------------------------------
2 253952 8192 61 2 128 0
2 253952 262144 62 2 128 0
2 270336 516096 29 0 64 0
2 262144 786432 30 0 64 29000000024900007
2 262144 1048576 31 0 64 30000000023100007
2 262144 1310720 32 0 64 31000000023100007
...
2 344064 9093120 58 0 64 55000000027200011
2 253952 9437184 59 0 64 58000000039300011
2 253952 9691136 60 2 64 58000000039300011
Po pierwsze liczba VLF-ów się zmniejszyła (u mnie został usunięty ostatni VLF i mam ich teraz 34). A więc polecenie DBCC SHRINKFILE coś jednak zrobiło! Ostatnim zajętym VLF-em jest teraz drugi VLF w pliku .ldf. A więc przez wykonanie próby zmniejszenia loga tenże log się “obrócił” i teraz będzie wypełniany od początku.
Jeśli teraz wykonamy ponownie backup loga:
BACKUP LOG TestLogDB
TO DISK = N'C:\TestLogDBLog.bak'
to być może okaże się (nie jest to regułą, o czym przeczytasz we wnioskach), że nasz plik .ldf będzie wreszcie gotowy do zmniejszenia:
DBCC LOGINFO(N'TestLogDB')
GO
FileId FileSize StartOffset FSeqNo Status Parity CreateLSN
----------- -------------------- -------------------- ----------- ----------- ------ -----------------
2 253952 8192 61 0 128 0
2 253952 262144 62 2 128 0
2 270336 516096 29 0 64 0
2 262144 786432 30 0 64 29000000024900007
2 262144 1048576 31 0 64 30000000023100007
...
2 344064 9093120 58 0 64 55000000027200011
2 253952 9437184 59 0 64 58000000039300007
2 253952 9691136 60 0 64 58000000039300007
Liczba VLF-ów – bez zmian. Ale tym razem począwszy od trzeciego wszystkie są wolne i gotowe do usunięcia.
Dopełnienie formalności:
DBCC SHRINKFILE(N'TestLogDB_log')
GO
Database Name Log Size (MB) Log Space Used (%) Status
------------- ------------- ------------------ -----------
TestLogDB 0,7421875 38,55263 0
Uff… Ileż się trzeba było napracować. Ale wreszcie sukces (?) – log jest mniejszy (gdyby sprawdzić liczbę VLF-ów, to okazałoby się, że jest ich bardzo niewiele – na przykład 3). I teraz…
I teraz rekomendowana jest operacja ALTER DATABASE ... MODIFY FILE ... na pliku logów, by zwiększyć go do właściwego rozmiaru (zalecane od 10 do 25% rozmiaru plików danych bazy – ale wszystko zależy od paru czynników, m.in. od ilości transakcji w bazie, ilości użytkowników, etc.).
Wnioski i zalecenia
Cały ten ciąg czynności miał na celu wyciągnięcie niekoniecznie banalnych wniosków oraz przedstawienie pewnych zaleceń przydatnych w pracy z logiem:
- DBCC SHRINKFILE na pliku loga działa od razu, a nie z opóźnieniem, jak czasem można sądzić (choć jego działanie może być opóźnione przez aktywne transakcje lub inne operacje wykonywane na logu).
- DBCC SHRINKFILE nie przesuwa niczego w pliku .ldf (w plikach danych potrafi przemieszczać dane ku początkowi pliku).
- DBCC SHRINKFILE w pliku .ldf działa na VLF-ach (VLF jest atomową jednostką).
- Przed wykonaniem DBCC SHRINKFILE robimy backup dziennika transakcji (żeby go opróżnić).
- Jeśli nie uda się raz zmniejszyć loga tą metodą, trzeba spróbować drugi :-) A jak nie uda się za drugim razem, to i trzeci (a bywa, że i czwarty – musisz “wstrzelić się” z DBCC SHRINKFILE w moment, gdy ostatni aktywny VLF leży na początku pliku .ldf).
- Operacje zmniejszania loga najlepiej jest przeprowadzać przy braku aktywności użytkowników w bazie (można ustawić tryb SINGLE_USER). Wtedy mamy pełną kontrolę nad tym, co dzieje się w logu.
- Jeśli pokazana metoda zawodzi notorycznie, sprawdź, czy nie ma otwartej transakcji na bazie danych (w widoku sys.databases w bazie master jest kolumna log_reuse_wait_desc, możesz też użyć polecenia DBCC OPENTRAN).
- Liczba VLF-ów powinna być jak najmniejsza i nie powinna przekraczać 50 (ma to wpływ na wydajność operacji zapisu i zmniejszania pliku loga).
- Szalenie ważna dla utrzymania loga jest strategia backupowania loga. Wykonuj backupy loga, jeśli nie chcesz, by rósł on “w nieskończoność”.
- Zaraz po wykonaniu operacji zmniejszenia loga, zwiększ rozmiar pliku .ldf do rozmiaru właściwego. Wykonanie jednorazowego zwiększenia loga w miejsce wielu automatycznych operacji powiększenia spowoduje, że w logu będzie mniej VLF-ów. To dlatego wykonywanie operacji zmniejszania loga nierzadko mija się z celem. Czasem po prostu lepiej go opróżnić, skontrolować liczbę VLF-ów i – jeśli wszystko jest w porządku – zostawić nieco większy plik loga (i uniknąć jego “poszatkowania” przy operacjach automatycznego powiększania).
Ciekawostką może być fakt, że wiele razy próbowałem w tym procesie wykorzystać polecenie CHECKPOINT (celowo nie umieściłem go ani razu w kodzie, by nie psuć obrazu całości). Jednak nie zauważyłem istotnego wpływu CHECKPOINTa na VLF-y i problem zmniejszania pliku .ldf.
Dodatki
O poleceniu DBCC LOGINFO i innych nieudokumentowanych poleceniach DBCC poczytasz w artykule: SQL Server 2005 - nieudokumentowane polecenia DBCC.
Swego czasu o utrzymaniu loga bardzo mądrze i ciekawie napisała Kimberly L. Tripp: 8 Steps to better Transaction Log throughput.
Credits
W przeprowadzeniu pokazanego tu doświadczenia i wyciąganiu słusznych, jak się wydaje, wniosków, uczestniczyli Jarek Kuśmierek oraz Marek Adamczuk. Jarkowi składam osobne podziękowania za dążenie do prawdy w kwestii działania DBCC SHRINKFILE i obalenie mylnego przekonania, jakoby polecenie to mogło być wykonywane z pewnym naturalnym “odłożeniem” w czasie (deferred log shrink umarł ponoć wraz z SQL Server 7.0 - http://msdn.microsoft.com/en-us/library/aa902689(SQL.80).aspx).