Bagikan melalui

Menyusutkan database tempdb

  • Artikel

Berlaku untuk: SQL Server Azure SQL Managed Instance

Artikel ini membahas berbagai metode yang bisa Anda gunakan untuk menyusutkan tempdb database di SQL Server.

Anda dapat menggunakan salah satu metode berikut untuk mengubah ukuran tempdb. Tiga opsi pertama dijelaskan dalam artikel ini. Jika Anda ingin menggunakan SQL Server Management Studio (SSMS), ikuti instruksi di Menyusutkan database.

Metode Perlu menghidupkan ulang? Informasi selengkapnya
ALTER DATABASE Ya Memberikan kontrol penuh pada ukuran file default tempdb (tempdev dan templog).
DBCC SHRINKDATABASE No Beroperasi pada tingkat database.
DBCC SHRINKFILE No Memungkinkan Anda menyusutkan file individual.
SQL Server Management Studio No Menyusutkan file database melalui antarmuka pengguna grafis.

Keterangan

Secara default, database dikonfigurasi tempdb untuk ditumbuhi otomatis sesuai kebutuhan. Oleh karena itu, database ini mungkin secara tak terduga tumbuh tepat waktu ke ukuran yang lebih besar dari ukuran yang diinginkan. Ukuran database yang lebih besar tempdb tidak berdampak buruk pada performa SQL Server.

Saat SQL Server dimulai, tempdb dibuat ulang dengan menggunakan salinan model database, dan tempdb diatur ulang ke ukuran terakhir yang dikonfigurasi. Ukuran yang dikonfigurasi adalah ukuran eksplisit terakhir yang diatur dengan menggunakan operasi perubahan ukuran file seperti ALTER DATABASE yang menggunakan MODIFY FILE opsi atau DBCC SHRINKFILE pernyataan atau DBCC SHRINKDATABASE . Oleh karena itu, kecuali Anda harus menggunakan nilai yang berbeda atau mendapatkan resolusi langsung ke database besar tempdb , Anda dapat menunggu mulai ulang layanan SQL Server berikutnya agar ukurannya berkurang.

Anda dapat menyusut tempdb saat tempdb aktivitas sedang berlangsung. Namun, Anda mungkin mengalami kesalahan lain seperti pemblokiran, kebuntuan, dan sebagainya, yang dapat mencegah penyusutan selesai. Oleh karena itu, untuk memastikan bahwa penyusutan tempdb berhasil, kami sarankan Anda melakukan ini saat server berada dalam mode pengguna tunggal atau ketika Anda menghentikan semua tempdb aktivitas.

SQL Server hanya mencatat informasi yang tempdb cukup dalam log transaksi untuk mengembalikan transaksi, tetapi tidak mengulangi transaksi selama pemulihan database. Fitur ini meningkatkan performa INSERT pernyataan di tempdb. Selain itu, Anda tidak perlu mencatat informasi untuk mengulangi transaksi apa pun karena tempdb dibuat ulang setiap kali Anda menghidupkan ulang SQL Server. Oleh karena itu, tidak memiliki transaksi untuk digulirkan ke depan atau untuk menggulung balik.

Untuk informasi selengkapnya tentang mengelola dan memantau tempdb, lihat Perencanaan kapasitas dan Memantau penggunaan tempdb.

Gunakan perintah ALTER DATABASE

Catatan

Perintah ini hanya beroperasi pada file tempdev logis default tempdb dan templog. Jika lebih banyak file ditambahkan ke tempdb, Anda dapat menyusutkannya setelah memulai ulang SQL Server sebagai layanan. Semua tempdb file dibuat ulang selama pengaktifan. Namun, mereka kosong dan dapat dihapus. Untuk menghapus file tambahan di tempdb, gunakan ALTER DATABASE perintah dengan REMOVE FILE opsi .

Metode ini mengharuskan Anda untuk memulai ulang SQL Server.

  1. Hentikan SQL Server.

  2. Pada prompt perintah, mulai instans dalam mode konfigurasi minimum. Untuk melakukan ini, ikuti langkah-langkah berikut:

    1. Pada prompt perintah, ubah ke folder tempat SQL Server diinstal (ganti <VersionNumber> dan <InstanceName> dalam contoh berikut):

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

    2. Jika instans adalah instans bernama SQL Server, jalankan perintah berikut (ganti <InstanceName> dalam contoh berikut):

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

    3. Jika instans adalah instans default SQL Server, jalankan perintah berikut:

      sqlservr -c -f -mSQLCMD

      Catatan

      Parameter -c dan -f menyebabkan SQL Server dimulai dalam mode konfigurasi minimum yang memiliki tempdb ukuran 1 MB untuk file data, dan 0,5 MB untuk file log. Parameter -mSQLCMD mencegah aplikasi lain selain sqlcmd mengambil alih koneksi pengguna tunggal.

  3. Sambungkan ke SQL Server dengan sqlcmd, lalu jalankan perintah Transact-SQL berikut. Ganti <target_size_in_MB> dengan ukuran yang diinginkan:

    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. Hentikan SQL Server. Untuk melakukan ini, tekan Ctrl+C di jendela Prompt Perintah, mulai ulang SQL Server sebagai layanan, lalu verifikasi ukuran tempdb.mdf file dan templog.ldf .

Gunakan perintah DBCC SHRINKDATABASE

DBCC SHRINKDATABASE menerima parameter target_percent. Ini adalah persentase ruang kosong yang diinginkan yang tersisa dalam file database setelah database diciutkan. Jika Anda menggunakan DBCC SHRINKDATABASE, Anda mungkin harus memulai ulang SQL Server.

  1. Tentukan ruang yang saat ini digunakan tempdb dengan menggunakan prosedur tersimpan sp_spaceused . Kemudian, hitung persentase ruang kosong yang dibiarkan untuk digunakan sebagai parameter ke DBCC SHRINKDATABASE. Perhitungan ini didasarkan pada ukuran database yang diinginkan.

    Catatan

    Dalam beberapa kasus, Anda mungkin harus menjalankan sp_spaceused @updateusage = true untuk menghitung ulang ruang yang digunakan dan untuk mendapatkan laporan yang diperbarui. Untuk informasi selengkapnya, lihat sp_spaceused.

    Pertimbangkan contoh berikut:

    Asumsikan bahwa tempdb memiliki dua file: file data utama (tempdb.mdf) yaitu 1.024 MB dan file log (tempdb.ldf) yaitu 360 MB. Asumsikan bahwa sp_spaceused melaporkan bahwa file data utama berisi 600 MB data. Selain itu, asumsikan bahwa Anda ingin menyusutkan file data utama menjadi 800 MB. Hitung persentase ruang kosong yang diinginkan setelah penyusutan: 800 MB - 600 MB = 200 MB. Sekarang, bagi 200 MB dengan 800 MB = 25 persen, dan itu adalah Anda target_percent. File log transaksi disusutkan sesuai dengan itu, membiarkan 25 persen atau 200 MB ruang kosong setelah database menyusut.

  2. Sambungkan ke SQL Server dengan SQL Server, Azure Data Studio, atau sqlcmd, lalu jalankan perintah Transact-SQL berikut. Ganti <target_percent> dengan persentase yang diinginkan:

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

Ada batasan dengan DBCC SHRINKDATABASE perintah pada tempdb. Ukuran target untuk data dan file log tidak boleh lebih kecil dari ukuran yang ditentukan saat database dibuat, atau lebih kecil dari ukuran terakhir yang secara eksplisit diatur dengan menggunakan operasi pengubahan ukuran file seperti ALTER DATABASE yang menggunakan MODIFY FILE opsi . Batasan DBCC SHRINKDATABASE lain adalah perhitungan target_percentage parameter dan dependensinya pada ruang saat ini yang digunakan.

Gunakan perintah DBCC SHRINKFILE

DBCC SHRINKFILE Gunakan perintah untuk menyusutkan file individualtempdb. DBCC SHRINKFILE memberikan lebih banyak fleksibilitas daripada DBCC SHRINKDATABASE karena Anda dapat menggunakannya pada satu file database tanpa memengaruhi file lain yang termasuk dalam database yang sama. DBCC SHRINKFILEtarget_size menerima parameter . Ini adalah ukuran akhir yang diinginkan untuk file database.

  1. Tentukan ukuran yang diinginkan untuk file data utama (tempdb.mdf), file log (templog.ldf), dan file tambahan yang ditambahkan ke tempdb. Pastikan bahwa ruang yang digunakan dalam file kurang dari atau sama dengan ukuran target yang diinginkan.

  2. Sambungkan ke SQL Server dengan SQL Server, Azure Data Studio, atau sqlcmd, lalu jalankan perintah Transact-SQL berikut untuk file database tertentu yang ingin Anda susutkan. Ganti <target_size_in_MB> dengan ukuran yang diinginkan:

    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

Keuntungannya DBCC SHRINKFILE adalah dapat mengurangi ukuran file ke ukuran yang lebih kecil dari ukuran aslinya. Anda dapat mengeluarkan DBCC SHRINKFILE file data atau log apa pun. Anda tidak bisa membuat database lebih kecil dari ukuran model database.

Kesalahan 8909 saat Anda menjalankan operasi penyusutan

Jika tempdb sedang digunakan, dan jika Anda mencoba menyusutkannya dengan menggunakan DBCC SHRINKDATABASE perintah atau DBCC SHRINKFILE , Anda mungkin menerima pesan yang menyerupai berikut ini, tergantung pada versi SQL Server yang Anda gunakan:

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).

Kesalahan ini tidak menunjukkan kerusakan nyata di tempdb. Namun, mungkin ada alasan lain untuk kesalahan kerusakan data fisik seperti kesalahan 8909, dan alasan tersebut termasuk masalah subsistem I/O. Oleh karena itu, jika kesalahan terjadi di luar operasi penyusutan, Anda harus melakukan lebih banyak penyelidikan.

Meskipun pesan 8909 dikembalikan ke aplikasi atau kepada pengguna yang menjalankan operasi penyusutan, operasi penyusutan tidak gagal.

Konten terkait