Zmniejszanie objętości bazy danych SQL Server 2005
Zawartość strony
Opis zagadnienia
W SQL Server 2005 każdy plik zawarty w bazie danych może zostać zmniejszony w celu usunięcia nieużywanych stron. Pomimo, że aparat bazodanowy ponownie efektywnie wykorzysta przestrzeń, niekiedy nie ma potrzeby, aby plik zajmował tyle samo przestrzeni, co początkowo; zmniejszenie objętości pliku może okazać się konieczne. Zredukowane lub zmniejszone mogą zostać zarówno pliki danych, jak i pliki dziennika transakcji. Pliki bazy danych mogą być zmniejszane ręcznie, jako grupa lub pojedynczo, lub też możliwe jest ustawienie automatycznego zmniejszania bazy danych następującego w określonych odstępach czasu.
Pliki zawsze są zmniejszane od końca. Przykładowo, jeżeli mamy plik o objętości 5 GB i określimy docelowy rozmiar właściwości target_size w instrukcji DBCC SHRINKFILE, aparat bazodanowy uwolni największą możliwą przestrzeń z ostatniego 1 GB pliku. Jeżeli w tej uwalnianej części pliku znajdują się używane strony, aparat bazodanowy najpierw przeniesie te strony do części pliku, która zostaje zachowana. Zmniejszenie bazy danych możliwe jest tylko do momentu, w którym nie ma już wolnej przestrzeni. Przykładowo, jeżeli baza danych o objętości 5 GB zawiera 4 GB danych, a w instrukcji DBCC SHRINKFILE określimy rozmiar docelowy 3 GB, uwolniony zostanie jedynie 1 GB.
Automatyczne zmniejszenie objętości bazy danych
Jeżeli opcja bazy danych AUTO_SHRINK jest włączona, aparat bazodanowy automatycznie zmniejszy bazy danych zawierające wolną przestrzeń. Tę opcję ustawia się za pomocą instrukcji ALTER DATABASE. Domyślnie jest ona wyłączona. Co jakiś czas aparat bazodanowy sprawdza wykorzystanie przestrzeni w każdej bazie danych. Jeżeli jakaś baza danych ma włączoną opcję AUTO_SHRINK, aparat bazodanowy redukuje rozmiar plików znajdujących się w tej bazie danych. Ta czynność zachodzi w tle i nie ma wpływu na aktywność użytkownika w ramach bazy danych.
Ręczne zmniejszanie objętości bazy danych
Możliwe jest ręczne zmniejszanie objętości bazy danych lub plików za pomocą instrukcji DBCC SHRINKDATABASE lub DBCC SHRINKFILE. Jeżeli instrukcja DBCC SHRINKDATABASE lub DBCC SHRINKFILE nie może odzyskać całości określonej przestrzeni w pliku dziennika, wyświetli komunikat informacyjny opisujący czynność, którą należy wykonać, aby możliwe było uwolnienie większej przestrzeni. Więcej informacji na temat zmniejszania objętości plików dziennika znajduje się w dokumencie Shrinking the Transaction Log (j.ang.).
Czynność DBCC SHRINKDATABASE oraz DBCC SHRINKFILE może zostać zatrzymana na każdym etapie procesu, przy czym zachowane zostają wszelkie zakończone zmiany.
Używając instrukcji DBCC SHRINKDATABASE nie można zmniejszyć objętości całej bazy do rozmiaru mniejszego, niż jej pierwotny rozmiar. Dlatego też, jeżeli utworzona została baza danych wielkości 10 MB, która rozrosła się do 100 MB, może ona zostać zmniejszona do najmniejszego rozmiaru 10 MB nawet, gdy wszystkie znajdujące się w niej dane zostaną usunięte.
Jednak możliwe jest zmniejszenie poszczególnych plików bazy danych do rozmiaru mniejszego niż początkowy za pomocą instrukcji DBCC SHRINKFILE. Zamiast zmniejszać bazę danych jako całość, należy zmniejszyć pojedynczo każdy plik.
| Uwaga: |
Nie jest możliwe zmniejszenie bazy danych ani dziennika transakcji podczas tworzenia kopii zapasowej bazy danych lub dziennika transakcji. I odwrotnie, nie jest możliwe stworzenie kopii zapasowej bazy danych ani dziennika transakcji podczas próby zmniejszenia objętości bazy danych lub dziennika transakcji. |
Zmniejszanie objętości dziennika transakcji
Nie istnieją żadne określone granice, w ramach których można zmniejszać plik dziennika transakcji. Rozmiar wirtualnych plików dziennika transakcji w dzienniku określa możliwą redukcję rozmiaru, Dlatego też plik dziennika nie może zostać zmniejszony do rozmiaru mniejszego niż wirtualny plik dziennika. Ponadto, plik dziennika jest zmniejszany stopniowo o przestrzeń równą rozmiarowi wirtualnego pliku dziennika. Przykładowo, plik dziennika transakcji o rozmiarze 1 GB może zawierać pięć wirtualnych plików dziennika po 200 MB każdy. Zmniejszenie pliku dziennika transakcji usunie nieużywane wirtualne pliki dziennika, ale pozostawi przynajmniej dwa wirtualne pliki dziennika. Ponieważ każdy wirtualny plik w tym przykładzie ma 200 MB, dziennik transakcji może zostać zmniejszony do minimalnie 400 MB i jedynie stopniowo o przestrzeń 200 MB. W celu zmniejszenia pliku dziennika transakcji do mniejszego rozmiaru należy utworzyć mały dziennik transakcji i pozwolić mu rozrastać się automatycznie, zamiast tworzyć duży plik dziennika za jednym razem.
W SQL Server 2005, instrukcja DBCC SHRINKDATABASE lub DBCC SHRINKFILE od razu podejmuje próbę zmniejszenia pliku dziennika transakcji do żądanego rozmiaru (zgodnie z zaokrągleniem). Zalecane jest utworzenie kopii zapasowej pliku dziennika przed redukcją objętości pliku w celu zmniejszenia rozmiaru dziennika logicznego i oznaczenie jako nieaktywnych dzienników wirtualnych, które nie zawierają żadnych elementów dziennika logicznego. Więcej informacji na ten temat znajduje się w dokumencie Shrinking the Transaction Log (j.ang.).
Sprawdzone procedury
Podczas planowania zmniejszenia bazy danych lub pliku należy wziąć pod uwagę następujące informacje:
| • | Operacja zmniejszenia pliku jest najbardziej efektywna po operacji tworzącej dużo nieużywanej przestrzeni, takiej jak operacja obcięcia tabeli lub jej usunięcia. |
| • | Większość baz danych wymaga do codziennych operacjo określonej wolnej przestrzeni. Jeżeli po kilkakrotnym zmniejszeniu objętości bazy danych jej rozmiar ponownie się zwiększa oznacza to, że zmniejszona przestrzeń jest wymagana do zwykłych operacji. W takim przypadku, powtarzanie operacji zmniejszania objętości bazy danych nie ma sensu. |
| • | Zmniejszanie objętości nie zachowuje stanu fragmentacji indeksów w bazie danych i ogólnie w pewnym stopniu zwiększa fragmentację. Przykładowo, nie należy zmniejszać bazy danych ani plików danych po przebudowaniu indeksów. Jest to kolejny powód, dla którego nie należy kilkakrotnie zmniejszać objętości bazy danych. |
| • | O ile nie występują specjalne wymagania, nie należy włączać opcji AUTO_SHRINK dla bazy danych. |
Poziomy izolacji i zmniejszanie w oparciu o wersjonowanie wierszy
Możliwe jest zablokowanie operacji zmniejszania przez transakcję działającą w izolacji opartej na wersjonowaniu wierszy. Przykładowo, jeżeli duża operacja zmniejszania działająca w izolacji opartej na wersjonowaniu wierszy jest w toku podczas wykonywania operacji DBCC SHRINK DATABASE, operacja zmniejszania zaczeka do momentu zakończenia operacji usuwania i dopiero wtedy pliki zostaną zmniejszone. Kiedy tak się stanie, operacje DBCC SHRINKFILE oraz DBCC SHRINKDATABASE będą przekazywać komunikat informacyjny (5202 dla SHRINKDATABASE oraz 5203 dla SHRINKFILE) do dziennika błędów serwera SQL co pięć minut przez pierwszą godzinę, a następnie co godzinę od tego momentu. Więcej informacji na ten temat znajduje się w części DBCC SHRINKDATABASE (Transact-SQL) (j.ang.).