Udostępnij za pośrednictwem

Zmniejszanie bazy danych tempdb

  • Artykuł

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 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ąć w czasie 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 tempdb zostanie ponownie utworzona przy użyciu kopii bazy danych model, a tempdb zostanie zresetowana do ostatniego skonfigurowanego rozmiaru. Skonfigurowany rozmiar to ostatni jawny rozmiar, który został ustawiony przy użyciu operacji zmiany rozmiaru pliku, takiej jak ALTER DATABASE, która używa opcji MODIFY FILE lub instrukcji DBCC SHRINKFILE lub DBCC SHRINKDATABASE. W związku z tym, o ile nie trzeba używać różnych wartości lub uzyskać natychmiastowe rozwiązanie dla dużej tempdb bazy danych, możesz poczekać na następne ponowne uruchomienie usługi PROGRAMU SQL Server, aby zmniejszyć rozmiar.

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. W związku z tym, aby upewnić się, że zmniejszenie tempdb powiedzie się, zalecamy wykonanie tej czynności, gdy serwer jest w trybie pojedynczego użytkownika lub gdy zatrzymasz wszystkie działania tempdb.

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 plikach logicznych tempdbtempdev i templog. Jeśli do tempdbzostanie dodanych więcej plików, można je zmniejszyć po ponownym uruchomieniu programu SQL Server jako usługi. Wszystkie pliki tempdb są tworzone ponownie podczas uruchamiania. Są one jednak puste i można je usunąć. Aby usunąć dodatkowe pliki w tempdb, użyj polecenia ALTER DATABASE z opcją REMOVE FILE.

Ta metoda wymaga ponownego uruchomienia programu SQL Server.

  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. W tym celu naciśnij Ctrl+C w oknie wiersza polecenia, uruchom ponownie program SQL Server jako usługę, a następnie sprawdź rozmiar plików tempdb.mdf i templog.ldf.

Użyj polecenia DBCC SHRINKDATABASE

DBCC SHRINKDATABASE odbiera parametr target_percent. Jest to żądany procent wolnego miejsca pozostawionego w pliku bazy danych po usunięciu bazy danych. Jeśli używasz DBCC SHRINKDATABASE, może być konieczne ponowne uruchomienie programu SQL Server.

  1. Określ przestrzeń, która jest obecnie używana w tempdb przy użyciu procedury składowanej sp_spaceused. Następnie oblicz procent wolnego miejsca, który jest pozostawiony do użycia jako parametr do DBCC SHRINKDATABASE. To obliczenie jest oparte na żądanym rozmiarze bazy danych.

    Notatka

    W niektórych przypadkach może być konieczne wykonanie sp_spaceused @updateusage = true, aby ponownie obliczyć używane miejsce i uzyskać 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 na 800 MB = 25% i to jest twój target_percent. Plik dziennika transakcji jest odpowiednio skurczony, pozostawiając 25 procent, czyli 200 MB wolnego miejsca po skurczeniu bazy danych.

  2. Połącz się z programem SQL Server przy użyciu programu SSMS, narzędzia Azure Data Studio lub sqlcmd, a następnie uruchom następujące polecenie Transact-SQL. Zastąp <target_percent> żądaną wartością procentową:

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

Istnieją ograniczenia dotyczące polecenia DBCC SHRINKDATABASE w tempdb. Rozmiar docelowy plików danych i dziennika nie może być mniejszy niż rozmiar określony podczas tworzenia bazy danych lub mniejszy niż ostatni rozmiar, który został jawnie ustawiony przy użyciu operacji zmiany rozmiaru pliku, takiej jak ALTER DATABASE używającej opcji MODIFY FILE. 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 pliki tempdb. 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 odbiera parametr target_size. Jest to żą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 ilość miejsca zajmowanego przez pliki jest mniejsza lub równa żądanemu rozmiarowi docelowemu.

  2. Połącz się z programem SQL Server przy użyciu programu SSMS, narzędzia Azure Data Studio lub sqlcmd, a 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żesz przeprowadzić DBCC SHRINKFILE na dowolnym z plików danych lub plików dzienników. 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żywana, a jeśli spróbujesz zmniejszyć ją przy użyciu poleceń DBCC SHRINKDATABASE lub DBCC SHRINKFILE, możesz otrzymywać komunikaty podobne do następujących, w zależności 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. Dlatego, jeśli błąd wystąpi poza operacjami zmniejszania, warto przeprowadzić dodatkowe dochodzenie.

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ść