DBCC SHRINKFILE (Transact-SQL)

Berlaku untuk:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceDatabase SQL di Microsoft Fabric

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 mengimpikan file dan memungkinkan penghapusan databasenya. Anda dapat menyusutkan file menjadi kurang dari ukurannya saat pembuatan, mengatur ulang ukuran file minimum ke nilai baru.

Gunakan DBCC SHRINKFILE hanya jika diperlukan karena shrink adalah operasi yang berjalan lama dan membutuhkan sumber daya.

Catatan

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

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 }

Argumen

file_name

Nama logis file yang akan diperkecil.

file_id

Nomor identifikasi (ID) file yang akan diperkecil. Untuk mendapatkan ID file, gunakan fungsi sistem FILE_IDEX atau kueri tampilan katalog sys.database_files dalam database saat ini.

target_size

Bilangan bulat yang mewakili ukuran megabyte baru file. Jika Anda mengatur target_size ke 0 atau tidak menentukannya, DBCC SHRINKFILE file akan dikurangi ke ukuran pembuatan.

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 8target_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.

Jika Anda menentukan target_size dengan TRUNCATEONLY, DBCC SHRINKFILE mungkin tidak akan melepaskan ruang kosong di akhir file.

KOSONGFILE

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 untuk ALTER DATABASE menghapus file. Jika Anda menggunakan ALTER DATABASE pernyataan 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 telah 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 cara menghapus kontainer FILESTREAM, lihat bagian terkait di ALTER DATABASE Opsi File dan Filegroup

EMPTYFILEtidak didukung di Azure SQL Database, Azure SQL Database Hyperscale, atau SQL database di Microsoft Fabric.

TIDAK BERJALAN

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.

TERBATAS

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

Jika target_size ditentukan dengan TRUNCATEONLY, ruang kosong di akhir file mungkin tidak dirilis.

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

DENGAN NO_INFOMSGS

Menekan semua pesan informasi.

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 lebih lanjut, lihat Memahami masalah konkurensi dengan DBCC SHRINKFILE.

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

• Anda tidak dapat menentukan opsi.ABORT_AFTER_WAITNONE
• 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 ] }

Berlaku untuk: SQL Server (SQL Server 2022 (16.x) dan versi yang lebih baru), Azure SQL Database, database SQL di Microsoft Fabric.

• SELF

  SELF adalah opsi default. Keluar dari operasi shrink file 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 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. 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 lebih lanjut tentang cara mengubah database saat ini, lihat GUNAKAN.

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.

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.

Masalah umum

Berlaku untuk: SQL Server, Azure SQL Database, database SQL di Microsoft Fabric, Azure SQL Managed Instance, pool SQL khusus Azure Synapse Analytics

• Pada versi SQL Server sebelum SQL Server 2025 (17.x), halaman yang digunakan oleh tipe kolom objek besar (LOB) (varbinary(max), varchar(max), dan nvarchar(max)) pada segmen columnstore terkompresi tidak dapat dipindahkan oleh DBCC SHRINKDATABASE dan DBCC SHRINKFILE. Untuk informasi selengkapnya, lihat Apa yang baru dalam indeks penyimpan kolom.

Memahami masalah konkurensi dengan DBCC SHRINKFILE

Perintah shrink database dan shrink file dapat menyebabkan masalah konkurensi, terutama dengan pemeliharaan aktif seperti membangun ulang indeks, atau di lingkungan pemrosesan transaksi online (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. Ini juga berarti bahwa setiap kueri baru yang memerlukan Sch-S kunci pada halaman IAM dapat mengantri di belakang operasi penyusutan, yang semakin memperparah 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.

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 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 potong atau operasi 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, 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.

• 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

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 langkah-langkah berikut untuk memverifikasi bahwa file memiliki ruang kosong yang cukup:

• Jalankan kueri berikut:

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

• Jika Anda ingin mengecilkan file log transaksi, gunakan sys.dm_db_log_space_usage dynamic management view (DMV) untuk melihat ruang yang digunakan dalam log transaksi.

Operasi penyusutan tidak dapat mengurangi ukuran file lebih jauh jika ruang kosong tidak cukup.

Alasan umum file log transaksi tidak menyusut adalah ketiadaan cadangan log transaksi reguler. Untuk memotong catatan, cadangkan log transaksi lalu jalankan operasi DBCC SHRINKFILE lagi. Jika pemulihan point-in-time tidak diperlukan, pertimbangkan model Recovery (SQL Server) untuk menghindari pertumbuhan file log.

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:

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 atau first_snapshot_sequence_num tampilan berisi angka yang kurang dari transaksi operasi penyusutan yang terakhir selesai (109), operasi penyusutan menunggu transaksi tersebut selesai.

Untuk mengatasi masalah, lakukan salah satu langkah 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

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 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 AdventureWorks2025 database menjadi 1 MB. Untuk memungkinkan DBCC SHRINKFILE perintah mengecilkan file, file pertama-tama dipotong dengan mengatur model pemulihan database ke .SIMPLE

USE AdventureWorks2025;
GO

-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE AdventureWorks2025
    SET RECOVERY SIMPLE;
GO

-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (AdventureWorks2025_Log, 1);
GO

-- Reset the database recovery model.
ALTER DATABASE AdventureWorks2025
    SET RECOVERY FULL;
GO

C. Memotong file data

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

USE AdventureWorks2025;
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 AdventureWorks2025;
GO

-- Create a data file and assume it contains data.
ALTER DATABASE AdventureWorks2025
    ADD FILE (NAME = Test1data, FILENAME = 'C:\t1data.ndf', SIZE = 5 MB);
GO

-- Empty the data file.
DBCC SHRINKFILE (Test1data, EMPTYFILE);
GO

-- Remove the data file from the database.
ALTER DATABASE AdventureWorks2025
     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 AdventureWorks2025;
GO

SELECT file_id,
       name
FROM sys.database_files;
GO

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

Konten terkait

• Menyusutkan database
• Menyusutkan file
• DBCC SHRINKDATABASE (Transact-SQL)
• Pertimbangan untuk pengaturan autogrow dan autoshrink di SQL Server
• File database dan kelompok file
• sys.database_files (Transact-SQL)
• sys.databases (T-SQL)
• FILE_ID (T-SQL)
• ALTER DATABASE (Transact-SQL)
• Mengelola ruang file untuk database di Azure SQL Database