Mengelola ruang file untuk database di Azure SQL Database

Berlaku untuk:Azure SQL Database

• Azure SQL Database
• Instans Terkelola Azure SQL

Artikel ini menjelaskan berbagai jenis ruang penyimpanan untuk database di Azure SQL Database. Anda mungkin kadang-kadang perlu secara eksplisit mengelola ruang file yang dialokasikan. Artikel ini mencakup langkah-langkah untuk melakukannya.

Gambaran Umum

Pola beban kerja tertentu dapat menyebabkan ruang yang dialokasikan untuk file data menjadi lebih besar daripada ruang yang digunakan. Kondisi ini terjadi ketika ruang yang digunakan meningkat karena pertumbuhan data, tetapi kemudian Anda menghapus atau mengompres data. Ruang yang dialokasikan namun tidak terpakai tidak otomatis direklamasi karena reklamasi membutuhkan banyak sumber daya dan akan memperlambat pertumbuhan file di masa depan.

Anda mungkin perlu mengecilkan file data dan mengambil kembali ruang yang tidak terpakai dalam skenario berikut:

• Untuk memungkinkan pertumbuhan data pada database dalam pool elastis ketika alokasi ruang yang besar untuk beberapa database di dalam pool menyebabkan pool mendekati ukuran maksimum.
• Untuk memungkinkan pengurangan ukuran maksimum dari satu database atau pool elastis.
• Untuk mengubah database atau pool elastis ke tier dengan batas ukuran maksimum lebih rendah.
• Untuk mengurangi biaya penyimpanan saat menggunakan tingkat layanan Hyperscale.

Perhatian

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

Memantau penggunaan ruang file

API Azure Resource Manager (ARM), termasuk get-metrics PowerShell, mengembalikan ruang yang digunakan dan dialokasikan untuk database dan elastic pool.

Tampilan sistem berikut ini juga menampilkan ukuran ruang yang digunakan dan yang dialokasikan untuk basis data dan kumpulan elastis:

• sys.resource_stats
• sys.elastic_pool_resource_stats
• sys.dm_elastic_pool_resource_stats

Memahami jenis ruang penyimpanan untuk database

Memahami jumlah ruang penyimpanan berikut penting untuk mengelola ruang file database.

Jumlah database	Definisi	Komentar
Ruang data yang digunakan	Jumlah ruang yang digunakan untuk menyimpan data.	Umumnya, ruang yang digunakan meningkat (berkurang) saat memasukkan (menghapus) data. Dalam beberapa kasus, ruang yang digunakan tidak berubah saat penyisipan atau penghapusan data, bergantung pada jumlah dan pola data yang terlibat dalam operasi tersebut serta tingkat fragmentasi yang ada. Misalnya, menghapus satu baris dari setiap halaman data tidak selalu mengurangi ruang yang digunakan.
Ruang data dialokasikan	Jumlah ruang penyimpanan yang diambil oleh file data.	Jumlah ruang yang dialokasikan bertambah secara otomatis, tetapi tidak pernah berkurang secara otomatis setelah dihapus. Hal ini memastikan bahwa operasi penyisipan berikutnya lebih cepat karena ruang tidak perlu dialokasikan ulang.
Ruang data dialokasikan tetapi tidak digunakan	Perbedaan antara jumlah ruang data yang dialokasikan dan ruang data yang digunakan.	Kuantitas ini menunjukkan jumlah maksimum ruang kosong yang dapat direklamasi dengan menyusutkan file data database.
Ukuran data maksimal	Jumlah ruang maksimum yang dapat digunakan untuk menyimpan data.	Jumlah ruang data yang dialokasikan tidak dapat tumbuh melebihi ukuran maksimal data.

Diagram berikut ini mengilustrasikan hubungan antara berbagai jenis ruang penyimpanan untuk database.

Mengkueri database tunggal untuk informasi ruang file

Gunakan kueri berikut pada sys.database_files untuk mengembalikan jumlah ruang file database yang dialokasikan dan jumlah ruang yang tidak digunakan yang dialokasikan.

-- Connect to a user database
SELECT file_id,
       type_desc,
       CAST (FILEPROPERTY(name, 'SpaceUsed') AS DECIMAL (19, 4)) * 8 / 1024. AS space_used_mb,
       CAST (size / 128.0 - CAST (FILEPROPERTY(name, 'SpaceUsed') AS INT) / 128.0 AS DECIMAL (19, 4)) AS space_unused_mb,
       CAST (size AS DECIMAL (19, 4)) * 8 / 1024. AS space_allocated_mb,
       CAST (max_size AS DECIMAL (19, 4)) * 8 / 1024. AS max_size_mb
FROM sys.database_files;

Memahami jenis ruang penyimpanan untuk kumpulan elastis

Memahami jumlah ruang penyimpanan berikut penting untuk mengelola ruang file dari pool elastis.

Kuantitas kumpulan elastis	Definisi	Komentar
Ruang data yang digunakan	Penjumlahan ruang data yang digunakan oleh semua database dalam kumpulan elastis.
Ruang data dialokasikan	Jumlah total ruang penyimpanan yang digunakan oleh file data di semua basis data dalam elastic pool.
Ruang data dialokasikan tetapi tidak digunakan	Perbedaan antara jumlah ruang data yang dialokasikan dan ruang data yang digunakan oleh semua database dalam kumpulan elastis.	Kuantitas ini menunjukkan jumlah ruang maksimum yang dialokasikan untuk kumpulan elastis yang dapat direklamasi dengan menyusutkan file data database.
Ukuran data maksimal	Jumlah maksimum ruang data yang digunakan oleh pool elastis untuk semua basis datanya.	Ruang yang dialokasikan untuk kolam elastis tidak boleh melebihi ukuran maksimum kolam elastis. Jika kondisi ini terjadi, maka file data yang dialokasikan tetapi tidak digunakan dapat diambil kembali dengan mengecilkan file data.

Pesan kesalahan "Kolam elastis telah mencapai batas penyimpanan" menunjukkan bahwa objek basis data mengonsumsi cukup ruang untuk memenuhi batas ukuran maksimum kolam elastis. Pertimbangkan untuk meningkatkan batas penyimpanan, atau bebaskan ruang data seperti yang dijelaskan dalam Reclaim unused allocated space.

Mengkueri kumpulan elastis untuk informasi ruang penyimpanan

Gunakan kueri berikut untuk menentukan kuantitas ruang penyimpanan untuk pool elastis.

Ruang data kumpulan elastis yang digunakan

Gunakan contoh kueri berikut untuk mengembalikan jumlah ruang data elastic pool yang digunakan. Ubah parameter nama kolam elastis agar sesuai dengan nama kolam Anda.

-- Connect to master
SELECT TOP (1) avg_storage_percent / 100.0 * elastic_pool_storage_limit_mb AS elastic_pool_space_used_mb,
               avg_allocated_storage_percent / 100.0 * elastic_pool_storage_limit_mb AS elastic_pool_space_allocated_mb,
               elastic_pool_storage_limit_mb AS elastic_pool_maximum_size_mb
FROM sys.elastic_pool_resource_stats
WHERE elastic_pool_name = 'ep1'
ORDER BY end_time DESC;

Klaim kembali ruang yang dialokasikan dan tidak digunakan

Penting

Operasi penciutan mengonsumsi sumber daya dan dapat memengaruhi kinerja basis data selama proses berlangsung. Jika memungkinkan, jalankan proses penyusutan selama periode penggunaan rendah.

Menyusutkan file data

Karena penyusutan file data dapat memengaruhi kinerja basis data, Azure SQL Database tidak secara otomatis mengecilkan file data. Jika perlu, Anda dapat memperkecil file data pada waktu yang Anda pilih. Jangan jadikan penyusutan sebagai operasi yang dijadwalkan secara rutin. Sebaliknya, pertimbangkan untuk menggunakannya hanya setelah pengurangan besar dalam konsumsi ruang yang digunakan.

Tip

Jangan buang sumber daya komputasi dan waktu untuk mengecilkan file data jika beban kerja aplikasi reguler menyebabkan file tumbuh kembali ke ukuran yang dialokasikan sama.

Untuk mengecilkan file, gunakan perintah salah satu DBCC SHRINKDATABASE atau DBCC SHRINKFILE T-SQL:

• DBCC SHRINKDATABASE mengecilkan semua data dan file log dalam database dengan satu perintah. Perintah ini akan menyusutkan satu file data dalam satu waktu, yang dapat memakan waktu lama untuk database yang lebih besar. Perintah ini juga menyusutkan file log, yang biasanya tidak diperlukan karena Azure SQL Database menyusutkan file log secara otomatis jika diperlukan.
• DBCC SHRINKFILE perintah mendukung skenario yang lebih canggih:
  • Fungsi ini dapat memproses file individual sesuai kebutuhan, bukan mengurangi ukuran semua file dalam database.
  • Setiap perintah DBCC SHRINKFILE dapat dijalankan secara paralel dengan perintah DBCC SHRINKFILE lainnya untuk mengurangi total waktu penciutan, dengan konsekuensi penggunaan sumber daya yang lebih tinggi dan peluang yang lebih besar untuk sementara memblokir kueri pengguna serta perintah DBCC SHRINKFILE serentak.
  • Jika ekor file tidak berisi data, Anda dapat mengurangi ukuran file yang dialokasikan lebih cepat dengan menentukan argumen.TRUNCATEONLY TRUNCATEONLY tidak memerlukan pergerakan data di dalam file, tetapi juga tidak mengurangi ukuran yang dialokasikan sebanyak itu.
• Untuk informasi lebih lanjut terkait perintah penyusutan ini, lihat DBCC SHRINKDATABASE dan DBCC SHRINKFILE.

Jalankan contoh berikut saat terhubung ke database pengguna target, bukan ke master database.

Untuk menggunakan DBCC SHRINKDATABASE guna menyusutkan semua file data dan file log dalam database yang diberikan:

DBCC SHRINKDATABASE (N'database_name');

Sebuah database mungkin memiliki satu atau lebih file data, yang dibuat secara otomatis seiring pertumbuhan data. Untuk menentukan tata letak file database Anda, termasuk ukuran yang digunakan dan dialokasikan dari setiap file, kueri sys.database_files tampilan katalog dengan menggunakan skrip contoh berikut:

-- Review file properties, including the file_id and name values to use in shrink commands
SELECT file_id,
       name,
       CAST (FILEPROPERTY(name, 'SpaceUsed') AS BIGINT) * 8 / 1024. AS space_used_mb,
       CAST (size AS BIGINT) * 8 / 1024. AS space_allocated_mb,
       CAST (max_size AS BIGINT) * 8 / 1024. AS max_file_size_mb
FROM sys.database_files
WHERE type_desc IN ('ROWS', 'LOG');

Untuk mengecilkan satu file, gunakan perintah ini DBCC SHRINKFILE , misalnya:

-- Shrink database data file named 'data_0` by removing all unused at the end of the file, if any.
DBCC SHRINKFILE ('data_0', TRUNCATEONLY);

Mengurangi ukuran file log transaksi

Tidak seperti file data, Azure SQL Database secara otomatis menyusutkan file log transaksi untuk menghindari penggunaan ruang berlebihan yang dapat menyebabkan kesalahan kehabisan ruang. Dalam kebanyakan kasus, Anda tidak perlu menyusutkan file log transaksi.

Pada tingkat layanan Premium dan Business Critical, jika log transaksi menjadi besar, hal tersebut dapat secara signifikan berkontribusi pada konsumsi penyimpanan lokal menuju batas penyimpanan lokal maksimum . Jika konsumsi penyimpanan lokal mendekati batas, Anda mungkin memilih untuk memperkecil log transaksi menggunakan DBCC SHRINKFILE perintah seperti yang ditunjukkan pada contoh berikut. Ini melepaskan penyimpanan lokal segera setelah perintah selesai, tanpa menunggu operasi penyusutan otomatis berkala.

Jalankan contoh berikut saat terhubung ke database pengguna target, bukan ke master database.

-- Shrink the database log file (always file_id 2), by removing all unused space at the end of the file, if any.
DBCC SHRINKFILE (2, TRUNCATEONLY);

Penyusutan otomatis

Sebagai alternatif dari menyusutkan file data secara manual, penyusutan otomatis dapat diaktifkan untuk database. Namun, penyusutan otomatis dapat kurang efektif dalam mereklamasi ruang file daripada DBCC SHRINKDATABASE dan DBCC SHRINKFILE.

Secara default, penyusutan otomatis dinonaktifkan seperti yang direkomendasikan untuk sebagian besar database. Jika auto-shrink perlu diaktifkan, sebaiknya fitur tersebut dinonaktifkan setelah tujuan pengelolaan ruang tercapai, alih-alih membiarkannya tetap aktif secara permanen. Untuk informasi selengkapnya, lihat Pertimbangan untuk AUTO_SHRINK.

Misalnya, auto-shrink dapat membantu jika kolam elastis berisi banyak basis data yang terus-menerus mengalami pertumbuhan dan pengurangan signifikan pada ruang yang digunakan, sehingga kolam mendekati batas ukuran maksimumnya. Skenario ini tidak umum.

Opsi auto-shrink database tidak berpengaruh di database Hyperscale.

Untuk mengaktifkan penyusutan otomatis, jalankan perintah berikut saat tersambung ke database Anda (bukan master database).

-- Enable auto-shrink for the current database.
ALTER DATABASE CURRENT
    SET AUTO_SHRINK ON;

Untuk informasi selengkapnya tentang perintah ini, lihat opsi DATABASE SET.

Pemeliharaan indeks setelah penyusutan

Setelah operasi penyusutan selesai, indeks mungkin menjadi terfragmentasi. Untuk sebagian besar beban kerja di platform modern, fragmentasi indeks kemungkinan besar tidak akan memengaruhi kinerja. Untuk beban kerja yang menggunakan pemindaian indeks berukuran besar, fragmentasi dapat menurunkan throughput baca I/O. Jika penurunan performa terjadi setelah operasi penyusutan selesai, pertimbangkan pemeliharaan indeks untuk membangun ulang atau mengatur ulang indeks. Pembangunan ulang indeks memerlukan ruang kosong dalam database, sehingga dapat menyebabkan ruang yang dialokasikan bertambah dan mengimbangi efek dari operasi shrink.

Untuk informasi lebih lanjut terkait pemeliharaan indeks, lihat Mengoptimalkan pemeliharaan indeks untuk meningkatkan performa kueri dan mengurangi konsumsi sumber daya.

Menyusutkan database besar

Ketika ruang yang dialokasikan dalam basis data mencapai ratusan gigabyte atau lebih, penyusutan bisa memakan waktu lama. Operasi penyusutan dapat membentang selama jam, hari, atau minggu untuk basis data multi-terabyte. Bagian ini menjelaskan optimasi proses dan praktik terbaik yang membuat proses ini lebih efisien dan kurang berdampak pada beban kerja aplikasi.

Tip

ShrinkDriver adalah skrip PowerShell yang mengotomatisasi dan menyederhanakan proses penyusutan untuk basis data besar, 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.

Merekam garis besar penggunaan ruang

Sebelum memulai penyusutan, rekam ruang yang saat ini digunakan dan dialokasikan dalam setiap file database dengan menjalankan kueri penggunaan ruang berikut:

SELECT file_id,
       CAST (FILEPROPERTY(name, 'SpaceUsed') AS BIGINT) * 8 / 1024. AS space_used_mb,
       CAST (size AS BIGINT) * 8 / 1024. AS space_allocated_mb,
       CAST (max_size AS BIGINT) * 8 / 1024. AS max_size_mb
FROM sys.database_files
WHERE type_desc = 'ROWS';

Setelah penyusutan telah selesai, Anda dapat menjalankan lagi kueri ini dan membandingkan hasilnya dengan garis besar awal.

Pangkas berkas data untuk peningkatan cepat tetapi terbatas

Jika Anda ingin segera mengurangi ruang yang dialokasikan, pertimbangkan untuk menjalankan DBCC SHRINKFILE dengan parameter TRUNCATEONLY. Jika ada ruang yang dialokasikan tetapi tidak terpakai di akhir file, operasi akan menghapus ruang tersebut dengan cepat tanpa perpindahan data.

Namun, jangan gunakan TRUNCATEONLY jika tujuan Anda adalah memaksimalkan pengurangan ruang yang dialokasikan. Untuk mencapai tujuan tersebut, Anda perlu menjalankan proses penyusutan penuh seperti yang dijelaskan di bagian ini. Karena proses itu memangkas file pada bagian akhir, pengecilan terpisah dengan TRUNCATEONLY tidak ada manfaatnya.

Contoh perintah berikut memotong ID file 4:

DBCC SHRINKFILE (4, TRUNCATEONLY);

Setelah Anda menjalankan perintah ini untuk setiap file data, jalankan ulang kueri penggunaan ruang untuk melihat pengurangan ruang yang dialokasikan, jika ada. Anda juga dapat melihat ruang yang dialokasikan untuk database di portal Azure.

Mengevaluasi kerapatan halaman indeks

Sebagai langkah opsional namun direkomendasikan, tentukan rata-rata kepadatan halaman untuk indeks dalam basis data. Untuk jumlah data yang sama, operasi penyusutan selesai lebih cepat jika kepadatan halaman tinggi, karena operasi memindahkan lebih sedikit halaman dalam setiap file. Jika kerapatan halaman rendah untuk beberapa indeks, pertimbangkan untuk melakukan pemeliharaan pada indeks ini guna meningkatkan kepadatan halaman sebelum menyusutkan file data. Kepadatan halaman yang lebih tinggi memungkinkan proses penyusutan mencapai pengurangan yang lebih besar pada ruang penyimpanan yang dialokasikan.

Untuk menentukan kerapatan halaman untuk semua indeks dalam database, gunakan kueri berikut. Kerapatan halaman dilaporkan dalam kolom avg_page_space_used_in_percent.

SELECT OBJECT_SCHEMA_NAME(ips.object_id) AS schema_name,
       OBJECT_NAME(ips.object_id) AS object_name,
       i.name AS index_name,
       i.type_desc AS index_type,
       ips.avg_page_space_used_in_percent,
       ips.avg_fragmentation_in_percent,
       ips.page_count,
       ips.alloc_unit_type_desc,
       ips.ghost_record_count
FROM sys.dm_db_index_physical_stats(DB_ID(), DEFAULT, DEFAULT, DEFAULT, 'SAMPLED') AS ips
     INNER JOIN sys.indexes AS i
         ON ips.object_id = i.object_id
        AND ips.index_id = i.index_id
ORDER BY page_count DESC;

Jika ada indeks dengan jumlah halaman tinggi (seperti yang dilaporkan di page_count kolom) dengan kepadatan halaman di bawah 60-70%, pertimbangkan untuk membangun ulang atau mengatur ulang indeks tersebut sebelum mengecilkan file data.

Untuk database yang lebih besar, kueri untuk menentukan kepadatan halaman mungkin membutuhkan waktu lama untuk diselesaikan. Membangun kembali atau mengatur ulang indeks besar juga memerlukan waktu dan penggunaan sumber daya yang substansial. Namun, pemeliharaan indeks sebelum menyusut dapat mengurangi durasi penyusutan dan mencapai penghematan ruang yang lebih tinggi.

Jika ada beberapa indeks dengan kepadatan halaman rendah, Anda mungkin dapat membangunnya kembali secara paralel pada beberapa sesi database untuk mempercepat proses. Namun, pastikan Anda tidak mendekati batas sumber daya database dengan melakukan hal tersebut. Pastikan ada ruang lingkup sumber daya yang cukup untuk beban kerja aplikasi yang mungkin berjalan. Pantau konsumsi sumber daya (CPU, Data IO, Log IO) di portal Azure atau menggunakan tampilan sys.dm_db_resource_stats. Mulai operasi indeks tambahan hanya jika pemanfaatan sumber daya pada masing-masing dimensi ini tetap jauh lebih rendah dari 100%.

Contoh perintah pembangunan ulang indeks

Contoh perintah berikut menggunakan pernyataan ALTER INDEX untuk membangun ulang indeks dan meningkatkan kepadatan halamannya:

ALTER INDEX [index_name] ON [schema_name].[table_name]
REBUILD WITH (
    FILLFACTOR = 100, MAXDOP = 8, ONLINE = ON (
        WAIT_AT_LOW_PRIORITY (MAX_DURATION = 5 MINUTES, ABORT_AFTER_WAIT = NONE)),
    RESUMABLE = ON
);

Perintah ini memulai penyusunan ulang indeks yang dapat dilanjutkan dan bersifat online. Operasi ini memungkinkan beban kerja bersamaan terus menggunakan tabel saat rekonstruksi sedang berlangsung, dan memungkinkan Anda melanjutkan rekonstruksi jika ada gangguan karena alasan apa pun. Namun, jenis penyusunan ulang ini lebih lambat daripada menyusun ulang secara offline, yang memblokir akses ke tabel. Jika tidak ada beban kerja lain yang perlu mengakses tabel selama penyusunan ulang, atur opsi ONLINE dan RESUMABLE ke OFF serta hapus klausul WAIT_AT_LOW_PRIORITY.

Untuk informasi lebih lanjut terkait pemeliharaan indeks, lihat Mengoptimalkan pemeliharaan indeks untuk meningkatkan performa kueri dan mengurangi konsumsi sumber daya.

Atur ulang indeks sebelum penyusutan

Mengatur ulang indeks sebelum penyusutan dapat membuat operasi penyusutan jauh lebih cepat dalam dua skenario.

1. Jika basis data memenuhi semua kriteria berikut:

   • Ia memiliki banyak file data (lebih dari 10).
   • Ia memiliki banyak tabel dalam basis data (beberapa ratus atau lebih), secara kolektif menggunakan ruang yang sangat besar (ratusan gigabyte atau lebih).
   • Sejumlah besar data dihapus dari beberapa tabel.

   Untuk basis data seperti ini, mengatur ulang indeks pada tabel tempat Anda menghapus data memperpendek fase panjang dalam proses penyusutan.

2. Jika basis data berisi:

   • Tipe data objek besar (LOB) seperti varchar(max), nvarchar(max), varbinary(max), xml, atau tipe data serupa yang disimpan dalam LOB_DATA unit alokasi.
   • Baris besar disimpan dalam ROW_OVERFLOW_DATA unit alokasi.
   • Indeks penyimpanan kolom.

   Untuk membuat shrink run lebih cepat dan melepaskan lebih banyak ruang dalam skenario ini, pastikan untuk menyertakan LOB_COMPACTION klausa saat Anda mengatur ulang indeks. Pemadatan LOB sebelum penyusutan direkomendasikan untuk semua indeks yang berisi kolom LOB atau baris besar.

   Mengatur ulang atau membangun kembali indeks penyimpan kolom sebelum penyusutan juga dapat meningkatkan kecepatan dan efektivitas penyusutan.

Contoh berikut menunjukkan perintah untuk mengatur ulang indeks dan melakukan pemadatan LOB:

ALTER INDEX [index_name] ON [schema_name].[table_name]
REORGANIZE WITH(LOB_COMPACTION = ON);

Memperkecil beberapa file data secara paralel

Operasi penyusutan yang memerlukan pemindahan data adalah proses yang berlangsung lama. Jika database memiliki beberapa file data, Anda dapat mempercepat prosesnya dengan menyusutkan beberapa file data secara paralel. Buka beberapa sesi database, dan gunakan DBCC SHRINKFILE pada setiap sesi dengan nilai yang berbeda file_id . Sama dengan menyusun ulang indeks sebelumnya, pastikan Anda memiliki headroom sumber daya yang memadai (CPU, Data IO, Log IO) sebelum memulai setiap perintah penyusutan paralel baru.

Contoh perintah berikut mengecilkan ID file 4, mencoba mengurangi ukuran yang dialokasikan menjadi 52.000 MB:

DBCC SHRINKFILE (4, 52000);

Untuk mengurangi ruang yang dialokasikan untuk file ke seminimal mungkin, jalankan pernyataan tanpa menentukan ukuran target:

DBCC SHRINKFILE (4);

Jika Anda memulai terlalu banyak operasi penyusutan paralel, Anda mungkin melihat pemanfaatan sumber daya yang tinggi dan kontroversi kunci di antara operasi penyusutan. Untuk sebagian besar skenario, jumlah optimal operasi penyusutan paralel berada dalam kisaran empat hingga delapan.

Perkecil secara bertahap

Jika operasi penyusutan berhenti secara tidak terduga (misalnya, karena pemeliharaan terencana atau tidak terencana), beban kerja mungkin mulai menggunakan ruang yang dibebaskan oleh proses penyusutan sebelum proses tersebut memangkas file, sehingga sebagian progres penyusutan yang telah dicapai hingga saat itu hilang. Karena proses shrink sering berlangsung lama, kemungkinan terjadinya gangguan lebih tinggi.

Untuk menghindari masalah ini, kecilkan setiap file dalam langkah-langkah kecil dan bertahap. Dalam perintah DBCC SHRINKFILE, atur nilai target agar lebih kecil daripada ruang yang saat ini dialokasikan untuk file, tetapi lebih besar daripada ruang terpakai yang dikembalikan oleh kueri penggunaan ruang baseline.

Misalnya, jika ruang yang dialokasikan untuk ID file 4 adalah 200.000 MB, dan Anda ingin mengecilkannya menjadi 100.000 MB, Anda dapat terlebih dahulu mengatur target menjadi 180.000 MB:

DBCC SHRINKFILE (4, 180000);

Setelah perintah ini mengurangi ukuran yang dialokasikan menjadi 180.000 MB, Anda dapat menjalankan shrink lagi, mengatur target terlebih dahulu ke 160.000 MB, lalu ke 140.000 MB, dan terus mengurangi target hingga file mencapai ukuran yang diinginkan.

Mengecilkan file secara bertahap mungkin memakan waktu lebih lama, tetapi mengurangi risiko penyusutan berulang untuk seluruh file karena gangguan tak terduga.

Sebagai titik awal, gunakan kenaikan sebesar 10–20 gigabita. Anda dapat menyesuaikan kenaikan sesuai kebutuhan untuk skenario Anda. Kenaikan bertahap yang lebih besar mungkin memungkinkan Anda menyelesaikan penyusutan file lebih cepat, sedangkan kenaikan bertahap yang lebih kecil mengurangi risiko kehilangan progres jika penyusutan terganggu.

Memantau operasi pengecilan

Untuk memantau kemajuan penyusutan untuk semua sesi penyusutan yang berjalan secara bersamaan, gunakan kueri berikut:

SELECT command,
       percent_complete,
       status,
       wait_resource,
       session_id,
       wait_type,
       blocking_session_id,
       cpu_time,
       reads,
       writes,
       CAST (((DATEDIFF(s, start_time, GETDATE())) / 3600) AS VARCHAR) + ' hour(s), '
               + CAST ((DATEDIFF(s, start_time, GETDATE()) % 3600) / 60 AS VARCHAR) + 'min, '
               + CAST ((DATEDIFF(s, start_time, GETDATE()) % 60) AS VARCHAR) + ' sec'
           AS running_time
FROM sys.dm_exec_requests AS r
     LEFT OUTER JOIN sys.databases AS d
         ON r.database_id = d.database_id
WHERE r.command IN ('DbccSpaceReclaim', 'DbccFilesCompact', 'DbccLOBCompact', 'DBCC');

Catatan

Kemajuan penyusutan mungkin tidak linier, dan nilai di percent_complete kolom mungkin tetap tidak berubah dalam jangka waktu lama, meskipun penyusutan masih berlangsung. Peningkatan nilai cpu_time, reads, atau writes untuk session_id yang sama antara dua eksekusi kueri berarti bahwa shrink terus mengalami kemajuan.

Setelah shrink selesai untuk semua file data dengan sukses, jalankan ulang kueri penggunaan ruang (atau cek portal Azure) untuk melihat pengurangan ukuran penyimpanan yang dialokasikan. Jika masih ada perbedaan besar antara ruang yang digunakan dan ruang yang dialokasikan, bangun ulang atau susun ulang indeks. Pembangunan kembali indeks mungkin akan meningkatkan ruang yang dialokasikan untuk sementara. Namun, mengecilkan file data lagi setelah membangun ulang indeks sering kali mengakibatkan pengurangan ruang yang dialokasikan lebih dalam.

Kesalahan sementara selama pengurangan

Kadang-kadang perintah shrink dapat gagal dengan kesalahan seperti timeout dan deadlock. Kesalahan ini sering bersifat sementara dan tidak terjadi lagi jika Anda mengulangi perintah yang sama. Jika shrink gagal karena error, maka progres yang dicapai sejauh ini tetap dipertahankan. Jalankan perintah penyusutan yang sama lagi untuk terus menyusutkan file.

Skrip PowerShell ShrinkDriver secara otomatis mencoba kembali proses shrink saat terjadi kesalahan sementara. Gunakan skrip ini untuk mengecilkan database besar.

Contoh skrip T-SQL berikut menunjukkan cara menjalankan shrink untuk satu file dalam loop retry. Loop tersebut secara otomatis mencoba ulang operasi hingga jumlah yang dapat dikonfigurasi saat terjadi kesalahan timeout atau deadlock. Pendekatan coba lagi ini dapat diterapkan pada banyak kesalahan lain yang mungkin terjadi selama proses penyusutan.

DECLARE @RetryCount AS INT = 3; -- adjust to configure desired number of retries
DECLARE @Delay AS CHAR (12);

-- Retry loop
WHILE @RetryCount >= 0
BEGIN
    BEGIN TRY
        DBCC SHRINKFILE (1); -- adjust file_id and other shrink parameters

        -- Exit retry loop on successful execution
        SELECT @RetryCount = -1;

    END TRY
    BEGIN CATCH
        -- Retry for the declared number of times without raising
        -- an error if deadlocked or timed out waiting for a lock
        IF ERROR_NUMBER() IN (1205, 49516) AND @RetryCount > 0
        BEGIN
            SELECT @RetryCount -= 1;

            PRINT CONCAT('Retry at ', SYSUTCDATETIME());

            -- Wait for a random period of time between 1 and 10 seconds before retrying
            SELECT @Delay = '00:00:0' + CAST (CAST (1 + RAND() * 8.999 AS DECIMAL (5, 3)) AS VARCHAR (5));

            WAITFOR DELAY @Delay;

        END
        ELSE -- Raise error and exit loop
        BEGIN
            SELECT @RetryCount = -1;

            THROW;

        END
    END CATCH
END

Selain timeout dan deadlock, shrink juga dapat mengalami kesalahan karena masalah tertentu yang sudah diketahui.

Tinjau kesalahan dan langkah-langkah mitigasi di bagian berikut.

Nomor kesalahan 49503

%.*ls: Page %d:%d could not be moved because it is an off-row persistent version store page. Page holdup reason: %ls. Page holdup timestamp: %I64d.

Kesalahan ini terjadi ketika transaksi aktif yang berjalan lama menghasilkan versi baris di penyimpanan versi persisten (PVS). Shrink tidak dapat memindahkan halaman yang berisi versi baris.

Untuk mengurangi kesalahan ini, tunggu hingga transaksi yang berjalan lama selesai. Sebagai alternatif, identifikasi dan akhiri transaksi yang berjalan lama, tetapi tindakan ini dapat memengaruhi aplikasi Anda jika tidak menangani kegagalan transaksi dengan baik.

Untuk informasi lebih lanjut tentang pemecahan masalah keterlambatan pembersihan PVS yang mungkin memengaruhi operasi shrink, lihat Memantau dan memecahkan masalah pemulihan database dipercepat.

Nomor kesalahan 5223

%.*ls: Empty page %d:%d could not be deallocated.

Kesalahan ini dapat terjadi selama operasi pemeliharaan indeks yang sedang berlangsung seperti ALTER INDEX. Coba lagi perintah penyusutan setelah operasi ini selesai.

Jika kesalahan ini tetap ada, Anda mungkin harus membangun ulang indeks terkait. Untuk menemukan indeks yang akan disusun ulang, jalankan kueri berikut ini dalam database yang sama tempat Anda menjalankan perintah penyusutan:

SELECT OBJECT_SCHEMA_NAME(pg.object_id) AS schema_name,
       OBJECT_NAME(pg.object_id) AS object_name,
       i.name AS index_name,
       p.partition_number
FROM sys.dm_db_page_info(DB_ID(), <file_id>, <page_id>, default) AS pg
INNER JOIN sys.indexes AS i
ON pg.object_id = i.object_id
   AND
   pg.index_id = i.index_id
INNER JOIN sys.partitions AS p
ON pg.partition_id = p.partition_id;

Sebelum menjalankan kueri ini, ganti placeholder dan <file_id><page_id> dengan nilai sebenarnya dari pesan kesalahan. Misalnya, jika pesannya adalah: Empty page 1:62669 could not be deallocated, maka <file_id> adalah dan 1<page_id> adalah 62669.

Susun ulang indeks yang diidentifikasi oleh kueri, lalu coba lagi perintah penyusutan.

Nomor kesalahan 5201

DBCC SHRINKDATABASE: File ID %d of database ID %d was skipped because the file does not have enough free space to reclaim.

Kesalahan ini berarti file data tidak dapat diperkecil lebih jauh. Anda dapat melanjutkan ke file data berikutnya.

Konten terkait

• Batas sumber daya untuk database tunggal yang menggunakan model pembelian vCore
• Batas sumber daya untuk database tunggal menggunakan model pembelian DTU - Azure SQL Database
• Batas sumber daya untuk kumpulan elastis menggunakan model pembelian vCore
• Batas sumber daya untuk kumpulan elastis menggunakan model pembelian DTU