Witaj na Zine.net online Zaloguj się | Rejestracja | Pomoc

[PL] Mój log jest za duży

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 1000

W 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:

  1. 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).
  2. DBCC SHRINKFILE nie przesuwa niczego w pliku .ldf (w plikach danych potrafi przemieszczać dane ku początkowi pliku).
  3. DBCC SHRINKFILE w pliku .ldf działa na VLF-ach (VLF jest atomową jednostką).
  4. Przed wykonaniem DBCC SHRINKFILE robimy backup dziennika transakcji (żeby go opróżnić).
  5. 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).
  6. 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.
  7. 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).
  8. Liczba VLF-ów powinna być jak najmniejsza i nie powinna przekraczać 50 (ma to wpływ na wydajność operacji zapisu i zmniejszania pliku loga).
  9. Szalenie ważna dla utrzymania loga jest strategia backupowania loga. Wykonuj backupy loga, jeśli nie chcesz, by rósł on “w nieskończoność”.
  10. 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).

Opublikowane 25 lipca 2008 09:03 przez brejk
Attachment(s): transaction_log.txt

Komentarze:

# re: [PL] Mój log jest za duży

17 maja 2009 16:22 by Wincek

Naprawdę pomocny artykuł, tego mi własnie brakowało :)

Dzięki i pozdro!  

# re: [PL] Mój log jest za duży

5 sierpnia 2009 14:42 by aaa

o logach

# re: [PL] Mój log jest za duży

17 września 2009 15:12 by grzegorz

W raporcie (sql server exprres 2005) dashboard mam aktywne 22 transakcje. Po wylogowaniu wszystkich urzytkownikow liczba transakji sie nie zmienia. Jak moge zamknac te tranzacje ?

# re: [PL] Mój log jest za duży

2 października 2009 00:23 by brejk

@grzegorz: Możesz znaleźć SPIDy sesji, na których wiszą transakcje (np. odpytując sys.dm_tran_session_transactions - nie pamiętam, czy ten widok jest na SQL Server 2005). Potem zabijasz takie sesje poleceniem KILL.

# re: [PL] Mój log jest za duży

25 listopada 2009 15:30 by eres

Świetnie napisane, treściwie i dla ludzi (nie dla cyborgów : ) ). Bardzo dziękuję.

# re: [PL] Mój log jest za duży

29 stycznia 2010 10:15 by Prekto

Tekst rewelacja. Bardzo mi pomógł. Nie wiedziałem jak podejść do zmniejszenia logów a ten artykuł ładnie mi w tym pomógł. Dzięki

Pozdrawiam,

Prekto

# re: [PL] Mój log jest za duży

6 kwietnia 2010 10:07 by MK

Bardzo konkretny i bardzo dobrze napisane pozdrawiam :)

# Du??y log transakcyjny SQL Server | NullWorld

# SQL Server – Kontrola dziennik??w transakcji « Jasne i ciemne strony baz danych

# SQL Server – Kontrola dziennik??w transakcji « SQLGeek.pl

# re: [PL] Mój log jest za duży

20 września 2010 18:00 by smietan

Potwierdzam, rzeczywiście działa w pełni. W opisany powyżej sposób w SQL Server 2008 zmniejszyłem plik ldf z ponad 90GB (!) do poniżej 1MB dla bazy o wielkości 64GB.

# re: [PL] Mój log jest za duży

20 września 2010 18:00 by smietan

Potwierdzam, rzeczywiście działa w pełni. W opisany powyżej sposób w SQL Server 2008 zmniejszyłem plik ldf z ponad 90GB (!) do poniżej 1MB dla bazy o wielkości 64GB.

Pozdrawiam,

Marek

# re: [PL] Mój log jest za duży

26 października 2010 14:12 by re

Mam pytanie jak poradzić sobie w przypadku gdy mam 7 plików :

baza_Daat01.ndf

baza_Data_201.ndf

baza_Index01.ndf

baza_Large01.ndf

baza_Large_Index01.ndf

baza_log01.ldf

baza_Primary01.mdf

a nie jak w przykładzie 2 : .mdf i .ldf .

Również chciałbym zmniejszyć .ldf ( wielkości 70GB), jednakże postępując wg. schematu wyrzuca mi błąd :

"

Could not locate file 'baza_log01' for database 'baza' in sys.database_files. The file either does not exist, or was dropped.

"

pozdrawiam

Krzysiek

# re: [PL] Mój log jest za duży

8 kwietnia 2011 14:09 by C3PO

@Krzysiek: Polecenie DBCC SHRINKFILE odwołuje się do nazwy logicznej pliku. Musisz odnaleźć nazwę logiczną w widoku sys.database_files w bazie danych, dla której plik loga chcesz zmniejszyć.

# re: [PL] Mój log jest za duży

16 czerwca 2011 13:03 by Janek

Bardzo przydatny tekst.

Nie do końca rozwiązał mój problem, bo moja baza jest na okrągło replikowana i pokazywał się komunikat:

The log was not truncated because records at the beginning of the log are pending replication or Change Data Capture. Ensure the Log Reader Agent or capture job is running or use sp_repldone to mark transactions as distributed or captured.

zastosowałem procedurę sp_repldone w następujący sposób:

EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0,    @time = 0, @reset = 1

następnie backup loga i opisany powyżej shrink pliku loga.

po tym wszystkim plik log zmniejszył się z 52GB do 100MB

pozdrawiam

Janek

# re: [PL] Mój log jest za duży

12 lipca 2012 16:34 by Shr

Świetny tekst. Niby wiele informacji na różnych forach na ten temat, ale wszystkie niekompletne, zawierające wiele niedopowiedzeń. Zyskałeś w mojej osobie nowego czytelnika.

Komentarze anonimowe wyłączone

About brejk

MVP, MCT, SQL Server geek