DBCC SHRINKFILE (Transact-SQL)

Berlaku untuk:SQL Server Azure SQL DatabaseAzure SQL Managed Instance

Menyusutkan data atau ukuran file log yang ditentukan database saat ini. Anda dapat menggunakannya untuk memindahkan data dari satu file ke file lain dalam grup file yang sama, yang menggairahkan file dan memungkinkan penghapusan databasenya. Anda dapat menyusutkan file menjadi kurang dari ukurannya saat pembuatan, mengatur ulang ukuran file minimum ke nilai baru.

Konvensi sintaks Transact-SQL

Sintaks

DBCC SHRINKFILE   
(  
    { file_name | file_id }   
    { [ , EMPTYFILE ]   
    | [ [ , target_size ] [ , { 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 }

Catatan

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

Argumen

file_name

File yang akan disusutkan nama logisnya.

file_id

File yang akan disusutkan nomor identifikasi (ID) . Untuk mendapatkan ID file, gunakan fungsi sistem FILE_IDEX atau kueri tampilan katalog sys.database_files di database saat ini.

target_size

Bilangan bulat yang mewakili ukuran megabyte baru file. Jika tidak ditentukan atau 0, DBCC SHRINKFILE mengurangi ke ukuran pembuatan file.

Anda dapat mengurangi ukuran default file kosong menggunakan DBCC SHRINKFILE <target_size>. Misalnya, jika Anda membuat file 5-MB lalu menyusutkan file menjadi 3 MB saat file masih kosong, ukuran file default diatur ke 3 MB. Ini hanya berlaku untuk file kosong yang belum pernah berisi data.

Opsi ini tidak didukung untuk kontainer grup file FILESTREAM.

Jika ditentukan, DBCC SHRINKFILE coba susutkan file ke target_size. Halaman yang digunakan di area file yang akan dibebaskan dipindahkan ke ruang kosong di area file yang disimpan. Misalnya, dengan file data 10 MB, DBCC SHRINKFILE operasi dengan 8 target_size memindahkan semua halaman yang digunakan dalam 2 MB terakhir file ke halaman yang tidak dialokasikan dalam 8 MB pertama file. DBCC SHRINKFILE tidak menyusutkan file melewati ukuran data tersimpan yang diperlukan. Misalnya, jika 7 MB dari file data 10 MB digunakan, DBCC SHRINKFILE pernyataan dengan target_size 6 menyusutkan file menjadi hanya 7 MB, bukan 6 MB.

EMPTYFILE

Memigrasikan semua data dari file yang ditentukan ke file lain dalam grup file yang sama. Dengan kata lain, EMPTYFILE memigrasikan data dari file tertentu ke file lain dalam grup file yang sama. EMPTYFILE meyakinkan Anda bahwa tidak ada data baru yang ditambahkan ke file, meskipun file ini tidak bersifat baca-saja. Anda dapat menggunakan pernyataan ALTER DATABASE untuk menghapus file. Jika Anda menggunakan pernyataan ALTER DATABASE untuk mengubah ukuran file, bendera baca-saja diatur ulang, dan data dapat ditambahkan.

Untuk kontainer grup file FILESTREAM, Anda tidak dapat menggunakan ALTER DATABASE untuk menghapus file hingga Pengumpul Sampah FILESTREAM menjalankan dan menghapus semua file kontainer grup file yang tidak perlu yang EMPTYFILE telah disalin ke kontainer lain. Untuk informasi selengkapnya, lihat sp_filestream_force_garbage_collection. Untuk informasi tentang menghapus kontainer FILESTREAM, lihat bagian terkait di AlTER DATABASE File dan Filegroup Options (Transact-SQL)

NOTRUNCATE

Memindahkan halaman yang dialokasikan dari akhir file data ke halaman yang tidak dialokasikan di depan file dengan atau tanpa menentukan target_percent. Ruang kosong di akhir file tidak dikembalikan ke sistem operasi, dan ukuran fisik file tidak berubah. Oleh karena itu, jika NOTRUNCATE ditentukan, file tampaknya tidak menyusut.

NOTRUNCATE hanya berlaku untuk file data. File log tidak terpengaruh.

Opsi ini tidak didukung untuk kontainer grup file FILESTREAM.

TRUNCATEONLY

Melepaskan semua ruang kosong di akhir file ke sistem operasi tetapi tidak melakukan pergerakan halaman apa pun di dalam file. File data hanya disusutkan hingga batas terakhir yang dialokasikan.

target_size diabaikan jika ditentukan dengan TRUNCATEONLY.

Opsi TRUNCATEONLY tidak memindahkan informasi dalam log, tetapi menghapus VLF yang tidak aktif dari akhir file log. Opsi ini tidak didukung untuk kontainer grup file FILESTREAM.

DENGAN NO_INFOMSGS

Menyembunyikan semua pesan informasi.

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 TIDAK ADA.

WAIT_AT_LOW_PRIORITY

Berlaku untuk: SQL Server (SQL Server 2022 (16.x) dan yang lebih baru) dan database Azure SQL.

Ketika perintah penyusutan dijalankan dalam mode WAIT_AT_LOW_PRIORITY, kueri baru yang memerlukan kunci stabilitas skema (Sch-S) tidak diblokir oleh operasi penyusutan yang 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 mode WAIT_AT_LOW_PRIORITY tidak dapat memperoleh kunci karena kueri yang berjalan lama, operasi penyusutan pada akhirnya akan habis setelah 1 menit secara default dan akan keluar secara diam-diam.

Jika operasi penyusutan baru dalam mode WAIT_AT_LOW_PRIORITY tidak dapat memperoleh kunci karena kueri yang berjalan lama, operasi penyusutan pada akhirnya akan habis setelah 1 menit secara default dan akan keluar secara diam-diam. 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 mode WAIT_AT_LOW_PRIORITY mengetahui bahwa tidak akan ada dampak pada aplikasi.

ABORT_AFTER_WAIT = [ SELF | PEMBLOKIR ]

Berlaku untuk: SQL Server (SQL Server 2022 (16.x) dan yang lebih baru) dan database Azure SQL.

• DIRI

  Keluar dari operasi penyusutan file yang saat ini sedang dijalankan tanpa mengambil tindakan apa pun.

• BLOCKER

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

Tataan hasil

Tabel berikut ini menjelaskan kolom tataan hasil.

Nama kolom	Deskripsi
DbId	Nomor identifikasi database file yang coba disusutkan oleh Mesin Database.
FileId	Nomor identifikasi file dari file yang coba disusutkan oleh Mesin Database.
Ukuran Saat Ini	Jumlah halaman 8-KB yang saat ini ditempati file.
Ukuran Minimum	Jumlah halaman 8-KB yang dapat ditempati file, minimal. Angka 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.

Keterangan

DBCC SHRINKFILE berlaku untuk file database saat ini. Untuk informasi selengkapnya tentang cara mengubah database saat ini, lihat USE (Transact-SQL).

Anda dapat menghentikan DBCC SHRINKFILE operasi kapan saja dan pekerjaan yang telah selesai dipertahankan. Jika Anda menggunakan EMPTYFILE parameter dan membatalkan operasi, file tidak ditandai untuk mencegah data tambahan ditambahkan.

DBCC SHRINKFILE Ketika operasi gagal, kesalahan dimunculkan.

Pengguna lain dapat bekerja dalam database selama penyusutan file; database tidak harus dalam mode pengguna tunggal. Anda tidak perlu menjalankan instans SQL Server dalam mode pengguna tunggal untuk menyusutkan database sistem.

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.

Memahami masalah konkurensi dengan DBCC SHRINKFILE

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 untuk merebut 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 yang 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 menunggu dengan prioritas rendah 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.

Menyusutkan file log

Untuk file log, Mesin Database menggunakan target_size untuk menghitung ukuran target seluruh log. Oleh karena itu, target_size adalah ruang kosong log setelah operasi penyusutan. Ukuran target seluruh log kemudian diterjemahkan ke ukuran target setiap file log. DBCC SHRINKFILE mencoba untuk menyusutkan setiap file log fisik ke ukuran targetnya segera. Namun, jika bagian dari log logis 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 dilakukan, DBCC SHRINKFILE dapat digunakan untuk membebaskan ruang yang tersisa.

Karena file log hanya dapat disusutkan ke batas file log virtual, menyusutkan file log ke ukuran yang lebih kecil dari ukuran file log virtual mungkin tidak dimungkinkan, bahkan jika tidak digunakan. Mesin Database secara dinamis memilih ukuran log file virtual saat file log dibuat atau diperluas.

Praktik terbaik

Pertimbangkan informasi berikut saat Anda berencana untuk menyusutkan file:

• Operasi penyusutan paling efektif setelah operasi yang membuat sejumlah besar ruang yang tidak digunakan, seperti tabel terpotok atau operasi hilangkan tabel.

• 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. Mengotomatiskan peristiwa yang diperlukan untuk menumbuhkan file database yang menghambat performa.

• Operasi penyusutan tidak mempertahankan status fragmentasi indeks dalam database, dan umumnya meningkatkan fragmentasi ke tingkat tertentu. Fragmentasi ini adalah alasan lain untuk tidak berulang kali menyusutkan database.

• Susutkan beberapa file dalam database yang sama secara berurutan alih-alih secara bersamaan. Ketidakcocokan pada tabel sistem dapat menyebabkan pemblokiran dan menyebabkan keterlambatan.

Pecahkan masalah

Bagian ini menjelaskan cara mendiagnosis dan memperbaiki masalah yang dapat terjadi saat menjalankan DBCC SHRINKFILE perintah.

File tidak menyusut

Jika ukuran file tidak berubah setelah operasi penyusutan tanpa kesalahan, coba yang berikut ini untuk memverifikasi bahwa file memiliki ruang kosong yang memadai:

• Jalankan kueri berikut:

SELECT name
    , size / 128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT) / 128.0 AS AvailableSpaceInMB
FROM sys.database_files;

• Jalankan perintah DBCC SQLPERF untuk mengembalikan ruang yang digunakan dalam log transaksi.

Operasi penyusutan tidak dapat mengurangi ukuran file lebih lanjut jika tidak tersedia ruang kosong yang memadai.

Biasanya itu adalah file log yang tampaknya tidak menyusut. Non-penyusutan ini biasanya merupakan hasil dari file log yang belum terpotok. Untuk memotong log, Anda dapat mengatur model pemulihan database ke SIMPLE, atau mencadangkan log lalu menjalankan DBCC SHRINKFILE operasi lagi.

Operasi penyusutan diblokir

Transaksi yang berjalan di bawah tingkat isolasi berbasis penerapan versi baris dapat memblokir operasi penyusutan. Misalnya, jika operasi penghapusan besar yang berjalan di bawah tingkat isolasi berbasis penerapan versi baris sedang berlangsung saat DBCC SHRINKDATABASE operasi dijalankan, operasi penyusutan menunggu penghapusan selesai sebelum melanjutkan. Ketika pemblokiran ini terjadi, DBCC SHRINKFILE dan DBCC SHRINKDATABASE operasi mencetak pesan informasi (5202 untuk SHRINKDATABASE dan 5203 untuk SHRINKFILE) ke log kesalahan SQL Server. Pesan ini dicatat setiap lima menit dalam satu jam pertama dan kemudian setiap jam. Misalnya, jika log kesalahan berisi pesan kesalahan berikut, kesalahan berikut akan terjadi:

DBCC SHRINKFILE for file ID 1 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.

Pesan ini berarti transaksi rekam jepret dengan tanda waktu yang lebih lama dari 109 (transaksi terakhir yang diselesaikan operasi penyusutan) memblokir operasi penyusutan. Ini juga menunjukkan transaction_sequence_numkolom , atau first_snapshot_sequence_num dalam tampilan manajemen dinamis sys.dm_tran_active_snapshot_database_transactions berisi nilai 15. transaction_sequence_num Jika kolom tampilan atau first_snapshot_sequence_num berisi angka yang kurang dari transaksi terakhir yang diselesaikan operasi penyusutan (109), operasi penyusutan 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 selesai disimpan jika operasi penyusutan berakhir.
• 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 file data ke ukuran target tertentu

Contoh berikut menyusutkan ukuran file data bernama DataFile1 dalam UserDB database pengguna menjadi 7 MB.

USE UserDB;
GO
DBCC SHRINKFILE (DataFile1, 7);
GO

B. Menyusutkan file log ke ukuran target tertentu

Contoh berikut menyusutkan file log dalam AdventureWorks2022 database menjadi 1 MB. Untuk memungkinkan DBCC SHRINKFILE perintah menyusutkan file, file pertama-tama dipotok dengan mengatur model pemulihan database ke SIMPLE.

USE AdventureWorks2022;
GO
-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE AdventureWorks2022
SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (AdventureWorks2022_Log, 1);
GO
-- Reset the database recovery model.
ALTER DATABASE AdventureWorks2022
SET RECOVERY FULL;
GO

C. Memotong file data

Contoh berikut memotong file data utama dalam AdventureWorks2022 database. Tampilan sys.database_files katalog dikueri untuk mendapatkan file_id file data.

USE AdventureWorks2022;
GO
SELECT file_id, name
FROM sys.database_files;
GO
DBCC SHRINKFILE (1, TRUNCATEONLY);

D. Mengosongkan file

Contoh berikut menunjukkan pengosongan file sehingga dapat dihapus dari database. Untuk tujuan contoh ini, file data pertama kali dibuat dan berisi data.

USE AdventureWorks2022;
GO
-- Create a data file and assume it contains data.
ALTER DATABASE AdventureWorks2022
ADD FILE (
    NAME = Test1data,
    FILENAME = 'C:\t1data.ndf',
    SIZE = 5MB
    );
GO
-- Empty the data file.
DBCC SHRINKFILE (Test1data, EMPTYFILE);
GO
-- Remove the data file from the database.
ALTER DATABASE AdventureWorks2022
REMOVE FILE Test1data;
GO

E. Menyusutkan file database dengan WAIT_AT_LOW_PRIORITY

Contoh berikut mencoba menyusutkan ukuran file data dalam database pengguna saat ini menjadi 1 MB. Tampilan sys.database_files katalog dikueri untuk mendapatkan file_id file data, dalam contoh ini, file_id 5. Jika kunci tidak dapat diperoleh dalam satu menit, operasi penyusutan akan dibatalkan.

USE AdventureWorks2022;
GO

SELECT file_id, name
FROM sys.database_files;
GO

DBCC SHRINKFILE (5, 1) WITH WAIT_AT_LOW_PRIORITY (ABORT_AFTER_WAIT = SELF);

Lihat juga

• Pertimbangan untuk pengaturan autogrow dan autoshrink di SQL Server
• File Database dan Grup File
• sys.database_files (Transact-SQL)
• sys.databases (T-SQL)
• FILE_ID (Transact-SQL)

Langkah berikutnya

• MENGUBAH DATABASE (T-SQL)
• DBCC SHRINKDATABASE (Transact-SQL)
• Menyusutkan database
• Menyusutkan file