DBCC SHRINKDATABASE (Transact-SQL)

Berlaku untuk:SQL ServerAzure SQL Database Azure SQL Managed Instance Azure Synapse AnalyticsSQL database di Microsoft Fabric

Menyusutkan ukuran data dan file log dalam database yang ditentukan.

Jangan anggap operasi penyusutan sebagai operasi perawatan rutin. File data dan log yang tumbuh karena operasi bisnis reguler berulang tidak memerlukan operasi penyusutan.

Konvensi sintaks transact-SQL

Sintaks

Sintaks untuk 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 }

Sintaks untuk Azure Synapse Analytics:

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

Argumen

{ database_name | database_id | 0 }

Nama atau ID database yang akan diperkecil. Nilai 0 menentukan database saat ini.

target_percent

Persentase ruang kosong yang harus ditinggalkan di file database setelah operasi penyusutan selesai.

Jika Anda menentukan target_percent dengan TRUNCATEONLY, operasi penyusutan mungkin tidak melepaskan ruang kosong di akhir file.

TIDAK BERJALAN

Memindahkan halaman yang ditetapkan dari akhir file ke halaman yang tidak ditetapkan di bagian depan file. Tindakan ini memampatkan data dalam file. target_percent bersifat opsional. Azure Synapse Analytics tidak mendukung opsi ini.

Ruang kosong di akhir file tidak dikembalikan ke sistem operasi, dan ukuran fisik file tidak berubah. Dengan demikian, database tampaknya tidak menyusut saat Anda menentukan NOTRUNCATE.

NOTRUNCATE Hanya berlaku untuk file data. NOTRUNCATE tidak memengaruhi file log.

TERBATAS

Melepaskan semua ruang kosong di akhir file ke sistem operasi. Tidak memindahkan halaman apa pun di dalam file. File data hanya menyusut hingga batas terakhir yang ditetapkan. Azure Synapse Analytics tidak mendukung opsi ini.

Jika Anda menentukan target_percent dengan TRUNCATEONLY, operasi penyusutan mungkin tidak melepaskan ruang kosong di akhir file.

DENGAN NO_INFOMSGS

Menekan semua pesan informasi yang memiliki tingkat keparahan dari 0 hingga 10.

WAIT_AT_LOW_PRIORITY dengan operasi penyusutan

Berlaku untuk: SQL Server 2022 (16.x) dan versi berikutnya, Azure SQL Database, Azure SQL Managed Instance, database SQL di Microsoft Fabric

Fitur tunggu dengan prioritas rendah mengurangi konténsi kunci selama operasi penyusutan. Untuk informasi selengkapnya, lihat Memahami masalah konkurensi dengan DBCC SHRINKDATABASE.

Fitur ini mirip dengan WAIT_AT_LOW_PRIORITY dengan operasi indeks online, dengan beberapa perbedaan.

  • Anda tidak dapat menentukan ABORT_AFTER_WAIT opsi.NONE
  • Anda tidak bisa mengatur opsi tersebut MAX_DURATION . Waktu penguncian prioritas rendah untuk operasi penyusutan selalu satu menit.

TUNGGU_DALAM_PRIORITAS_RENDAH

Ketika perintah shrink dijalankan dalam WAIT_AT_LOW_PRIORITY mode, query yang memerlukan kunci stabilitas skema (Sch-S) pada halaman Index Allocation Map (IAM) tidak diblokir oleh operasi shrink. Namun, operasi penyusutan dapat diblokir oleh kunci Sch-S pada halaman IAM. Shrink terus dijalankan hanya ketika dapat memperoleh schema modify lock () lock padaSch-M halaman IAM yang dibutuhkan.

Jika operasi shrink dalam WAIT_AT_LOW_PRIORITY mode tidak dapat memperoleh kunci ini karena kueri yang berjalan lama menahan kunci Sch-S , operasi shrink akan habis dengan error 49516, misalnya: 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.

{ ABORT_AFTER_WAIT = [ DIRI | PENGHALANG ] }

  • SELF

    SELF adalah opsi default. Keluar dari operasi shrink database yang sedang dijalankan tanpa mengambil tindakan lebih lanjut.

  • BLOCKERS

    Matikan semua transaksi pengguna yang memblokir operasi penyusutan file sehingga operasi dapat dilanjutkan. Opsi ini BLOCKERS mengharuskan login memiliki izin or ALTER ANY CONNECTIONKILL DATABASE CONNECTION .

Tataan hasil

Tabel berikut ini menjelaskan kolom dalam tataan hasil.

Nama kolom Deskripsi
DbId Nomor identifikasi database file yang coba menyusutkan Mesin Database.
FileId Nomor identifikasi file file yang coba menyusutkan Mesin Database.
CurrentSize Jumlah halaman 8-KB yang saat ini diduduki file.
MinimumSize Jumlah halaman 8-KB yang dapat diduduki file, minimal. Nilai ini sesuai dengan ukuran minimum atau ukuran file yang awalnya dibuat.
UsedPages Jumlah halaman 8-KB yang saat ini digunakan oleh file.
EstimatedPages Jumlah halaman 8-KB yang diperkirakan Mesin Database file dapat disusutkan.

Catatan

Database Engine tidak menampilkan baris untuk file yang tidak diperkecil.

Keterangan

Untuk menyusutkan semua file data dan log untuk database tertentu, jalankan DBCC SHRINKDATABASE perintah . Untuk menyusutkan satu data atau file log pada satu waktu untuk database tertentu, jalankan perintah DBCC SHRINKFILE .

Untuk melihat jumlah ruang bebas saat ini (tidak dialokasikan) dalam database, jalankan sp_spaceused.

DBCC SHRINKDATABASE operasi dapat dihentikan kapan saja dalam proses, dan pekerjaan yang selesai disimpan.

Database tidak boleh lebih kecil dari ukuran minimum database yang dikonfigurasi. Anda menentukan ukuran minimum saat database awalnya dibuat. Atau, ukuran minimum dapat menjadi ukuran terakhir yang secara eksplisit diatur dengan menggunakan operasi perubahan ukuran file. Operasi seperti DBCC SHRINKFILE atau ALTER DATABASE adalah contoh operasi perubahan ukuran file.

Pertimbangkan database awalnya dibuat dengan ukuran 10 MB. Kemudian, tumbuh menjadi 100 MB. Database terkecil dapat dikurangi menjadi 10 MB, bahkan jika semua data dalam database telah dihapus.

Anda dapat menentukan NOTRUNCATE opsi atau opsi saat TRUNCATEONLY menjalankan DBCC SHRINKDATABASE. Jika Anda tidak menentukan salah satu opsi, hasilnya sama seperti jika Anda menjalankan DBCC SHRINKDATABASE operasi dengan diikuti NOTRUNCATE dengan menjalankan DBCC SHRINKDATABASE operasi dengan TRUNCATEONLY.

Database penyusutan tidak harus dalam mode pengguna tunggal. Pengguna lain dapat bekerja dalam database saat disusutkan, termasuk database sistem.

Anda tidak dapat menyusutkan database saat database sedang dicadangkan. Sebaliknya, Anda tidak dapat mencadangkan database saat operasi penyusutan pada database sedang dalam proses.

Di Azure Synapse SQL pool, hindari menjalankan perintah shrink karena ini adalah operasi intensif I/O yang dapat membuat pool SQL khusus Anda (sebelumnya SQL DW) offline. Perintah ini juga memengaruhi biaya snapshot data warehouse Anda.

Masalah umum

Berlaku untuk: SQL Server, Azure SQL Database, Azure SQL Managed Instance, Azure Synapse Analytics pool SQL khusus

  • Dalam SQL Server 2022 (16.x) dan versi sebelumnya, halaman yang digunakan oleh tipe kolom LOB (varbinary(max), varchar(max), dan nvarchar(max)) dalam segmen columnstore terkompresi tidak dapat dipindahkan oleh DBCC SHRINKDATABASE dan DBCC SHRINKFILE. Untuk informasi selengkapnya, lihat Apa yang baru dalam indeks penyimpan kolom.

Cara kerja DBCC SHRINKDATABASE

DBCC SHRINKDATABASE menyusutkan file data per file, tetapi menyusutkan file log seolah-olah semua file log ada dalam satu kumpulan log yang bersebelahan. File selalu disusutkan dari akhir.

Misalkan Anda memiliki dua file log dan satu file data dalam sebuah database bernama mydb. Data dan file log masing-masing 10 MB dan file data berisi 6 MB data. Mesin Database menghitung ukuran target untuk setiap file. Nilai ini adalah ukuran target untuk file setelah penyusutan. Saat Anda menentukan DBCC SHRINKDATABASE dengan target_percent, Database Engine menghitung ukuran target sebagai jumlah ruang target_percent yang kosong di file setelah penyusutan.

Misalnya, jika Anda menentukan target_percent 25 untuk penyusutan mydb, Mesin Database menghitung ukuran target untuk file data menjadi 8 MB (6 MB data ditambah 2 MB ruang kosong). Dengan demikian, Mesin Database memindahkan data apa pun dari 2 MB terakhir file data ke ruang kosong apa pun dalam 8 MB pertama file data lalu menyusutkan file.

Asumsikan file mydb data berisi 7 MB data. Menentukan target_percent 30 memungkinkan file data ini disusutkan ke persentase gratis 30. Namun, menentukan target_percent 40 tidak menyusutkan file data karena tidak cukup ruang kosong yang dapat dibuat dalam ukuran total file data saat ini.

Anda dapat memikirkan masalah ini dengan cara lain: 40 persen menginginkan ruang kosong + 70 persen file data lengkap (7 MB dari 10 MB) lebih dari 100 persen. Setiap target_percent yang lebih besar dari 30 tidak akan menyusutkan file data. Ini tidak akan menyusut karena persentase gratis yang Anda inginkan ditambah persentase saat ini yang diduduki file data lebih dari 100 persen.

Untuk file log, Mesin Database menggunakan target_percent untuk menghitung ukuran target untuk seluruh log. Itulah sebabnya target_percent adalah jumlah ruang kosong dalam log setelah operasi penyusutan. Ukuran target untuk seluruh log kemudian diterjemahkan ke ukuran target untuk setiap file log.

DBCC SHRINKDATABASE mencoba menyusutkan setiap file log fisik ke ukuran targetnya segera. Jika tidak ada bagian log logis yang tetap berada di log virtual melebihi ukuran target file log, DBCC SHRINKDATABASE file berhasil dipotong dan selesai tanpa pesan apapun. Namun, jika bagian dari log logis tetap berada di log virtual di luar ukuran target, Mesin Database membebaskan ruang sebanyak mungkin, lalu mengeluarkan pesan informasi. Pesan tersebut menjelaskan tindakan untuk memindahkan log logis keluar dari log virtual di akhir file. Setelah aksi dijalankan, gunakan DBCC SHRINKDATABASE untuk membebaskan sisa ruang.

Anda hanya dapat mengecilkan file log menjadi batas file log virtual. Itulah sebabnya mengecilkan file log menjadi ukuran yang lebih kecil dari ukuran file log virtual tidak memungkinkan. Database Engine secara dinamis memilih ukuran file log virtual saat membuat atau memperluas file log.

Memahami masalah konkurensi dengan DBCC SHRINKDATABASE

Perintah shrink database dan shrink file dapat menyebabkan masalah konkurensi, terutama dengan pemeliharaan aktif seperti membangun ulang indeks, atau di lingkungan OLTP yang sibuk.

Misalnya, kueri pengguna mungkin memperoleh kunci stabilitas skema (Sch-S) pada halaman Index Allocation Map (IAM) dan menahannya hingga selesai. Saat mencoba merebut kembali ruang selama penggunaan rutin, operasi shrink database dan shrink file memerlukan schema modification (Sch-M) lock saat memindahkan atau menghapus halaman IAM, sehingga memblokir kunci yang Sch-S dibutuhkan oleh kueri pengguna. Akibatnya, kueri yang berjalan lama dapat memblokir operasi penyusutan. Perilaku ini juga berarti bahwa setiap kueri baru yang memerlukan Sch-S kunci pada halaman IAM dapat mengantri di belakang operasi penyusutan, yang semakin memperburuk masalah konkurensi ini.

Diperkenalkan di SQL Server 2022 (16.x), fitur tunggu dengan prioritas rendah untuk operasi penyusutan mengatasi masalah ini dengan mengambil kunci modifikasi skema pada halaman IAM dalam mode tersebutWAIT_AT_LOW_PRIORITY. Untuk informasi selengkapnya, lihat WAIT_AT_LOW_PRIORITY dengan operasi penyusutan.

Untuk informasi lebih lanjut tentang Sch-S dan Sch-M penguncian, lihat Panduan Penguncian transaksi dan versi baris.

Praktik terbaik

Pertimbangkan informasi berikut saat Anda berencana menyusutkan database:

  • Keefektifan operasi penyusutan dapat dicapai secara maksimal setelah operasi yang membuat ruang yang tidak digunakan dijalankan, seperti truncate table atau drop table.

  • Sebagian besar basis data membutuhkan ruang kosong untuk operasi sehari-hari secara rutin. Jika Anda mengecilkan file database berulang kali dan melihat ukuran database bertambah lagi, pertumbuhan ini menunjukkan bahwa operasi reguler membutuhkan ruang kosong. Dalam kasus ini, mengecilkan file database berulang kali justru kontraproduktif. Pertumbuhan file yang diperlukan untuk mengalokasikan ruang baru setelah penyusutan dapat menghambat kinerja.

  • Operasi penyusutan tidak mempertahankan status fragmentasi indeks di database, dan dapat meningkatkan fragmentasi indeks, yang mungkin mengurangi throughput bacaan I/O untuk kueri yang menggunakan pemindaian besar.

  • Kecuali Anda memiliki persyaratan tertentu, jangan atur AUTO_SHRINK opsi database ke ON.

  • Jika Anda perlu mengecilkan file data dari database besar, pertimbangkan menggunakan skrip ShrinkDriver PowerShell. Skrip ini mengotomatisasi dan menyederhanakan proses penyusutan, mengubahnya menjadi satu operasi yang dapat diamati dan dapat dilanjutkan. Skrip mengecilkan beberapa file secara paralel, mencoba ulang saat terganggu, dan mengeluarkan laporan status rinci saat berjalan.

Pecahkan masalah

Transaksi yang berjalan di bawah tingkat isolasi berbasis penerapan versi baris dapat memblokir operasi penyusutan. Misalnya, Anda menjalankan DBCC SHRINKDATABASE saat operasi penghapusan besar yang berjalan di bawah tingkat isolasi berbasis versi baris sedang berlangsung. Dalam kasus ini, operasi penyusutan menunggu hingga operasi penghapusan selesai sebelum mengecilkan file. Ketika operasi penyusutan menunggu, DBCC SHRINKFILE dan DBCC SHRINKDATABASE operasi mencetak pesan informasi (5202 untuk SHRINKDATABASE dan 5203 untuk SHRINKFILE). Pesan ini akan dicetak ke log kesalahan SQL Server setiap lima menit pada jam pertama dan kemudian setiap jam setelahnya. Misalnya, jika log kesalahan berisi pesan kesalahan berikut:

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.

Kesalahan ini berarti transaksi snapshot dengan timestamp lebih lama dari 109 menghambat operasi penyusutan. Transaksi tersebut adalah transaksi terakhir yang diselesaikan operasi penyusutan. Ini juga menunjukkan transaction_sequence_num kolom atau first_snapshot_sequence_num dalam tampilan manajemen dinamis sys.dm_tran_active_snapshot_database_transactions berisi nilai 15. Kolom transaction_sequence_num atau first_snapshot_sequence_num dalam tampilan mungkin berisi angka yang kurang dari transaksi terakhir yang diselesaikan oleh operasi penyusutan (109). Jika demikian, operasi penyusutan menunggu transaksi tersebut selesai.

Untuk mengatasi masalah ini, Anda dapat melakukan salah satu hal berikut:

  • Akhiri transaksi yang memblokir operasi penyusutan.
  • Akhiri operasi penyusutan. Setiap pekerjaan yang telah selesai disimpan.
  • Jangan lakukan apa pun dan biarkan operasi penyusutan menunggu hingga transaksi pemblokiran selesai.

Izin

Memerlukan keanggotaan dalam peran server tetap sysadmin atau peran database tetap db_owner .

Contoh

Sampel kode dalam artikel ini menggunakan database sampel AdventureWorks2025 atau AdventureWorksDW2025, yang dapat Anda unduh dari halaman beranda Sampel dan Proyek Komunitas Microsoft SQL Server.

J. Menyusutkan database dan menentukan persentase ruang kosong

Contoh berikut mengurangi ukuran data dan file log dalam UserDB database pengguna untuk memungkinkan ruang kosong 10 persen dalam database.

DBCC SHRINKDATABASE (UserDB, 10);
GO

B. Memotong database

Contoh berikut menyusutkan data dan file log dalam AdventureWorks2025 database sampel hingga batas terakhir yang ditetapkan.

DBCC SHRINKDATABASE (AdventureWorks2025, TRUNCATEONLY);

C. Menyusutkan database Azure Synapse Analytics

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

D. Memperkecil database dengan WAIT_AT_LOW_PRIORITY

Contoh berikut mencoba mengurangi ukuran data dan file log dalam AdventureWorks2025 database untuk memungkinkan ruang kosong 20% dalam database. Jika kunci tidak dapat diperoleh dalam satu menit, operasi pengecilan dibatalkan.

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

Konten terkait

  • Last updated on