Rozwi¹zywanie problemu z zape³nionym dziennikiem transakcji w bazie danych programu SQL Server

W tym artykule opisano czynnoœci, które nale¿y wykonaæ, gdy dzienniki transakcji osi¹gn¹ zbyt du¿y rozmiar. Zape³nienie dzienników transakcji mo¿e uniemo¿liwiæ korzystanie z bazy danych programu Microsoft SQL Server. W tym artykule opisano sposób skracania i zmniejszania rozmiaru dzienników transakcji oraz zapobiegania nieoczekiwanemu zwiêkszeniu rozmiaru tych dzienników.

Skrócenie dziennika transakcji

Aby rozwi¹zaæ problem dotycz¹cy zape³nionych dzienników transakcji, nale¿y skróciæ te dzienniki. W tym celu nale¿y usun¹æ nieaktywne transakcje z dziennika i zmniejszyæ rozmiar pliku dziennika transakcji.

Uwaga Dzienniki transakcji stanowi¹ bardzo istotny czynnik dla zachowania integralnoœci transakcyjnej bazy danych. Z tego wzglêdu nie wolno usuwaæ plików dzienników transakcji nawet po utworzeniu kopii zapasowych bazy danych i dzienników transakcji.


Wiêcej informacji o zmniejszaniu rozmiaru dziennika transakcji mo¿na znaleŸæ w nastêpuj¹cej witrynie firmy Microsoft w sieci Web:

Usuwanie nieaktywnych transakcji z dziennika transakcji

W przypadku zape³nienia dzienników transakcji nale¿y niezw³ocznie utworzyæ kopiê zapasow¹ pliku dziennika transakcji. Podczas tworzenia kopii zapasowej plików dziennika transakcji program SQL Server automatycznie usuwa nieaktywn¹ czêœæ dziennika transakcji. W nieaktywnej czêœci pliku dziennika transakcji zawarte s¹ informacje o zakoñczonych transakcjach. Z tego wzglêdu program SQL Server nie korzysta z tego pliku dziennika podczas procesu odzyskiwania. Miejsce zajmowane przez informacje o nieaktywnych transakcjach jest ponownie wykorzystywane przez programu SQL Server, dziêki czemu dziennik transakcji pozostaje w u¿yciu bez zu¿ywania dodatkowego miejsca.

Wiêcej istotnych informacji dotycz¹cych tworzenia i przywracania kopii zapasowych dzienników transakcji zawarto w nastêpuj¹cych tematach w dokumentacji SQL Server Books Online:

• Kopie zapasowe dziennika transakcji
• Tworzenie i przywracanie kopii zapasowej dziennika transakcji

Nieaktywne transakcje mo¿na te¿ usun¹æ z pliku dziennika transakcji przy u¿yciu metody Obetnij. Wiêcej informacji o obcinaniu dzienników transakcji zawarto w temacie „Obcinanie dziennika transakcji” w dokumentacji SQL Server Books Online.

Wa¿ne W przypadku przeprowadzenia rêcznego obcinania plików dziennika transakcji przed utworzeniem kopii zapasowej dziennika transakcji nale¿y utworzyæ pe³n¹ kopiê zapasow¹ bazy danych.

Aby uzyskaæ wiêcej informacji o problemach, które mog¹ wyst¹piæ w przypadku obciêcia plików dziennika transakcji, nale¿y klikn¹æ nastêpuj¹cy numer artyku³u w celu wyœwietlenia tego artyku³u z bazy wiedzy Microsoft Knowledge Base:

Zmniejszenie rozmiaru dziennika transakcji

Utworzenie kopii zapasowej i zastosowanie metody Obetnij nie powoduje ograniczenia rozmiaru pliku dziennika. W tym celu nale¿y zmniejszyæ rozmiar pliku dziennika transakcji. Aby ograniczyæ rozmiar pliku dziennika transakcji do okreœlonej wartoœci i usun¹æ nieu¿ywane strony, nale¿y u¿yæ operacji DBCC SHRINKFILE. Przy u¿yciu instrukcji DBCC SHRINKFILE jêzyka Transact-SQL mo¿na skróciæ plik dziennika tylko o jego nieaktywn¹ czêœæ.

Uwaga Zastosowanie samej instrukcji DBCC SHRINKFILE jêzyka Transact-SQL nie pozwala obci¹æ dziennika ani zmniejszyæ u¿ywanego miejsca w pliku dziennika.

Wiêcej informacji o zmniejszaniu rozmiaru plików dziennika transakcji mo¿na znaleŸæ w nastêpuj¹cych tematach w dokumentacji SQL Server Books Online:

• Zmniejszanie rozmiaru dziennika transakcji
• DBCC SHRINKFILE

Aby uzyskaæ wiêcej informacji na temat zmniejszania rozmiaru plików dziennika transakcji w programie SQL Server 2000, nale¿y klikn¹æ nastêpuj¹cy numer artyku³u w celu wyœwietlenia tego artyku³u z bazy wiedzy Microsoft Knowledge Base: Aby uzyskaæ wiêcej informacji o problemach, które mog¹ wyst¹piæ podczas zmniejszania plików dziennika transakcji, nale¿y klikaæ nastêpuj¹ce numery artyku³ów w celu wyœwietlenia tych artyku³ów z bazy wiedzy Microsoft Knowledge Base:

Zapobiegniêcie nieoczekiwanemu zwiêkszeniu rozmiaru plików dziennika transakcji

Aby zapobiec nieoczekiwanemu zwiêkszaniu siê rozmiaru plików dziennika transakcji, nale¿y u¿yæ jednej z poni¿szych metod:

• Okreœlenie du¿ej wartoœci w ustawieniu rozmiaru plików dziennika transakcji w celu unikniêcia automatycznego rozszerzania plików dziennika transakcji.
• Skonfigurowanie automatycznego rozszerzania plików dziennika transakcji przy u¿yciu jednostek pamiêci zamiast wartoœci procentowych po szczegó³owej ocenie optymalnego rozmiaru pamiêci.
  
  Aby uzyskaæ wiêcej informacji o istotnych zagadnieniach dotycz¹cych konfigurowania opcji autogrow, nale¿y klikn¹æ nastêpuj¹cy numer artyku³u w celu wyœwietlenia tego artyku³u z bazy wiedzy Microsoft Knowledge Base:
  315512

  (http://support.microsoft.com/kb/315512/pl/ )

  Zagadnienia dotycz¹ce konfigurowania opcji autogrow i autoshrink
• Zmiana modelu odzyskiwania. W przypadku wyst¹pienia awarii lub uszkodzenia danych nale¿y przeprowadziæ odzyskiwanie bazy danych w sposób umo¿liwiaj¹cy zachowanie spójnoœci danych i integralnoœci transakcyjnej w bazie danych. W zale¿noœci od tego, jak krytyczne dane s¹ przechowywane w bazie danych, mo¿na u¿yæ jednego z nastêpuj¹cych modeli odzyskiwania w celu okreœlenia sposobu tworzenia kopii zapasowej danych i zagro¿enia zwi¹zanego z utrat¹ danych:
  • Model odzyskiwania prostego
  • Model odzyskiwania pe³nego
  • Model odzyskiwania z niepe³nym dziennikiem
  Przy u¿yciu modelu odzyskiwania prostego mo¿na przywróciæ najnowsz¹ kopiê zapasow¹ bazy danych. Modele odzyskiwania pe³nego i z niepe³nym dziennikiem umo¿liwiaj¹ przywrócenie bazy danych do punktu, w którym wyst¹pi³ b³¹d. W tym celu u¿ywane s¹ kopie zapasowe pliku dziennika transakcji.
  
  Domyœlnie w programach SQL Server 2000 i SQL Server 2005 dla bazy danych programu SQL Server jest ustawiony model odzyskiwania pe³nego. Dla tego modelu regularnie s¹ tworzone kopie zapasowe dziennika transakcji, aby zapobiec nadmiernemu rozrastaniu siê pliku dziennika transakcji w stosunku do wielkoœci bazy danych. Jeœli jednak kopie zapasowe dziennika transakcji nie s¹ regularnie tworzone, rozmiar pliku tego dziennika zwiêksza siê a¿ do zape³nienia dysku, co z kolei mo¿e uniemo¿liwiæ wykonywanie jakichkolwiek operacji modyfikowania danych w bazie danych programu SQL Server.
  
  Jeœli pliki dziennika transakcji nie bêd¹ u¿ywane podczas operacji odzyskiwania awaryjnego, mo¿na zmieniæ model odzyskiwania na prosty.
• Regularne tworzenie kopii zapasowych plików dziennika transakcji i usuwanie nieaktywnych transakcji z dziennika transakcji.
• Zaprojektowanie transakcji o ma³ych rozmiarach.
• Upewnienie siê, ¿e niewykonane transakcje nie kontynuuj¹ dzia³ania w nieskoñczonoœæ.
• Zaplanowanie codziennego wykonywania aktualizacji statystyki.
• Defragmentowanie indeksów w celu zwiêkszenia wydajnoœci obs³ugi obci¹¿eñ w œrodowisku produkcyjnym przy u¿yciu instrukcji DBCC INDEXDEFRAG jêzyka Transact-SQL, a nie instrukcji DBCC DBREINDEX. Uruchomienie instrukcji DBCC DBREINDEX mo¿e spowodowaæ znaczne zwiêkszenie rozmiaru dziennika transakcji, jeœli w bazie danych programu SQL Server jest zastosowany tryb odzyskiwania pe³nego. Ponadto instrukcja DBCC INDEXDEFRAG nie powoduje d³ugotrwa³ego stosowania blokad, w odró¿nieniu od instrukcji DBCC DBREINDEX.
  
  Wiêcej informacji na temat defragmentowania indeksów w programie SQL Server 2000 mo¿na znaleŸæ w nastêpuj¹cej witrynie firmy Microsoft w sieci Web:
  Najlepsze rozwi¹zania dotycz¹ce defragmentacji indeksu w programie Microsoft SQL Server 2000

  (http://technet.microsoft.com/pl-pl/library/cc966523.aspx)
  Jeœli instrukcja DBCC DBREINDEX musi zostaæ uruchomiona jako zadanie w ramach planu konserwacji bazy danych, to nale¿y podzieliæ to zadanie na kilka mniejszych. Dodatkowo konieczne jest czêste tworzenie kopii zapasowych dzienników transakcji pomiêdzy wykonywaniem poszczególnych zadañ.

Wiêcej informacji o plikach dziennika transakcji

W programach SQL Server 2000 i SQL Server 2005 ka¿da baza danych zawiera co najmniej jeden plik danych i jeden plik dziennika transakcji. Program SQL Server przechowuje dane w fizycznej postaci w pliku danych. W pliku dziennika transakcji zapisywane s¹ szczegó³owe informacje o wszystkich modyfikacjach wprowadzanych w bazie danych programu SQL Server i transakcjach wykonywanych w ramach ka¿dej modyfikacji. Integralnoœæ transakcyjna stanowi podstawow¹ charakterystykê wewnêtrzn¹ programu SQL Server, dlatego nie mo¿na wy³¹czyæ rejestrowania informacji dotycz¹cych transakcji.

Plik dziennika transakcji jest podzielony w sposób logiczny na mniejsze segmenty nazywane wirtualnymi plikami dziennika. W programie SQL Server 2000 mo¿na skonfigurowaæ rozszerzanie pliku dziennika transakcji odpowiednio do potrzeb. Dziennik transakcji mo¿e byæ rozszerzany do limitu okreœlonego przez u¿ytkownika lub do zu¿ycia ca³ego dostêpnego miejsca na dysku. Wszystkie modyfikacje wprowadzane przez program SQL Server, które maj¹ wp³yw na rozmiar pliku dziennika transakcji, na przyk³ad obciêcie lub zwiêkszenie plików dziennika transakcji, s¹ wykonywane w jednostkach wirtualnych plików dziennika.

Jeœli plik dziennika transakcji skojarzony z baz¹ danych programu SQL Server zostanie zape³niony i ustawiono opcjê automatycznego zwiêkszania plików dziennika transakcji, to plik dziennika transakcji zostanie zwiêkszony o kolejne jednostki wirtualnych plików dziennika. Rozmiar pliku dziennika transakcji mo¿e osi¹gn¹æ bardzo du¿¹ wartoœæ, co powoduje wyczerpanie miejsca na dysku. Zape³nienie ca³ego wolnego miejsca na dysku przez nadmiernie zwiêkszony plik dziennika transakcji uniemo¿liwia wykonywanie jakichkolwiek operacji modyfikowania danych w bazie danych. Dodatkowo z powodu braku miejsca na rozszerzenie dziennika transakcji baza danych mo¿e zostaæ oznaczona w programie SQL Server jako podejrzana.

Aby uzyskaæ wiêcej informacji o scenariuszach, w których plik dziennika transakcji mo¿e zostaæ nieoczekiwanie zwiêkszony, nale¿y klikn¹æ nastêpuj¹cy numer artyku³u w celu wyœwietlenia tego artyku³u z bazy wiedzy Microsoft Knowledge Base:

Wiêcej informacji o rozwi¹zywaniu problemów dotycz¹cych wymagania dodatkowego miejsca na dysku podczas procesu odzyskiwania mo¿na znaleŸæ w temacie „Niewystarczaj¹ca iloœæ miejsca” w dokumentacji SQL Server Books Online. Dodatkowe informacje o architekturze dziennika transakcji mo¿na znaleŸæ w nastêpuj¹cych tematach w dokumentacji SQL Server Books Online:

• Architektura dziennika transakcji
• Architektura logiczna dziennika transakcji
• Architektura fizyczna dziennika transakcji

Wiêcej informacji o modelach odzyskiwania w programie SQL Server 2000 zawarto w nastêpuj¹cych tematach w dokumentacji SQL Server Books Online:

• Wybieranie modelu odzyskiwania
• Odzyskiwanie proste
• Odzyskiwanie pe³ne
• Odzyskiwanie z niepe³nym dziennikiem
• Prze³¹czanie modeli odzyskiwania