Nieoczekiwane zwiększenie rozmiaru lub przepełnienie dziennika transakcji na komputerze z programem SQL Server

Tłumaczenia artykułów Tłumaczenia artykułów
Numer ID artykułu: 317375 - Zobacz jakich produktów dotyczą zawarte w tym artykule porady.
Rozwiń wszystko | Zwiń wszystko

Na tej stronie

Streszczenie

W programie SQL Server 7.0, SQL Server 2000 i SQL Server 2005 po włączeniu opcji autogrow rozmiar plików dziennika transakcji może zwiększać się automatycznie.

Zwykle rozmiar pliku dziennika transakcji stabilizuje się, gdy może on pomieścić maksymalną liczbę transakcji, które mogą być zapisane pomiędzy kolejnymi obcięciami dziennika transakcji inicjowanymi przez punkty kontrolne lub wykonanie kopii zapasowej dziennika transakcji.

Jednakże w niektórych sytuacjach rozmiar dziennika transakcji może być tak duży, że przepełni się on lub spowoduje brak miejsca na dysku. Zwykle, gdy dziennik operacji zajmie całe dostępne miejsce na dysku i nie może już być rozszerzony, pojawi się następujący komunikat o błędzie:
Error: 9002, Severity: 17, State: 2
The log file for database '%.*ls' is full.
W przypadku korzystania z programu SQL Server 2005 pojawia się komunikat o błędzie podobny do następującego:
Error: 9002, Severity: 17, State: 2
The transaction log for database '%.*ls' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases. (Błąd: 9002, Ważność: 17, Stan: 17: Stan: 2. Dziennik transkacji dla bazy danych '%. *Aby dowiedzieć się, dlaczego miejsce w dzienniku nie może zostać ponownie wykorzystane, zobacz kolumnę log_reuse_wait_desc w tabeli sys.databases.)
Oprócz wysłania tego komunikatu program SQL Server może oznaczyć bazy danych jako „podejrzane” z powodu braku miejsca na dalsze rozszerzanie dziennika transakcji. Dodatkowe informacje o tym, jak poradzić sobie z tym problemem można znaleźć w rozdziale „Insufficient Disk Space” dokumentacji online programu SQL Server.

Ponadto zwiększenie rozmiaru dziennika transakcji może spowodować następujące problemy:
  • Bardzo duży rozmiar pliku dziennika transakcji.
  • Nieudane transakcje i wycofywanie transakcji.
  • Wydłużenie czasu ukończenia transakcji.
  • Obniżenie wydajności.
  • Blokowanie pracy programu.

Przyczyny

Przyczyny i scenariusze zwiększenia rozmiaru dziennika transakcji mogą być następujące: Uwaga W programie SQL Server 2005 można przejrzeć kolumny log_reuse_wait i log_reuse_wait_desc widoku wykazu sys.databases w celu ustalenia dwóch rzeczy:
  • Dlaczego miejsce w dzienniku transakcji nie jest ponownie wykorzystywane.
  • Dlaczego dziennik transakcji nie może zostać obcięty.

Niezakończone transakcje

Transakcje pozostają niezakończone do momentu jawnego wydania polecenia COMMIT lub ROLLBACK. Najczęstszym problemem jest wydanie przez aplikację polecenia CANCEL lub polecenia KILL w języku Transact SQL bez następującego po nim polecenia ROLLBACK. Następuje wówczas anulowanie transakcji, ale nie zostaje ona wycofana. Z tego powodu program SQL Server nie może obciąć żadnej późniejszej operacji, ponieważ anulowana transakcja wciąż pozostaje otwarta. W celu sprawdzenia, czy w bazie danych występuje taka aktywna operacja można użyć polecenia DBCC OPENTRAN w języku Transact SQL. Aby uzyskać więcej informacji dotyczących konkretnego scenariusza, kliknij następujący numer artykułu w celu wyświetlenia tego artykułu z bazy wiedzy Microsoft Knowledge Base:
295108 Incomplete transaction may hold large number of locks and case blocking
171224 Understanding how the Transact-SQL KILL command works
Przeczytaj również informacje dotyczące polecenia „DBCC OPENTRAN” w dokumentacji online programu SQL Server.

Scenariusze wydarzeń mogące doprowadzić do powstania niezakończonych transakcji:
  • Projekt budowy aplikacji zakładający, że każdy błąd powoduje wycofanie transakcji.
  • Projekt budowy aplikacji, który nie w pełni uwzględnia sposób działania serwera SQL przy wycofywaniu nazwanych transakcji lub zagnieżdżonych transakcji. Próba wycofania wewnętrznej transakcji spowoduje wyświetlanie następującego komunikatu o błędzie:
    Server: Msg 6401, Level 16, State 1, Line 13 Cannot roll back InnerTran. No transaction or savepoint of that name was found.
    Po wygenerowaniu błędu program SQL Server przechodzi do kolejnego polecenia. To zachowanie jest zgodne z projektem. Więcej informacji można znaleźć w rozdziałach „Nested Transactions” oraz „Inside SQL Server” dokumentacji online programu SQL Server.

    Firma Microsoft zaleca następujące postępowanie przy projektowaniu aplikacji:
    • Otwieraj tylko jedną jednostkę transakcyjną (istnieje możliwość, że inny proces może się odwoływać do tej aplikacji).
    • Sprawdź wartość @@TRANCOUNT przed wydaniem polecenia COMMIT, ROLLBACK, RETURN lub innego polecenia o podobnym działaniu.
    • Pisząc kod programu pamiętaj, że inna operacja @@TRANCOUNT może spowodować „zagnieżdżenie” i ta zewnętrzna operacja @@TRANCOUNT może zostać wycofana przy wystąpieniu błędu.
    • Przeanalizuj punkty zapisu i oznacz opcje dla transakcji. (Nie zapewnia to zwolnienia blokad!)
    • Wykonaj pełne testowanie.
  • Aplikacja umożliwiająca interakcję użytkownika po otwarciu transakcji. Transakcje mogą pozostawać otwarte przez bardzo długi czas, co może doprowadzić do blokowania i zwiększania rozmiaru dziennika transakcji, ponieważ otwarta operacja nie może być obcięta, a operacje następujące po niej są wciąż dodawane do dziennika transakcji.
  • Aplikacja nie sprawdzająca wartości @@TRANCOUNT w celu potwierdzenia, że nie ma żadnych otwartych operacji.
  • Błędy sieciowe lub innego typu powodujące zamknięcie połączenia klienta z serwerem SQL bez przesłania informacji.
  • Pule połączeń. Po utworzeniu wątków roboczych program SQL Server wykorzystuje je ponownie, jeśli nie obsługują żadnego połączenia. Jeśli połączony użytkownik rozpocznie transakcję i rozłączy się przed zakończeniem lub wycofaniem jej, a połączenie zostanie ponownie wykorzystane dla tego samego wątku, to poprzednia transakcja pozostaje otwarta. Zablokowanie poprzedniej transakcji uniemożliwia obcięcie zakończonych transakcji w dzienniku transakcji i powoduje zwiększenie rozmiaru dziennika transakcji. Aby uzyskać więcej informacji dotyczących pul połączeń, kliknij następujący numer artykułu w celu wyświetlenia tego artykułu z bazy wiedzy Microsoft Knowledge Base:
    164221 How to enable connection pooling in an ODBC application

Bardzo duże transakcje

Zapisy w plikach dziennika transakcji są obcinane poprzez liniowe analizowanie kolejnych transakcji. Jeśli transakcja jest bardzo duża to wszystkie transakcje następujące po niej mogą być usunięte z dziennika dopiero po jej zakończeniu. Może to spowodować powstanie dużych plików dziennika. W przypadku bardzo dużej transakcji plik dziennika może zająć całe dostępne miejsce na dysku, co spowoduje wystąpienie błędu przepełnienia dziennika transakcji, np. błędu 9002. Dodatkowe informacje na temat błędów tego typu można znaleźć w części „Więcej informacji” tego artykułu. Warto też pamiętać, że wycofanie bardzo dużej operacji silnie obciąża program SQL Server i może zająć mnóstwo czasu.

Operacje: DBCC DBREINDEX i CREATE INDEX

Za względu na zmiany w modelu odzyskiwania danych wprowadzone w programie SQL Server 2000, jeśli używany jest tryb pełnego odzyskiwania i uruchomione zostanie polecenie DBCC DBREINDEX, to rozmiar dziennika może zwiększyć się w bardzo dużym stopniu w porównaniu do programu SQL Server 7.0 w odpowiednim trybie odzyskiwania przy użyciu poleceń SELECT INTO lub BULK COPY z wyłączoną opcją „Trunc. Log on chkpt.”.

Pomimo że rozmiar dziennika transakcji po wykonaniu operacji DBREINDEX może bardzo wzrosnąć, to jednak obecne rozwiązanie zapewnia lepszą wydajność odzyskiwania dziennika.

Przywracanie z kopii zapasowej dziennika transakcji

Ten scenariusz jest opisany w następującym artykule z bazy wiedzy Microsoft Knowledge Base:
232196 Log space used appears to grow after restoring from backup

Jeśli w programie SQL Server 2000 włączono tryb Bulk-Logged, to po wydaniu polecenia BULK COPY lub SELECT INTO każdy zmieniony fragment jest oznaczany i archiwizowany przy tworzeniu kopii zapasowej dziennika transakcji. Umożliwia to wprawdzie tworzenie kopii zapasowych dzienników transakcji i przywracanie danych po błędach nawet w przypadku operacji zbiorczych, ale prowadzi do zwiększenia rozmiaru dziennika transakcji. W wersji 7.0 nie ma takiej funkcji. Program SQL Server 7.0 rejestruje tylko, które fragmenty zostały zmienione, nie zapisując jednak tych fragmentów. Z tego powodu rejestrowanie zmian w trybie Bulk-Log w programie SQL Server 2000 wymaga więcej miejsca niż w przypadku wersji 7.0, choć nie tak dużo, jak w trybie pełnym (Full mode).

Aplikacje klienta nie przetwarzają wszystkich wyników

Jeśli po przesłaniu kwerendy do serwera SQL aplikacja nie może natychmiast pobrać wyników, to podtrzymuje założone blokady i zmniejsza wydajność serwera.

Na przykład przypuśćmy, że przesłano kwerendę, której wykonanie wymaga dostępu do wierszy z dwóch stron. Serwer SQL analizuje, kompiluje i wykonuje kwerendę. Oznacza to, że zablokowane zostaną dwie strony zawierające wiersze odpowiadające warunkom kwerendy. Dodatkowo załóżmy, że nie wszystkie wiersze mieszczą się w pakiecie TDS serwera SQL (metoda komunikacji serwera z klientem). Pakiety TDS są wypełniane i przesyłane do klienta. Jeśli wszystkie wiersze z pierwszej strony mieszczą się w pakiecie TDS, to serwer SQL zwolni tę stronę, pozostawiając jednak blokadę na drugiej stronie. Program SQL Server czeka następnie na otrzymanie od klienta żądania przesłania dalszych wyników (służy do tego polecenie DBNEXTROW/DBRESULTS, SQLNextRow/SQLResults lub FetchLast/FetchFirst).

Strona pozostaje więc zablokowana aż do momentu przesłania żądania przez klienta. Inne procesy, które potrzebują danych z drugiej strony muszą czekać na jej odblokowanie.

Limit czasu kwerend upływa przed zakończeniem rozszerzenia dziennika transakcji i pojawiają się fałszywe komunikaty o przepełnieniu dziennika transakcji

W tym przypadku mimo że na dysku jest wystarczająca ilość miejsca, pojawia się komunikat o braku wolnego miejsca.

Sytuacja różni się zależnie od wersji serwera SQL (7.0 lub 2000).

Kwerenda może spowodować automatyczne zwiększenie rozmiaru dziennika transakcji, jeśli jest on prawie pełny. Operacja ta wymaga czasu, w którym kwerenda może zostać przerwana lub może upłynąć ustalony dla niej limit czasu. Program SQL Server 7.0 zwraca w tym przypadku błąd 9002. Problem nie dotyczy programu SQL Server 2000.

W programie SQL Server 2000, jeśli dla bazy danych włączono opcję auto-shrink, to w bardzo krótkim czasie może nastąpić próba zwiększenia rozmiaru dziennika, czego jednak nie można zrobić ze względu na równocześnie działającą funkcję auto-shrink. Może to również spowodować fałszywe błędy 9002.

Zwykle automatyczne zwiększanie dziennika transakcji trwa krótko. Jednakże może znacząco się wydłużyć w następujących sytuacjach:
  • Pojedynczy przyrost jest zbyt mały.
  • Serwer jest powolny z rozmaitych względów.
  • Dyski nie są wystarczająco szybkie.

Niezreplikowane transakcje

Rozmiar dziennika transakcji bazy danych publisher może znacząco się zwiększyć w przypadku korzystania z replikacji. Transakcje odnoszące się do replikowanych obiektów są oznaczane jako „do replikacji”. Transakcje takie jako niezakończone nie są usuwane w punkcje sprawdzania lub przy archiwizowaniu dziennika transakcji aż do momentu gdy zadanie log-reader skopiuje transakcję do dystrybucyjnej bazy danych i usunie oznaczenie. Jeśli problem podczas wykonania zadania log-reader uniemożliwia mu odczytanie tych transakcji w bazie danych publisher, to rozmiar dziennika może stale rosnąć wraz z liczbą niezreplikowanych transakcji. Parametr DBCC OPENTRAN języka Transact-SQL umożliwia odnalezienie najstarszej niezreplikowanej transakcji.

Dodatkowe informacje o rozwiązywaniu problemów związanych z niezreplikowanymi transakcjami, można znaleźć w rozdziałach „sp_replcounters” i „sp_repldone” dokumentacji online programu SQL Server.

Aby uzyskać więcej informacji, kliknij następujący numer artykułu w celu wyświetlenia tego artykułu z bazy wiedzy Microsoft Knowledge Base:
306769 FIX: Transaction log of snapshot published database cannot be truncated
240039 FIX: DBCC OPENTRAN does not report replication information
198514 FIX: Restore to new server causes transactions to remain in log

Więcej informacji

Dziennik transakcji dla każdej bazy danych jest przechowywany w zestawie wirtualnych plików dziennika (VLF), których rozmiary są określane przez program SQL Server na podstawie całkowitego rozmiaru dziennika transakcji oraz ustalonego przyrostu przy zwiększaniu rozmiaru dziennika. Rozmiar dziennika jest zawsze zwiększany o wielkość pojedynczego pliku VLF i nie może być skompresowany do mniejszej wartości. Plik VLF może mieć jeden z trzech stanów: ACTIVE, RECOVERABLE, lub REUSABLE.
  • ACTIVE: Aktywna część dziennika rozpoczyna się od najmniejszego numeru kolejnego dziennika (LSN, Log Sequence Number), który odpowiada aktywnej (niezakończonej transakcji). Aktywna część dziennika kończy się na ostatnim zapisanym LSN. Plik VLF zawierający dowolną aktywną część dziennika jest aktywny (niewykorzystana część dziennika nie jest częścią żadnego pliku VLF).
  • RECOVERABLE: Część dziennika poprzedzająca pierwszą aktywną transakcję jest potrzebna tylko do zachowania spójności przy archiwizowaniu dziennika transakcji.
  • REUSABLE: Jeśli nie są tworzone kopie zapasowe dziennika transakcji lub jeśli został on już zarchiwizowany, to serwer SQL może ponownie wykorzystać plik VLF poprzedzający pierwszą aktywną transakcję.
Po napotkaniu końca fizycznego pliku dziennika transakcji program SQL Server rozpoczyna ponowne wykorzystywanie miejsca w pliku, stosując operację CIRCLING BACK od początku pliku. W ten sposób serwer SQL ponownie wykorzystuje obszary pliku dziennika, które nie są już przydatne do tworzenia lub odtwarzania kopii zapasowych. Jeśli wymagane jest zachowanie kolejności kopii zapasowych, to część przed najmniejszym numerem LSN nie może być ponownie zapisana przed obcięciem lub zarchiwizowaniem tych zapisów dziennika. Po wykonaniu kopii zapasowej program SQL Server może powrócić do początku pliku. Od tego momentu ponownie wykorzystywana część dziennika znajduje się między końcem logicznego pliku dziennika a aktywną częścią dziennika.

Aby uzyskać dodatkowe informacje, zobacz temat „Transaction Log Physical Architecture” w dokumentacji SQL Server Books Online. Ponadto przejrzysty diagram i omówienie zagadnienia można znaleźć na s. 190 książki „Inside SQL Server 7.0" (Soukup, Ron. Inside Microsoft SQL Server 7.0, Microsoft Press, 1999), a także na s. 182-186 książki „Inside SQL Server 2000” (Delaney, Kalen. Inside Microsoft SQL Server 2000, Microsoft Press, 2000). Programy SQL Server 7.0 i SQL Server 2000 mają opcje automatycznego zwiększania (autogrow) oraz automatycznego zmniejszania (autoshrink) bazy danych. Opcje te można wykorzystać do skompresowania lub rozszerzenia dziennika transakcji.

Aby uzyskać więcej informacji dotyczących wpływu tych opcji na serwer, kliknij następujący numer artykułu w celu wyświetlenia tego artykułu z bazy wiedzy Microsoft Knowledge Base:
315512 Considerations for Autogrow and Autoshrink configuration in SQL Server
Operacje kompresji i obcięcia pliku dziennika transakcji różnią się od siebie. Kiedy program SQL Server obcina plik dziennika transakcji, oznacz to, że część zawartych w tym pliku danych (np. zakończone transakcje) jest usuwana. Sprawdzając jednak fizyczny rozmiar pliku na dysku (np. w Eksploratorze Windows lub używając polecenia dir), przekonamy się, że nie zmienił się on. Zwolnione miejsce wewnątrz pliku .ldf może być obecnie wykorzystane do zapisania nowych transakcji. Dopiero gdy program SQL Server zmniejszy rozmiar pliku dziennika transakcji, również fizyczny rozmiar tego pliku będzie mniejszy.

Aby uzyskać więcej informacji dotyczących sposobu zmniejszania dzienników transakcji, kliknij następujący numer artykułu w celu wyświetlenia tego artykułu z bazy wiedzy Microsoft Knowledge Base:
256650 How to shrink the SQL Server 7.0 transaction log
272318 Shrinking the transaction log in SQL Server 2000 with DBCC SHRINKFILE
Aby uzyskać więcej informacji dotyczących wykorzystania dzienników transakcji programu SQL Server 6.5, kliknij następujący numer artykułu w celu wyświetlenia tego artykułu z bazy wiedzy Microsoft Knowledge Base:
110139 Causes of SQL transaction log filling up

Właściwości

Numer ID artykułu: 317375 - Ostatnia weryfikacja: 17 maja 2011 - Weryfikacja: 6.0
Informacje zawarte w tym artykule dotyczą:
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2005 Express Edition
  • Microsoft SQL Server 2005 Workgroup Edition
  • Microsoft SQL Server 2000 Standard Edition
  • Microsoft SQL Server 7.0 Standard Edition
Słowa kluczowe: 
kbsqlmanagementtools kbinfo KB317375

Przekaż opinię

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com