Udostępnij za pośrednictwem

DBCC SHRINKDATABASE (Transact-SQL)

  • Artykuł

Dotyczy:sql ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse Analytics

Zmniejsza rozmiar plików danych i dzienników w określonej bazie danych.

Notatka

Operacje zmniejszania nie powinny być traktowane jako regularne operacje konserwacji. Pliki danych i dzienników, które rosną z powodu regularnych, cyklicznych operacji biznesowych nie wymagają operacji zmniejszania.

Transact-SQL konwencje składni

Składnia

Składnia dla programu SQL Server:

DBCC SHRINKDATABASE   
( database_name | database_id | 0   
     [ , target_percent ]   
     [ , { NOTRUNCATE | TRUNCATEONLY } ]   
)  
[ WITH 

    {     
         [ WAIT_AT_LOW_PRIORITY 
            [ ( 
                  <wait_at_low_priority_option_list>
             )] 
         ] 
         
         [ , NO_INFOMSGS]
    }
]

< wait_at_low_priority_option_list > ::=  
    <wait_at_low_priority_option>
    | <wait_at_low_priority_option_list> , <wait_at_low_priority_option>

< wait_at_low_priority_option > ::=
  ABORT_AFTER_WAIT = { SELF | BLOCKERS }

Składnia usługi Azure Synapse Analytics:

DBCC SHRINKDATABASE
( database_name
     [ , target_percent ]
)
[ WITH NO_INFOMSGS ]

Argumenty

database_name | database_id | 0

Nazwa bazy danych lub identyfikator, który ma zostać skurczona. 0 określa, że jest używana bieżąca baza danych.

target_percent

Procent wolnego miejsca, który ma pozostać w pliku bazy danych po zakończeniu operacji zmniejszania.

NOTRUNCATE

Przenosi przypisane strony z końca pliku do nieprzypisanych stron przed plikiem. Ta akcja kompaktuje dane w pliku. target_percent jest opcjonalne. Usługa Azure Synapse Analytics nie obsługuje tej opcji.

Wolne miejsce na końcu pliku nie jest zwracane do systemu operacyjnego, a rozmiar fizyczny pliku nie zmienia się. W związku z tym baza danych nie wydaje się zmniejszać, gdy określisz NOTRUNCATE.

NOTRUNCATE dotyczy tylko plików danych. NOTRUNCATE nie ma wpływu na plik dziennika.

TRUNCATEONLY

Zwalnia całe wolne miejsce na końcu pliku do użytku przez system operacyjny. Nie przenosi żadnych stron wewnątrz pliku. Plik danych zmniejsza się tylko do ostatniego przypisanego zakresu. Ignoruje target_percent, jeśli określono TRUNCATEONLY. Usługa Azure Synapse Analytics nie obsługuje tej opcji.

DBCC SHRINKDATABASE z opcją TRUNCATEONLY wpływa tylko na plik dziennika transakcji bazy danych. Aby obcinać plik danych, użyj DBCC SHRINKFILE zamiast tego. Aby uzyskać więcej informacji, zobacz DBCC SHRINKFILE.

Z OPCJĄ NO_INFOMSGS

Tłumi wszystkie komunikaty informacyjne, które mają poziomy istotności od 0 do 10.

WAIT_AT_LOW_PRIORITY z operacjami zmniejszania

Dotyczy: SQL Server 2022 (16.x) i nowsze wersje, Azure SQL Database, Azure SQL Managed Instance

Funkcja oczekiwania na niskim priorytcie zmniejsza rywalizację o blokadę. Aby uzyskać więcej informacji, zobacz Zrozumienie problemów z współbieżnością w DBCC SHRINKDATABASE.

Ta funkcja jest podobna do WAIT_AT_LOW_PRIORITY przy operacjach indeksowania online, ale istnieją pewne różnice.

  • Nie można określić opcji ABORT_AFTER_WAITNONE.

CZEKAJ_Z_NISKIM_PIERWSZEŃSTWEM

Gdy polecenie zmniejszania jest wykonywane w trybie WAIT_AT_LOW_PRIORITY, nowe zapytania wymagające blokad stabilności schematu (Sch-S) nie są blokowane przez oczekującą operację zmniejszania, dopóki operacja ta nie przestaje czekać i nie zacznie się wykonywać. Operacja zmniejszania jest wykonywana, gdy jest w stanie uzyskać blokadę modyfikowania schematu (blokadaSch-M). Jeśli nowa operacja zmniejszania w trybie WAIT_AT_LOW_PRIORITY nie może uzyskać blokady z powodu długotrwałego zapytania, domyślnie wygaśnie po 1 minucie i zakończy się bez błędu.

Jeśli nowa operacja zmniejszania w trybie WAIT_AT_LOW_PRIORITY nie może uzyskać blokady z powodu długotrwałego zapytania, wówczas operacja ta zakończy się przekroczeniem limitu czasu po 1 minucie domyślnie i zakończy się bez błędu. Dzieje się tak, jeśli operacja zmniejszania nie może uzyskać blokady Sch-M z powodu współbieżnych zapytań lub takich, które przechowują blokady Sch-S. Gdy wystąpi przekroczenie limitu czasu, błąd 49516 jest wysyłany do dziennika błędów programu SQL Server, na przykład: Msg 49516, Level 16, State 1, Line 134 Shrink timeout waiting to acquire schema modify lock in WLP mode to process IAM pageID 1:2865 on database ID 5. Spróbuj ponownie wykonać operację zmniejszania w trybie WAIT_AT_LOW_PRIORITY.

ABORT_AFTER_WAIT = [ SELF | BLOKUJĄCY ]

  • JA

    SELF jest opcją domyślną. Zakończ operację zmniejszania bazy danych, która jest obecnie wykonywana bez podejmowania żadnej akcji.

  • BLOKERY

    Zabij wszystkie transakcje użytkownika, które blokują operację zmniejszania bazy danych, aby operacja mogła kontynuować. Opcja BLOCKERS wymaga, aby logowanie miało uprawnienia ALTER ANY CONNECTION.

Zestaw wyników

W poniższej tabeli opisano kolumny w zestawie wyników.

Nazwa kolumny Opis
DbId Numer identyfikacyjny bazy danych dla pliku, który silnik bazy danych próbował zmniejszyć.
FileId Numer identyfikacyjny pliku, który silnik bazy danych próbował zmniejszyć.
CurrentSize Liczba stron 8 KB, które obecnie zajmuje plik.
MinimumSize Liczba 8 KB stron, które plik może zajmować co najmniej. Ta wartość odpowiada minimalnemu rozmiarowi lub pierwotnie utworzonemu rozmiarowi pliku.
UsedPages Liczba stron 8 KB używanych obecnie przez plik.
EstimatedPages Liczba stron o rozmiarze 8 KB, do których według szacunków silnika bazy danych plik może zostać zredukowany.

Notatka

Silnik bazy danych nie wyświetla wierszy dla tych plików, które nie zostały zmniejszone.

Uwagi

Aby zmniejszyć wszystkie pliki danych i dzienników dla określonej bazy danych, wykonaj polecenie DBCC SHRINKDATABASE. Aby zmniejszyć jeden plik danych lub plik dziennika naraz dla określonej bazy danych, wykonaj polecenie DBCC SHRINKFILE.

Aby wyświetlić bieżącą ilość wolnego (nieprzydzielonego) miejsca w bazie danych, uruchom polecenie sp_spaceused.

DBCC SHRINKDATABASE operacje można zatrzymać w dowolnym momencie procesu, a wszystkie ukończone prace są przechowywane.

Baza danych nie może być mniejsza niż skonfigurowany minimalny rozmiar bazy danych. Minimalny rozmiar jest określany podczas tworzenia bazy danych. Alternatywnie, minimalny rozmiar może odpowiadać ostatniemu rozmiarowi jawnie ustawionemu za pomocą operacji zmiany rozmiaru pliku. Operacje takie jak DBCC SHRINKFILE lub ALTER DATABASE to przykłady operacji zmiany rozmiaru pliku.

Rozważmy, że baza danych jest pierwotnie tworzona o rozmiarze 10 MB. Następnie rośnie do 100 MB. Najmniejszą bazę danych można zmniejszyć do 10 MB, nawet jeśli wszystkie dane w bazie danych zostały usunięte.

Określ opcję NOTRUNCATE lub opcję TRUNCATEONLY po uruchomieniu DBCC SHRINKDATABASE. Jeśli nie, wynik jest taki sam, jak gdybyśmy uruchomili operację DBCC SHRINKDATABASE z NOTRUNCATE, a następnie uruchomili operację DBCC SHRINKDATABASE z TRUNCATEONLY.

Zmniejszona baza danych nie musi być w trybie pojedynczego użytkownika. Inni użytkownicy mogą pracować w bazie danych, gdy zostanie zmniejszona jej wielkość, w tym w bazach danych systemowych.

Nie można zmniejszyć bazy danych podczas tworzenia kopii zapasowej bazy danych. Z drugiej strony nie można utworzyć kopii zapasowej bazy danych, gdy trwa operacja zmniejszania bazy danych.

Po określeniu przy użyciu WAIT_AT_LOW_PRIORITY Sch-M żądania blokowania operacji zmniejszania czeka z niskim priorytetem podczas wykonywania polecenia przez jedną minutę. Jeśli operacja zostanie zablokowana przez czas trwania, zostanie wykonana określona akcja ABORT_AFTER_WAIT.

W pulach SQL usługi Azure Synapse uruchamianie polecenia zmniejszania nie jest zalecane, ponieważ jest to intensywna operacja we/wy i może spowodować, że dedykowana pula SQL (dawniej SQL DW) zostanie przełączona w tryb offline. Ponadto, po uruchomieniu tego polecenia, będą konsekwencje kosztowe związane z migawkami magazynu danych.

Znane problemy

Dotyczy: SQL Server, Azure SQL Database, Azure SQL Managed Instance, Dedykowana pula SQL usługi Azure Synapse Analytics

  • Obecnie kolumny używające typów danych LOB (varbinary(max), varchar(max)i nvarchar(max)) w skompresowanych segmentach magazynu kolumn nie mają wpływu na DBCC SHRINKDATABASE i DBCC SHRINKFILE.

Jak działa baza danych DBCC SHRINKDATABASE

DBCC SHRINKDATABASE zmniejsza pliki danych dla poszczególnych plików, ale zmniejsza pliki dziennika tak, jakby wszystkie pliki dziennika istniały w jednej ciągłej puli dzienników. Pliki są zawsze zmniejszane od końca.

Załóżmy, że masz kilka plików dziennika, plik danych i bazę danych o nazwie mydb. Pliki danych i dziennika to 10 MB, a plik danych zawiera 6 MB danych. Aparat bazy danych oblicza rozmiar docelowy dla każdego pliku. Ta wartość to rozmiar, do którego plik ma zostać skurczony. Gdy DBCC SHRINKDATABASE jest określony z target_percent, aparat bazy danych oblicza rozmiar docelowy tak, aby była to ilość miejsca target_percent wolnego w pliku po zmniejszeniu.

Jeśli na przykład określisz target_percent 25 w celu zmniejszenia rozmiaru mydb, silnik bazy danych oblicza rozmiar docelowy pliku danych na 8 MB (6 MB danych i 2 MB wolnego miejsca). W związku z tym aparat bazy danych przenosi wszystkie dane z ostatniego 2 MB pliku danych do dowolnego wolnego miejsca w pierwszym 8 MB pliku danych, a następnie zmniejsza plik.

Załóżmy, że plik danych mydb zawiera 7 MB danych. Określenie target_percent na 30 pozwala na zredukowanie tego pliku danych do wolnej pojemności 30%. Jednak określenie target_percent 40 nie powoduje zmniejszenia pliku danych, ponieważ nie można utworzyć wystarczającej ilości wolnego miejsca w aktualnym rozmiarze pliku danych.

Możesz pomyśleć o tym problemie w inny sposób: 40 procent chciało wolnego miejsca + 70 procent pełnego pliku danych (7 MB na 10 MB) wynosi ponad 100 procent. Każda target_percent większa niż 30 nie spowoduje zmniejszenia pliku danych. Nie zmniejszy się, ponieważ pożądany procent wolnej przestrzeni plus bieżący procent zajmowany przez plik danych wynosi ponad 100 procent.

W przypadku plików dziennika aparat bazy danych używa target_percent do obliczania rozmiaru docelowego dla całego pliku dziennika. Dlatego target_percent jest ilością wolnego miejsca w dzienniku po operacji zmniejszania. Rozmiar docelowy całego dziennika jest następnie tłumaczony na rozmiar docelowy dla każdego pliku dziennika.

DBCC SHRINKDATABASE próbuje natychmiast zmniejszyć rozmiar każdego fizycznego pliku dziennika do rozmiaru docelowego. Załóżmy, że żadna część dziennika logicznego nie pozostaje w dziennikach wirtualnych poza docelowym rozmiarem pliku dziennika. Następnie plik zostanie pomyślnie obcięty i DBCC SHRINKDATABASE zakończy się bez żadnych komunikatów. Jeśli jednak część dziennika logicznego pozostanie w dziennikach wirtualnych poza rozmiarem docelowym, aparat bazy danych zwalnia jak najwięcej miejsca, a następnie wysyła komunikat informacyjny. Komunikat zawiera opis akcji wymaganych do przeniesienia dziennika logicznego z dzienników wirtualnych na końcu pliku. Po uruchomieniu akcji można użyć DBCC SHRINKDATABASE, aby zwolnić pozostałe miejsce.

Plik dziennika może zostać ograniczony tylko do granicy pliku dziennika wirtualnego. Dlatego zmniejszanie pliku dziennika do rozmiaru mniejszego niż rozmiar pliku dziennika wirtualnego może być niemożliwe. Może się okazać niemożliwe, nawet jeśli coś nie jest używane. Rozmiar pliku dziennika wirtualnego jest wybierany dynamicznie przez aparat bazy danych podczas tworzenia lub rozszerzania plików dziennika.

Zrozum problemy ze współbieżnością w DBCC SHRINKDATABASE

Polecenia zmniejszania bazy danych i zmniejszania pliku mogą prowadzić do problemów ze współbieżnością, zwłaszcza w przypadku aktywnej konserwacji, takiej jak ponowne kompilowanie indeksów lub w zajętych środowiskach OLTP. Gdy aplikacja wykonuje zapytania względem tabel bazy danych, te zapytania będą uzyskiwać i utrzymywać blokadę stabilności schematu (Sch-S), dopóki zapytania nie zakończą swoich operacji. Podczas próby odzyskania miejsca podczas zwykłego użycia operacje zmniejszania bazy danych i zmniejszania plików wymagają obecnie blokady modyfikacji schematu (Sch-M) podczas przenoszenia lub usuwania stron mapy alokacji indeksu (IAM), blokując blokady Sch-S wymagane przez zapytania użytkownika. W związku z tym długotrwałe zapytania blokują operację zmniejszania do momentu ukończenia zapytań. Oznacza to, że wszystkie nowe zapytania wymagające blokad Sch-S są również w kolejce za czekającą operacją zmniejszania i również będą blokowane, pogłębiając problem współbieżności. Może to znacząco wpłynąć na wydajność zapytań aplikacji, co spowoduje również trudności z ukończeniem niezbędnej konserwacji w celu zmniejszenia plików bazy danych. Wprowadzona w systemie SQL Server 2022 (16.x) funkcja oczekiwania na zmniejszanie przy niskim priorytecie (WLP) rozwiązuje ten problem, przyjmując blokadę modyfikacji schematu w trybie WAIT_AT_LOW_PRIORITY. Aby uzyskać więcej informacji, zobacz WAIT_AT_LOW_PRIORITY wraz z operacjami zmniejszania.

Aby uzyskać więcej informacji na temat blokad Sch-S i Sch-M, zobacz przewodnik dotyczący blokowania transakcji i przechowywania wersji wierszy.

Najlepsze rozwiązania

Podczas planowania zmniejszenia bazy danych należy wziąć pod uwagę następujące informacje:

  • Operacja zmniejszania jest najbardziej efektywna po operacji, która tworzy nieużywane miejsce, takie jak skrócenie tabeli lub usunięcie tabeli.
  • W przypadku codziennych operacji większość baz danych wymaga wolnego miejsca. Jeśli wielokrotnie zmniejszasz plik bazy danych i zauważasz, że rozmiar bazy danych ponownie wzrośnie, oznacza to, że wolne miejsce jest wymagane do regularnych operacji. W takich przypadkach wielokrotne zmniejszanie pliku bazy danych to zmarnowana operacja. Zdarzenia automatycznego zwiększania rozmiaru, niezbędne do powiększenia pliku bazy danych, obniżają wydajność.
  • Operacja zmniejszania nie zachowuje stanu fragmentacji indeksów w bazie danych i zazwyczaj prowadzi do pewnego wzrostu fragmentacji. Jest to kolejny powód, dla którego nie można wielokrotnie zmniejszać bazy danych.
  • Jeśli nie masz określonego wymagania, nie ustawiaj opcji AUTO_SHRINK bazy danych na Włączone.

Rozwiązywanie problemów

Istnieje możliwość zablokowania operacji zmniejszenia przez transakcję uruchomioną w poziomie izolacji opartym na wersjonowaniu wierszy. Na przykład, gdy operacja DBCC SHRINKDATABASE jest wykonywana, duża operacja usuwania uruchomiona w ramach poziomu izolacji opartej na wersjonowaniu wierszy jest w toku. W takiej sytuacji operacja zmniejszania czeka na zakończenie operacji usuwania, zanim zmniejszy pliki. Gdy operacja zmniejszania czeka, operacje DBCC SHRINKFILE i DBCC SHRINKDATABASE wyświetlają komunikat informacyjny (5202 dla SHRINKDATABASE i 5203 dla SHRINKFILE). Ten komunikat jest wyświetlany w dzienniku błędów programu SQL Server co pięć minut w ciągu pierwszej godziny, a następnie co nadchodzącą godzinę. Jeśli na przykład dziennik błędów zawiera następujący komunikat o błędzie:

DBCC SHRINKDATABASE for database ID 9 is waiting for the snapshot
transaction with timestamp 15 and other snapshot transactions linked to
timestamp 15 or with timestamps older than 109 to finish.

Błąd ten oznacza, że transakcje migawkowe, które mają znaczniki czasu starsze niż 109, zablokują operację zmniejszania. Ta transakcja jest ostatnią, którą zakończyła operacja pomniejszania. Wskazuje również, że kolumny transaction_sequence_num lub first_snapshot_sequence_num w sys.dm_tran_active_snapshot_database_transactions (Transact-SQL) widoku dynamicznego zarządzania zawierają wartość 15. Kolumna transaction_sequence_num lub first_snapshot_sequence_num w widoku może zawierać liczbę mniejszą niż ostatnia transakcja ukończona przez operację zmniejszania (109). Jeśli tak, operacja zmniejszania czeka na zakończenie tych transakcji.

Aby rozwiązać ten problem, możesz wykonać jedno z następujących zadań:

  • Zakończ transakcję blokującą operację zmniejszania.
  • Zakończ operację zmniejszania. Wszystkie ukończone prace są przechowywane.
  • Nie rób nic i pozwól, aby operacja zmniejszania czekała, aż blokująca transakcja się zakończy.

Uprawnienia

Wymaga członkostwa w przydzielonej roli serwera sysadmin lub w przydzielonej roli bazy danych db_owner.

Przykłady

A. Zmniejszanie bazy danych i określanie procentu wolnego miejsca

Poniższy przykład zmniejsza rozmiar plików danych i dzienników w bazie danych użytkownika UserDB, aby zapewnić 10 procent wolnego miejsca w bazie danych.

DBCC SHRINKDATABASE (UserDB, 10);
GO

B. Truncowanie bazy danych

Poniższy przykład zmniejsza pliki danych i dzienników w przykładowej bazie danych AdventureWorks2022 do ostatniego przypisanego zakresu.

DBCC SHRINKDATABASE (AdventureWorks2022, TRUNCATEONLY);

C. Zmniejszanie bazy danych usługi Azure Synapse Analytics

DBCC SHRINKDATABASE (database_A);
DBCC SHRINKDATABASE (database_B, 10);

D. Zmniejszanie bazy danych przy użyciu WAIT_AT_LOW_PRIORITY

Poniższy przykład próbuje zmniejszyć rozmiar plików danych i dzienników w bazie danych AdventureWorks2022, aby umożliwić 20% wolnego miejsca w bazie danych. Jeśli nie można uzyskać blokady w ciągu jednej minuty, operacja zmniejszania zostaje przerwana.

DBCC SHRINKDATABASE ([AdventureWorks2022], 20) WITH WAIT_AT_LOW_PRIORITY (ABORT_AFTER_WAIT = SELF);

Powiązana zawartość