W tym artykule omówimy SQL Server Auto Shrink właściwość bazy danych, która pozwala SQL Server do automatycznego zmniejszania plików bazy danych, gdy wartość jest skonfigurowana jako True w opcji bazy danych. Automatyczna operacja obkurczania jest wykonywana przez funkcję serwera auto shrink database, która jest głównym tematem tego artykułu.
Aby uniknąć czynności obkurczania plików danych/logów za każdym razem, gdy rozmiar pliku przekracza wolną przestrzeń w pliku danych, DBA (Database Administrator) musi wykonywać kopie zapasowe plików dziennika w regularnych odstępach czasu. Tworzenie kopii zapasowej całej bazy danych nie jest dobrym pomysłem; należy skonstruować lub ustawić również dziennik transakcji. Dzienniki transakcji będą rosły w liczbę, aż zajmą całą dostępną przestrzeń dyskową, jeśli nie wykonasz ich kopii zapasowej. Jeśli wykonasz kopię zapasową bazy danych, zwolni ona wolne miejsce, które może być ponownie wykorzystane. Administrator bazy danych musi zaplanować tworzenie kopii zapasowych dziennika transakcji, aby zmniejszyć rozmiar plików dziennika w odpowiednim rozmiarze.
Funkcja SQL Server Auto Shrink jest domyślnie wyłączona na bazach danych instancji SQL Server. W scenariuszu, w którym mamy kilka stosunkowo małych baz danych, które rosną do większego rozmiaru przez wstawianie nowych krotek lub przez dużą liczbę pustych miejsc utworzonych przez usunięcie kilku krotek, funkcja SQL Server Auto Shrink staje się całkiem przydatna w tej sytuacji. Co więcej, nie musisz się martwić o fragmentację rozmiaru plików bazy danych.
Podczas zmniejszania bazy danych, należy rozważyć operacje zmniejszania zarówno krytycznych, jak i stosunkowo większych baz danych jako część konserwacji. Poza tym, należy unikać uruchamiania funkcji ręcznego zmniejszania; konsekwencje tego są takie, że nigdy nie zostaniemy zapoznani z problemami dotyczącymi nowego lub istniejącego żądania. Jednak kurczenie plików transakcyjnych jest lepsze niż kurczenie plików danych.
Jak aktywować i dezaktywować opcję automatycznego zmniejszania bazy danych SQL Server?
Użytkownicy mogą włączać i wyłączać opcję automatycznego zmniejszania bazy danych za pomocą SSMS i T-SQL na oba sposoby.
Aktywacja automatycznego zmniejszania bazy danych za pomocą SSMS:
Użytkownicy mogą włączyć lub wyłączyć tę opcję z poziomu właściwości bazy danych, z tagiem Auto Shrink. Tutaj True w rozwijanej liście włączy tę opcję dla twojej bazy danych.
Włącz automatyczne obkurczanie bazy danych używając T-SQL:
Użytkownicy mogą wykonać poniższe instrukcje T-SQL aby włączyć lub wyłączyć opcję obkurczania bazy danych. Do powyższej instrukcji T-SQL użyliśmy bazy danych AdventureWorks.
|
1
2
3
4
5
6
|
–Włącz Auto Shrink dla bazy danych AdventureWorks
ALTER DATABASE AdventureWorks SET AUTO_SHRINK ON
GO
—.Wyłączenie opcji Auto Shrink dla bazy danych AdventureWorks
ALTER DATABASE AdventureWorks SET AUTO_SHRINK OFF
GO
|
Wpływ kurczenia się bazy danych na wydajność zapytań
Złe efekty mogą się pojawić po stronie wydajności zapytań, jeśli włączymy opcję auto shrink bazy danych i ustawienia auto-growth razem dla bazy danych. Możemy zachować trochę miejsca na pliki danych do wzrostu, a także uniknąć niektórych częstych zdarzeń auto-growth poprzez ustawienie rozmiaru bazy danych do optymalnej wartości lub przeważnie każda baza danych posiada pewne parametry z włączonymi funkcjami auto-growth.
Dla takich baz danych, funkcje auto shrink muszą być aktywowane przez nas, gdy baza danych jest mniejsza, jedna i nie ma więcej operacji CRUD, więc może sprawić, że kurczenie plików danych jest możliwe i odzyskać wolne miejsce, które zapewniliśmy celowo, aby utrzymać nasze bazy danych z dala od zdarzeń auto-growth. Wolne miejsce zostanie zwolnione automatycznie w plikach danych i plikach dziennika okresowo w round-robin sekwencyjny priorytet przez funkcję Auto shrink, jeśli wiele baz danych włączył tę funkcję.
Dla dużych baz danych, auto-growth, a następnie, auto shrink zostanie wykonana, która spowoduje problemy z wydajnością prowadzić przez fragmentację na poziomie systemu. Podsumowując wszystkie powyższe, dla każdej bazy danych, auto shrink nie powinien być aktywowany z następujących powodów:
- Wykonywanie algorytmów SQL Server auto shrink bez żadnego celu na pewno będzie trwonić zasoby w ogromnej ilości
- Niezależnie od tego, czy wykonujemy SQL Server auto shrink czy manual shrink, oczywiście będzie to powodować fragmentację indeksów, a to w końcu spowoduje, że pliki danych również będą się kurczyć
- Jeśli serwer również wymusza granice podsystemu IO, uruchomienie shrink może je przekroczyć, skutkując długimi kolejkami na dysku i być może timeoutami IO, to zje zasoby IO i CPU serwera w ogromnej ilości
- Wydajność systemu będzie opóźniona przez fragmentację systemu plików na poziomie dysku, co jest znowu bezpośrednim efektem wykonywania kurczących się i rosnących plików danych często
Aby zrozumieć więcej na temat zdarzeń wzrostu i kurczenia w SQL Server, przeczytaj ten artykuł, Uzyskaj szczegóły zdarzeń wzrostu i kurczenia bazy danych SQL Server.
Dla krytycznych baz danych, ręczna operacja zmniejszania może być przeprowadzona na poziomie pliku bazy danych przez eksperta. Ręczna operacja zmniejszania pliku bazy danych może być wykonana po wykonaniu operacji usunięcia, a następnie po odzyskaniu przestrzeni dyskowej. Musimy odbudować pofragmentowane indeksy po wykonaniu operacji zmniejszania, ponieważ operacja zmniejszania może prowadzić do fragmentacji indeksów. Procent fragmentacji indeksu może być sprawdzony przez użytkownika za pomocą instrukcji T-SQL lub DMV. Jednakże, zmniejszanie pliku logu powinno być wykonywane ręcznie w miarę potrzeb i nie powinno być częścią regularnych czynności konserwacyjnych.
W celu regularnego monitorowania użycia dysku, raport użycia dysku może być analizowany przez użytkownika przed wykonaniem operacji ręcznego zmniejszania przy użyciu SSMS, który daje wgląd w dane i informacje o przestrzeni logów dla bazy danych na wyświetlaczu. Jeśli użytkownik chciałby uzyskać pulpit do obliczania ilości wolnego i zarezerwowanego miejsca dla plików bazy danych, wówczas Disk Report staje się bardzo pomocny. Jednakże raport dyskowy pobiera informacje za pomocą SQL Server DMV. Raport dyskowy jest dostępny w poniższym katalogu za pomocą SSMS.
Database >> Reports >> Standard Reports >> Disk Usage
Tutaj mamy Disk Report dla bazy danych. Statystyki w czasie rzeczywistym dla plików danych i plików dziennika mogą być widziane przez użytkownika. Ten raport zawiera podstawowe informacje o całkowitym zarezerwowanym miejscu, zarezerwowanym miejscu dla plików danych, zarezerwowanym miejscu dla dziennika transakcji i zarezerwowanym miejscu dla OLTP w pamięci.
Skurcz bazy danych automatycznie używa zadań SQL Server
Takie skrypty mogą być wykonane przez zaplanowanie zadania przez użytkownika do wykonania operacji kurczenia bazy danych z aktywnością harmonogramu. Pierwszym krokiem skryptu jest znalezienie wolnego miejsca w pliku bazy danych, a następnie obkurczenie tego pliku, jeśli zdefiniowane kryteria wolnego miejsca zostaną spełnione. Wolne miejsce powinno być obliczane za pomocą SQL Server DMV’s.
Zasadniczo, monitorowanie pliku bazy danych jest wykonywane przez administratora bazy danych, który z kolei wykonuje kroki na bazie danych w celu monitorowania rozmiaru pliku bazy danych. Jeśli wstępnie zdefiniowana granica zostanie przekroczona przez dowolny plik, wówczas muszą wykonać takie kroki. Tak więc czynność ta może być zautomatyzowana przy użyciu zadania SQL Server do wykonywania codziennie, co tydzień lub co miesiąc w określonym czasie.
Najlepszą praktyką jest zaplanowanie tego zadania tylko na pliku dziennika bazy danych i monitorowanie wolnego miejsca w pliku danych ręcznie. Ponieważ może to również wpłynąć na wydajność zapytań. SQL Server DMV zwróci plik danych i plik logu w tym samym zestawie wyników, więc typ pliku musi być podzielony w instrukcji T-SQL SQL Server DMV. Przed zmniejszeniem pliku, użytkownik może zastosować logikę zapytania na całkowitą przestrzeń i wolną przestrzeń pliku dziennika. Jeśli kryteria obliczeniowe są dopasowane do właściwości pliku, wtedy plik będzie się kurczył z docelowym rozmiarem pliku.
Sprawdź wolną przestrzeń dla plików bazy danych:
Jeśli takie kryteria pasują do plików bazy danych, na przykład, jeśli wolna przestrzeń jest większa niż (n) MB/GB, jeśli n(%) wolnej przestrzeni w porównaniu do całkowitej przestrzeni, i wiele innych. Jeśli nie ma wolnego miejsca dla pliku logu, a rozmiar pliku jest zbliżony do wartości parametru max. rozmiar pliku, wówczas administrator bazy danych musi sprawdzić w dzienniku transakcji.
Zmniejszanie pliku bazy danych:
|
1
|
DBCC SHRINKFILE(nazwa_pliku, 5120);
|
Tutaj 5120 jest docelowym rozmiarem pliku w MB. Tak więc, plik będzie miał rozmiar 5120 MB. Skonfiguruj zapytanie T-SQL z wymaganymi kryteriami w kroku zadania SQL Server i zaplanuj je w godzinach wyłączenia.
Opcja automatycznego zmniejszania serwera SQL nie może być włączona dla wszystkich baz danych. W szczególności, pomaga ona dla mniejszych baz danych, które wykonują mniej operacji CRUD w porównaniu.
Zakończenie
W tym artykule omówiliśmy właściwość Auto Shrink bazy danych w SQL Server, aby zmniejszyć dane i pliki dziennika oraz usunąć niewykorzystane miejsce. Zmniejszanie bazy danych jest kosztowną operacją i powinno być używane ostrożnie.
- Autor
- Recent Posts
View all posts by Jignesh Raiyani
- Page Life Expectancy (PLE) in SQL Server – July 17, 2020
- Jak zautomatyzować partycjonowanie tabel w SQL Server – 7 lipca, 2020
- Konfigurowanie SQL Server Always On Availability Groups na AWS EC2 – 6 lipca, 2020