DBCC SHRINKDATABASE (Transact-SQL)

  • Artikel

Berlaku untuk: SQL Server Azure SQL DatabaseAzure SQL Managed InstanceAzure Synapse Analytics

Menyusutkan ukuran data dan file log dalam database yang ditentukan.

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 ]

Catatan

Untuk melihat sintaks Transact-SQL untuk SQL Server 2014 dan yang lebih lama, lihat Dokumentasi versi sebelumnya.

Argumen

| database_name database_id | 0

Nama database atau ID yang akan disusutkan. 0 menentukan bahwa database saat ini digunakan.

target_percent

Persentase ruang kosong yang ingin Anda tinggalkan dalam file database setelah database diciutkan.

NOTRUNCATE

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.

TRUNCATEONLY

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. Mengabaikan target_percent jika ditentukan dengan TRUNCATEONLY. Azure Synapse Analytics tidak mendukung opsi ini.

DBCC SHRINKDATABASETRUNCATEONLY dengan opsi hanya memengaruhi file log transaksi database. Untuk memotong file data, gunakan DBCC SHRINKFILE sebagai gantinya. Untuk informasi selengkapnya, lihat DBCC SHRINKFILE.

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 yang lebih baru, Azure SQL Database, Azure SQL Managed Instance

Fitur tunggu dengan prioritas rendah mengurangi ketidakcocokan kunci. 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 opsi ABORT_AFTER_WAIT NONE.

WAIT_AT_LOW_PRIORITY

Ketika perintah penyusutan dijalankan dalam WAIT_AT_LOW_PRIORITY mode, kueri baru yang memerlukan kunci stabilitas skema (Sch-S) tidak diblokir oleh operasi penyusutan menunggu sampai operasi penyusutan berhenti menunggu dan mulai dijalankan. Operasi penyusutan akan dijalankan ketika dapat memperoleh kunci penguncian modifikasi skema (Sch-M). Jika operasi penyusutan baru dalam WAIT_AT_LOW_PRIORITY mode tidak dapat memperoleh kunci karena kueri yang berjalan lama, operasi penyusutan pada akhirnya akan habis setelah 1 menit secara default dan akan keluar tanpa kesalahan.

Jika operasi penyusutan baru dalam WAIT_AT_LOW_PRIORITY mode tidak dapat memperoleh kunci karena kueri yang berjalan lama, operasi penyusutan pada akhirnya akan habis setelah 1 menit secara default dan akan keluar tanpa kesalahan. Ini akan terjadi jika operasi penyusutan tidak dapat memperoleh kunci Sch-M karena kueri atau kueri bersamaan yang memegang kunci Sch-S. Ketika waktu habis terjadi, pesan kesalahan 49516 akan dikirim ke log kesalahan SQL Server, 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. Pada titik ini, Anda cukup mencoba kembali operasi penyusutan dalam WAIT_AT_LOW_PRIORITY mode mengetahui bahwa tidak akan ada dampak pada aplikasi.

ABORT_AFTER_WAIT = [ SELF | PEMBLOKIR ]

  • DIRI

    SELF adalah opsi default. Keluar dari operasi menyusutkan database yang saat ini sedang dijalankan tanpa mengambil tindakan apa pun.

  • BLOCKER

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

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 dari file yang coba disusutkan oleh Mesin Database.
Ukuran Saat Ini Jumlah halaman 8-KB yang saat ini diduduki file.
Ukuran Minimum 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

Mesin Database tidak menampilkan baris untuk file-file tersebut yang tidak menyusut.

Keterangan

Catatan

Dalam Azure Synapse, menjalankan perintah penyusutan tidak disarankan karena ini adalah operasi intensif I/O dan dapat membuat kumpulan SQL khusus Anda (sebelumnya SQL DW) offline. Selain itu, akan ada implikasi biaya pada rekam jepret gudang data Anda setelah menjalankan perintah ini.

Untuk menyusutkan semua data dan file 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 pengubahan 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.

NOTRUNCATE Tentukan opsi atau TRUNCATEONLY opsi saat Anda menjalankan DBCC SHRINKDATABASE. Jika tidak, hasilnya sama seperti jika Anda menjalankan DBCC SHRINKDATABASE operasi dengan diikuti dengan NOTRUNCATE 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.

Ketika ditentukan dengan WAIT_AT_LOW_PRIORITY, permintaan kunci Sch-M operasi penyusutan akan menunggu dengan prioritas rendah saat menjalankan perintah selama 1 menit. Jika operasi diblokir selama durasi, tindakan ABORT_AFTER_WAIT yang ditentukan akan dijalankan.

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 menyusut dari akhir.

Asumsikan Anda memiliki beberapa file log, file data, dan database bernama mydb. File data dan log masing-masing 10 MB dan file data berisi 6 MB data. Mesin Database menghitung ukuran target untuk setiap file. Nilai ini adalah ukuran file yang akan disusutkan. Ketika DBCC SHRINKDATABASE ditentukan dengan target_percent, Mesin Database menghitung ukuran target menjadi jumlah ruang target_percent kosong dalam file setelah menyusut.

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 dan kemudian 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 untuk menyusutkan setiap file log fisik ke ukuran targetnya segera. Katakanlah tidak ada bagian dari log logis yang tetap berada di log virtual di luar ukuran target file log. Kemudian file berhasil dipotong dan DBCC SHRINKDATABASE selesai tanpa pesan apa pun. 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 menjelaskan tindakan apa yang diperlukan untuk memindahkan log logis keluar dari log virtual di akhir file. Setelah tindakan dijalankan, DBCC SHRINKDATABASE dapat digunakan untuk membebaskan ruang yang tersisa.

File log hanya dapat disusutkan ke batas file log virtual. Itulah sebabnya menyusutkan file log ke ukuran yang lebih kecil dari ukuran file log virtual mungkin tidak dimungkinkan. Mungkin tidak mungkin meskipun tidak digunakan. Ukuran file log virtual dipilih secara dinamis oleh Mesin Database saat file log dibuat atau diperluas.

Memahami masalah konkurensi dengan DBCC SHRINKDATABASE

Perintah susutkan database dan file penyusutan dapat menyebabkan masalah konkurensi, terutama dengan pemeliharaan aktif seperti membangun kembali indeks, atau pada lingkungan OLTP yang sibuk. Saat aplikasi Anda menjalankan kueri terhadap tabel database, kueri ini akan memperoleh dan mempertahankan kunci stabilitas skema (Sch-S) hingga kueri menyelesaikan operasinya. Saat mencoba mengklaim kembali ruang selama penggunaan reguler, menyusutkan database dan menyusutkan operasi file saat ini memerlukan kunci modifikasi skema (Sch-M) saat memindahkan atau menghapus halaman Peta Alokasi Indeks (IAM), memblokir kunci Sch-S yang diperlukan oleh kueri pengguna. Akibatnya, kueri yang berjalan lama akan memblokir operasi penyusutan hingga kueri selesai. Ini berarti bahwa setiap kueri baru yang memerlukan kunci Sch-S juga diantrekan di belakang operasi penyusutan menunggu dan juga akan diblokir, semakin memperburuk masalah konkurensi ini. Ini dapat berdampak signifikan pada performa kueri aplikasi dan juga akan menyebabkan kesulitan menyelesaikan pemeliharaan yang diperlukan untuk menyusutkan file database. Diperkenalkan pada SQL Server 2022 (16.x), fitur susutkan tunggu dengan prioritas rendah (WLP) mengatasi masalah ini dengan mengambil kunci modifikasi skema dalam WAIT_AT_LOW_PRIORITY mode. Untuk informasi selengkapnya, lihat WAIT_AT_LOW_PRIORITY dengan operasi penyusutan.

Untuk informasi selengkapnya tentang kunci Sch-S dan Sch-M, lihat Panduan penguncian transaksi dan penerapan versi baris.

Praktik terbaik

Pertimbangkan informasi berikut saat Anda berencana untuk 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 database memerlukan ketersediaan ruang kosong untuk operasi rutin sehari-hari. Jika Anda menyusutkan file database berulang kali dan melihat bahwa ukuran database tumbuh lagi, ini menunjukkan bahwa ruang kosong diperlukan untuk operasi reguler. Dalam kasus ini, berulang kali menyusutkan file database adalah operasi yang terbuang sia-sia. Peristiwa pertumbuhan otomatis yang diperlukan untuk meningkatkan performa penghambat file database.
  • Operasi penyusutan tidak mempertahankan status fragmentasi indeks dalam database, dan umumnya meningkatkan fragmentasi ke tingkat tertentu. Hasil ini adalah alasan lain untuk tidak berulang kali menyusutkan database.
  • Kecuali Anda memiliki persyaratan tertentu, jangan atur AUTO_SHRINK opsi database ke AKTIF.

Pecahkan masalah

Dimungkinkan untuk memblokir operasi penyusutan oleh transaksi yang berjalan di bawah tingkat isolasi berbasis penerapan versi baris. Misalnya, operasi penghapusan besar yang berjalan di bawah tingkat isolasi berbasis penerapan versi baris sedang berlangsung saat DBCC SHRINKDATABASE operasi dijalankan. Ketika situasi ini terjadi, operasi penyusutan akan menunggu operasi penghapusan selesai sebelum menyusutkan file. Ketika operasi penyusutan menunggu, DBCC SHRINKFILE dan DBCC SHRINKDATABASE operasi mencetak pesan informasi (5202 untuk SHRINKDATABASE dan 5203 untuk SHRINKFILE). Pesan ini dicetak ke log kesalahan SQL Server setiap lima menit dalam satu jam pertama dan kemudian setiap jam mendatang. 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 rekam jepret yang memiliki tanda waktu yang lebih lama dari 109 akan memblokir 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 (Transact-SQL) 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 akan menunggu transaksi tersebut selesai.

Untuk mengatasi masalah ini, Anda bisa melakukan salah satu tugas 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

A. 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 AdventureWorks2022 database sampel hingga batas terakhir yang ditetapkan.

DBCC SHRINKDATABASE (AdventureWorks2022, TRUNCATEONLY);

C. Menyusutkan database Azure Synapse Analytics

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

D. Menyusutkan database dengan WAIT_AT_LOW_PRIORITY

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

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

Lihat juga

Langkah berikutnya