Udostępnij przez

Zmniejszanie bazy danych tempdb

Dotyczy:programu SQL ServerAzure SQL Managed Instance

W tym artykule omówiono różne metody, których można użyć do zmniejszenia bazy danych tempdb w programie SQL Server.

Aby zmienić rozmiar obiektu tempdb, można użyć dowolnej z poniższych metod. Pierwsze trzy opcje zostały opisane w tym artykule. Jeśli chcesz użyć programu SQL Server Management Studio (SSMS), postępuj zgodnie z instrukcjami w Zmniejszanie bazy danych.

Metoda Wymaga ponownego uruchomienia? Więcej informacji
ALTER DATABASE Tak Zapewnia pełną kontrolę nad rozmiarem domyślnych plików tempdb (tempdev i templog).
DBCC SHRINKDATABASE Nie Działa na poziomie bazy danych.
DBCC SHRINKFILE Nie Pozwala zmniejszyć poszczególne pliki.
SQL Server Management Studio Nie Zmniejszanie plików bazy danych za pomocą graficznego interfejsu użytkownika.

Uwagi

Domyślnie baza danych tempdb jest skonfigurowana do automatycznego zwiększania w razie potrzeby. W związku z tym ta baza danych może nieoczekiwanie rosnąć wraz z upływem czasu do rozmiaru większego niż żądany rozmiar. Większe tempdb rozmiary baz danych nie wpływają negatywnie na wydajność programu SQL Server.

Po uruchomieniu programu SQL Server ponownie tworzy tempdb przy użyciu kopii bazy danych model i przywraca tempdb do jego ostatniego skonfigurowanego rozmiaru. Skonfigurowany rozmiar jest ostatnim jawnym rozmiarem ustawionym przy użyciu operacji zmiany rozmiaru pliku, takiej jak opcja ALTER DATABASE z opcją MODIFY FILE, lub instrukcja DBCC SHRINKFILE lub DBCC SHRINKDATABASE. W związku z tym, jeśli nie musisz używać różnych wartości lub chcesz natychmiast rozwiązać dużą tempdb bazę danych, możesz poczekać na następne ponowne uruchomienie usługi SQL Server, aby rozmiar się zmniejszył.

Możesz zmniejszyć tempdb, gdy działanie tempdb jest w toku. Jednak mogą wystąpić inne błędy, takie jak blokowanie, zakleszczenia itd., które mogą uniemożliwić ukończenie procesu zmniejszania. Aby upewnić się, że zmniejszenie wartości tempdb powiedzie się, wykonaj tę operację, gdy serwer jest w trybie pojedynczego użytkownika lub gdy zatrzymasz wszystkie tempdb działania.

Program SQL Server rejestruje tylko wystarczającą ilość informacji w dzienniku transakcji tempdb, aby wycofać transakcję, ale nie ponownie wykonać transakcji podczas odzyskiwania bazy danych. Ta funkcja zwiększa wydajność instrukcji INSERT w tempdb. Ponadto nie musisz rejestrować informacji w celu ponownego utworzenia żadnych transakcji, ponieważ tempdb jest tworzony ponownie za każdym razem, gdy ponownie uruchomisz program SQL Server. W związku z tym nie ma transakcji do przesyłania dalej ani wycofywania.

Aby uzyskać więcej informacji na temat zarządzania i monitorowania tempdb, zobacz Planowanie pojemności i Monitoruj użycie tempdb.

Użyj polecenia ALTER DATABASE

Notatka

To polecenie działa tylko na domyślnych tempdb plikach tempdev logicznych i templog. Jeśli dodasz więcej plików do tempdbprogramu , możesz je zmniejszyć po ponownym uruchomieniu programu SQL Server jako usługi. Wszystkie pliki tempdb są tworzone ponownie podczas uruchamiania. Te pliki są jednak puste i można je usunąć. Aby usunąć dodatkowe pliki w programie tempdb, użyj polecenia ALTER DATABASE z opcją REMOVE FILE.

Ta metoda wymaga ponownego uruchomienia programu SQL Server.

Notatka

Możesz nawiązać połączenie z wystąpieniem programu SQL Server przy użyciu dowolnego znanego narzędzia klienckiego programu SQL Server, takiego jak sqlcmd, SQL Server Management Studio (SSMS) lub rozszerzenie MSSQL dla programu Visual Studio Code.

  1. Zatrzymaj program SQL Server.

  2. W wierszu polecenia uruchom instancję w trybie minimalnej konfiguracji. Aby to zrobić, wykonaj następujące kroki:

    1. W wierszu polecenia przejdź do folderu, w którym jest zainstalowany program SQL Server (zastąp <VersionNumber> i <InstanceName> w poniższym przykładzie):

      cd C:\Program Files\Microsoft SQL Server\MSSQL<VersionNumber>.<InstanceName>\MSSQL\Binn

    2. Jeśli wystąpienie jest nazwanym wystąpieniem programu SQL Server, uruchom następujące polecenie (zastąp <InstanceName> w poniższym przykładzie):

      sqlservr.exe -s <InstanceName> -c -f -mSQLCMD

    3. Jeśli wystąpienie jest domyślnym wystąpieniem programu SQL Server, uruchom następujące polecenie:

      sqlservr -c -f -mSQLCMD

      Notatka

      Parametry -c i -f powodują uruchomienie programu SQL Server w trybie minimalnej konfiguracji, który ma rozmiar tempdb 1 MB dla pliku danych i 0,5 MB dla pliku dziennika. Parametr -mSQLCMD uniemożliwia przejęcie połączenia pojedynczego użytkownika przez dowolną inną aplikację niż sqlcmd.

  3. Połącz się z programem SQL Server przy użyciu sqlcmd, a następnie uruchom następujące polecenia Transact-SQL. Zastąp <target_size_in_MB> żądanym rozmiarem:

    ALTER DATABASE tempdb MODIFY FILE
(NAME = 'tempdev', SIZE = <target_size_in_MB>);

ALTER DATABASE tempdb MODIFY FILE
(NAME = 'templog', SIZE = <target_size_in_MB>);

  4. Zatrzymaj program SQL Server. Aby to zrobić, w oknie wiersza polecenia naciśnij Ctrl+C, ponownie uruchom SQL Server jako usługę, a następnie sprawdź rozmiar plików tempdb.mdf i templog.ldf.

Użyj polecenia DBCC SHRINKDATABASE

DBCC SHRINKDATABASE przyjmuje parametr target_percent. Ten parametr określa procent wolnego miejsca, który ma pozostać w pliku bazy danych po zmniejszeniu bazy danych. Jeśli używasz programu DBCC SHRINKDATABASE, może być konieczne ponowne uruchomienie programu SQL Server.

  1. Użyj procedury składowanej sp_spaceused, aby sprawdzić miejsce aktualnie używane przez tempdb program. Następnie oblicz procent wolnego miejsca, który ma być używany jako parametr dla elementu DBCC SHRINKDATABASE. To obliczenie jest oparte na żądanym rozmiarze bazy danych.

    Notatka

    W niektórych przypadkach może być konieczne uruchomienie polecenia sp_spaceused @updateusage = true , aby ponownie obliczyć używane miejsce i pobrać zaktualizowany raport. Aby uzyskać więcej informacji, zobacz sp_spaceused.

    Rozważmy następujący przykład:

    Załóżmy, że tempdb ma dwa pliki: podstawowy plik danych (tempdb.mdf), czyli 1024 MB, a plik dziennika (tempdb.ldf), czyli 360 MB. Załóżmy, że sp_spaceused raportuje, że podstawowy plik danych zawiera 600 MB danych. Załóżmy również, że chcesz zmniejszyć podstawowy plik danych do 800 MB. Oblicz żądaną wartość procentową wolnego miejsca pozostawionego po zmniejszeniu: 800 MB – 600 MB = 200 MB. Teraz podziel 200 MB przez 800 MB, co daje 25 procent, a ta wartość to target_percent. Plik dziennika transakcji jest odpowiednio skurczony, pozostawiając 25 procent, czyli 200 MB wolnego miejsca po skurczeniu bazy danych.

  2. Uruchom następujące polecenie Transact-SQL. Zastąp <target_percent> żądaną wartością procentową:

    DBCC SHRINKDATABASE (tempdb, '<target_percent>');

Polecenie DBCC SHRINKDATABASE ma ograniczenia dotyczące użycia w systemie tempdb. Nie można ustawić rozmiaru docelowego plików danych i dzienników na mniejszy niż rozmiar określony podczas tworzenia bazy danych. Nie można również ustawić go mniejszego niż ostatni rozmiar ustawiony jawnie przy użyciu operacji zmiany rozmiaru pliku, takiej jak ALTER DATABASE w przypadku MODIFY FILE opcji . Innym ograniczeniem DBCC SHRINKDATABASE jest obliczenie parametru target_percentage i jego zależności od bieżącego używanego miejsca.

Użyj polecenia DBCC SHRINKFILE

Użyj polecenia , DBCC SHRINKFILE aby zmniejszyć poszczególne tempdb pliki. DBCC SHRINKFILE zapewnia większą elastyczność niż DBCC SHRINKDATABASE, ponieważ można go używać w jednym pliku bazy danych bez wpływu na inne pliki należące do tej samej bazy danych. DBCC SHRINKFILE target_size przyjmuje parametr . Ten parametr ustawia żądany ostateczny rozmiar pliku bazy danych.

  1. Określ żądany rozmiar pliku danych podstawowych (tempdb.mdf), plik dziennika (templog.ldf) i dodatkowe pliki, które są dodawane do tempdb. Upewnij się, że używane miejsce w plikach jest mniejsze lub równe żądanemu rozmiarowi docelowemu.

  2. Nawiąż połączenie z programem SQL Server przy użyciu programu SSMS, programu Visual Studio Code lub narzędzia sqlcmd. Następnie uruchom następujące polecenia Transact-SQL dla określonych plików bazy danych, które chcesz zmniejszyć. Zastąp <target_size_in_MB> żądanym rozmiarem:

    USE tempdb;
GO

-- This command shrinks the primary data file
DBCC SHRINKFILE (tempdev, '<target_size_in_MB>');
GO

-- This command shrinks the log file, examine the last paragraph.
DBCC SHRINKFILE (templog, '<target_size_in_MB>');
GO

Zaletą DBCC SHRINKFILE jest to, że może zmniejszyć rozmiar pliku do rozmiaru mniejszego niż jego oryginalny rozmiar. Można uruchomić DBCC SHRINKFILE na dowolnych plikach danych lub dziennika. Nie można zmniejszyć bazy danych do rozmiaru mniejszego niż rozmiar bazy danych model.

Błąd 8909 podczas uruchamiania operacji zmniejszania

Jeśli tempdb jest używany i próbujesz go zmniejszyć przy użyciu poleceń DBCC SHRINKDATABASE lub DBCC SHRINKFILE, możesz otrzymywać komunikaty podobne do poniższego wyniku. Dokładny komunikat zależy od używanej wersji programu SQL Server:

Server: Msg 8909, Level 16, State 1, Line 1 Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 0 (type Unknown), page ID (6:8040) contains an incorrect page ID in its page header. The PageId in the page header = (0:0).

Ten błąd nie wskazuje żadnych rzeczywistych uszkodzeń w tempdb. Jednak mogą istnieć inne przyczyny błędów uszkodzenia danych fizycznych, takich jak błąd 8909, i że przyczyny te obejmują problemy podsystemu we/wy. W związku z tym, jeśli błąd występuje poza operacjami zmniejszania, warto przeprowadzić dalsze badania.

Mimo że komunikat 8909 jest zwracany do aplikacji lub do użytkownika, który wykonuje operację zmniejszania, operacje zmniejszania nie kończą się niepowodzeniem.

Powiązana zawartość

  • Last updated on