Mengatasi masalah terkait pembersihan otomatis pelacakan perubahan

Berlaku untuk: SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

Artikel ini menyediakan cara untuk memecahkan masalah umum yang diamati dalam pembersihan otomatis pelacakan perubahan.

Gejala

Umumnya, jika pembersihan otomatis tidak berfungsi seperti yang diharapkan, Anda dapat melihat satu atau beberapa gejala berikut:

• Konsumsi penyimpanan tinggi oleh satu atau beberapa tabel tambahan untuk pelacakan perubahan atau tabel sistem syscommittab.
• Tabel samping (tabel internal yang namanya dimulai dengan awalan change_tracking, misalnya, change_tracking_12345) atau syscommittab keduanya, memperlihatkan jumlah baris signifikan yang berada di luar periode retensi yang dikonfigurasi.
• dbo.MSChange_tracking_history tabel memiliki entri dengan kesalahan pembersihan tertentu.
• CHANGETABLE penurunan performa dari waktu ke waktu.
• Pembersihan otomatis atau pembersihan manual melaporkan penggunaan CPU yang tinggi.

Pemecahan masalah dan mitigasi

Untuk mengidentifikasi penyebab utama masalah dengan pembersihan otomatis dalam pelacakan perubahan, gunakan langkah-langkah berikut untuk memecahkan masalah dan mengurangi dampaknya.

Status pembersihan otomatis

Periksa apakah pembersihan otomatis telah berjalan. Untuk memeriksa hal ini, kueri tabel riwayat pembersihan dalam database yang sama. Jika pembersihan telah berjalan, tabel memiliki entri dengan waktu mulai dan berakhir pembersihan. Jika pembersihan belum berjalan, tabel kosong atau memiliki entri kedaluarsa. Jika tabel riwayat memiliki entri dengan tag cleanup errors di kolom comments, maka pembersihan gagal karena kesalahan pembersihan tingkat tabel.

SELECT TOP 1000 * FROM dbo.MSChange_tracking_history ORDER BY start_time DESC;

Pembersihan otomatis berjalan secara berkala dengan interval default 30 menit. Jika tabel riwayat tidak ada, kemungkinan besar, pembersihan otomatis tidak pernah berjalan. Jika tidak, periksa nilai kolom start_time dan end_time. Jika entri terkini bukan yang terbaru, yaitu, jika entri-entri tersebut sudah berjam-jam atau beberapa hari, maka pembersihan otomatis mungkin tidak berfungsi. Jika demikian, gunakan langkah-langkah berikut untuk memecahkan masalah.

1. Pembersihan dimatikan

Periksa apakah pembersihan otomatis diaktifkan untuk database. Jika tidak, aktifkan dan tunggu setidaknya 30 menit sebelum melihat tabel riwayat untuk entri baru. Pantau kemajuan dalam tabel riwayat setelah itu.

SELECT * FROM sys.change_tracking_databases WHERE database_id=DB_ID('<database_name>')

Nilai bukan nol di is_auto_cleanup_on menunjukkan bahwa pembersihan otomatis diaktifkan. Nilai periode retensi mengontrol durasi metadata pelacakan perubahan yang dipertahankan dalam sistem. Nilai default untuk periode retensi pelacakan perubahan adalah 2 hari.

Untuk mengaktifkan atau menonaktifkan pelacakan perubahan, lihat Mengaktifkan dan Menonaktifkan Pelacakan Perubahan (SQL Server).

2. Pembersihan diaktifkan tetapi tidak berjalan

Jika pembersihan otomatis aktif, utas pembersihan otomatis kemungkinan berhenti karena kesalahan yang tidak terduga. Saat ini, memulai ulang thread pembersihan otomatis tidak memungkinkan. Anda harus memulai failover ke server sekunder (atau memulai ulang server tanpa adanya sekunder), dan mengonfirmasi bahwa pengaturan pembersihan otomatis diaktifkan untuk database.

Pembersihan otomatis berjalan tetapi tidak membuat kemajuan

Jika satu atau beberapa tabel sisi menunjukkan konsumsi penyimpanan yang signifikan, atau berisi sejumlah besar rekaman di luar retensi yang dikonfigurasi, ikuti langkah-langkah di bagian ini, yang menjelaskan perbaikan untuk tabel sisi tunggal. Langkah yang sama dapat diulang untuk lebih banyak tabel jika perlu.

1. Menilai pekerjaan yang tertunda dalam pembersihan otomatis

Identifikasi tabel tambahan yang memiliki banyak backlog rekaman kedaluwarsa, yang memerlukan mitigasi. Jalankan kueri berikut untuk mengidentifikasi tabel samping dengan jumlah rekaman besar yang kedaluwarsa. Ingatlah untuk mengganti nilai dalam contoh skrip seperti yang ditunjukkan.

1. Dapatkan versi penghapusan yang tidak valid:

   SELECT * FROM sys.change_tracking_tables;
   

   Nilai cleanup_version dari baris yang dikembalikan mewakili versi pembersihan yang tidak valid.

2. Jalankan kueri Transact-SQL (T-SQL) dinamis berikut, yang menghasilkan kueri untuk mendapatkan jumlah baris tabel samping yang kedaluwarsa. Ganti nilai <invalid_version> dalam kueri dengan nilai yang diperoleh di langkah sebelumnya.

   SELECT 'SELECT ''' + QUOTENAME(name) + ''', COUNT_BIG(*) FROM [sys].' + QUOTENAME(name)
       + ' WHERE sys_change_xdes_id IN (SELECT xdes_id FROM sys.syscommittab ssct WHERE ssct.commit_ts <= <invalid_version>) UNION'
   FROM sys.internal_tables
   WHERE internal_type = 209;
   

3. Salin tataan hasil dari kueri sebelumnya, dan hapus UNION kata kunci dari baris terakhir. Jika Anda menjalankan kueri T-SQL yang dihasilkan melalui koneksi admin khusus (DAC), kueri tersebut memberikan jumlah baris yang telah melewati batas waktu dari semua tabel tambahan. Bergantung pada ukuran sys.syscommittab tabel dan jumlah tabel samping, kueri ini mungkin membutuhkan waktu lama untuk diselesaikan.

   Penting

   Langkah ini diperlukan untuk bergerak maju dengan langkah-langkah mitigasi. Jika kueri sebelumnya gagal dijalankan, identifikasi jumlah baris yang kedaluwarsa untuk tabel sisi individual menggunakan kueri yang diberikan berikutnya.

Lakukan langkah-langkah mitigasi berikut untuk tabel samping, yang diurutkan berdasarkan jumlah baris kedaluwarsa dari yang terbanyak hingga terendah, hingga jumlah baris kedaluwarsa turun ke tingkat yang dapat dikelola untuk memungkinkan pembersihan otomatis berlangsung secara efektif.

Setelah Anda mengidentifikasi tabel samping dengan jumlah rekaman besar yang kedaluwarsa, kumpulkan informasi tentang latensi pernyataan penghapusan tabel samping dan tingkat penghapusan per detik selama beberapa jam terakhir. Selanjutnya, perkirakan waktu yang diperlukan untuk membersihkan tabel samping dengan mempertimbangkan jumlah baris kedaluarsa dan latensi penghapusan.

Gunakan cuplikan kode T-SQL berikut dengan mengganti templat parameter dengan nilai yang sesuai.

• Kueri laju pembersihan per detik:

  SELECT
      table_name,
      rows_cleaned_up / ISNULL(NULLIF(DATEDIFF(second, start_time, end_time), 0), 1),
      cleanup_version
  FROM dbo.MSChange_tracking_history
  WHERE table_name = '<table_name>'
  ORDER BY end_time DESC;
  

  Anda juga dapat menggunakan granularitas menit atau jam untuk fungsi tersebut DATEDIFF .

• Temukan jumlah baris usang di tabel samping. Kueri ini membantu Anda menemukan jumlah baris yang tertunda untuk dibersihkan.

  <internal_table_name> dan <cleanup_version> untuk tabel pengguna berada dalam output yang dikembalikan di bagian sebelumnya. Dengan menggunakan informasi ini, jalankan kode T-SQL berikut melalui koneksi admin khusus (DAC):

  SELECT '<internal_table_name>',
      COUNT_BIG(*)
  FROM sys.<internal_table_name>
  WHERE sys_change_xdes_id IN (
          SELECT xdes_id
          FROM sys.syscommittab ssct
          WHERE ssct.commit_ts <= <cleanup_version>
  );
  

  Kueri ini bisa memakan waktu untuk menyelesaikannya. Dalam kasus di mana waktu kueri habis, hitung baris kedaluarsa dengan menemukan perbedaan antara total baris dan baris aktif yaitu, baris yang akan dibersihkan.

• Temukan jumlah total baris dalam tabel samping dengan menjalankan kueri berikut:

  SELECT sum(row_count) FROM sys.dm_db_partition_stats
  WHERE object_id = OBJECT_ID('sys.<internal_table_name>')
  GROUP BY partition_id;
  

• Temukan jumlah baris aktif di tabel samping dengan menjalankan kueri berikut:

  SELECT '<internal_table_name>', COUNT_BIG(*) FROM sys.<internal_table_name> WHERE sys_change_xdes_id
  IN (SELECT xdes_id FROM sys.syscommittab ssct WHERE ssct.commit_ts > <cleanup_version>);
  

  Anda dapat menghitung perkiraan waktu untuk membersihkan tabel menggunakan laju pembersihan dan jumlah baris usang. Pertimbangkan rumus berikut:

  Waktu untuk membersihkan dalam hitungan menit = (jumlah baris basi) / (laju pembersihan dalam menit)

  Jika waktu untuk menyelesaikan pembersihan tabel dianggap layak, pantau kemajuan dan biarkan pembersihan otomatis melanjutkan pekerjaannya. Jika tidak, lanjutkan dengan langkah berikutnya untuk menelusuri lebih lanjut.

2. Periksa konflik kunci tabel

Tentukan apakah pembersihan tidak mengalami kemajuan karena konflik eskalasi kunci tabel, yang membuat pembersihan secara konsisten gagal mendapatkan kunci pada tabel samping untuk menghapus baris.

Untuk mengonfirmasi konflik kunci, jalankan kode T-SQL berikut. Kueri ini mengambil rekaman untuk tabel yang bermasalah untuk menentukan apakah ada beberapa entri yang menunjukkan konflik kunci. Beberapa konflik sporadis yang tersebar selama periode tidak boleh memenuhi syarat untuk langkah-langkah mitigasi yang sedang berlangsung. Konflik harus berulang.

SELECT TOP 1000 *
FROM dbo.MSChange_tracking_history
WHERE table_name = '<user_table_name>'
ORDER BY start_time DESC;

Jika tabel sejarah memiliki beberapa entri dalam kolom comments dengan nilai Cleanup error: Lock request time out period exceeded, ini adalah indikasi yang jelas bahwa beberapa upaya pembersihan gagal karena konflik penguncian atau batas waktu penguncian berurutan. Pertimbangkan solusi berikut:

• Nonaktifkan dan aktifkan pelacakan perubahan pada tabel yang bermasalah. Ini menyebabkan semua metadata pelacakan yang dipertahankan untuk tabel tersebut dihapus. Data tabel tetap utuh. Ini adalah obat tercepat.

• Jika opsi sebelumnya tidak memungkinkan, lanjutkan untuk menjalankan pembersihan manual pada tabel dengan mengaktifkan bendera pelacakan 8284 sebagai berikut:

  DBCC TRACEON (8284, -1);
  GO
  EXEC [sys].[sp_flush_CT_internal_table_on_demand] @TableToClean = '<table_name>';
  

3. Periksa penyebab lain

Kemungkinan penyebab lain dari keterlambatan pembersihan adalah lambatnya pernyataan penghapusan. Untuk menentukan apakah demikian, periksa nilai hardened_cleanup_version. Nilai ini dapat diambil melalui koneksi admin khusus (DAC) ke database yang sedang dipertimbangkan.

Temukan versi pembersihan yang diperkuat dengan menjalankan kueri berikut:

SELECT * FROM sys.sysobjvalues WHERE valclass = 7 AND objid = 1004;

Temukan versi pembersihan dengan menjalankan kueri berikut:

SELECT * FROM sys.sysobjvalues WHERE valclass = 7 AND objid = 1003;

Jika hardened_cleanup_version nilai dan cleanup_version sama, lewati bagian ini dan lanjutkan ke bagian berikutnya.

Jika kedua nilai berbeda, itu berarti satu atau beberapa tabel sisi mengalami kesalahan. Mitigasi tercepat adalah menonaktifkan & mengaktifkan pelacakan perubahan pada tabel yang bermasalah. Ini menyebabkan semua metadata pelacakan yang dipertahankan untuk tabel tersebut dihapus. Data dalam tabel tetap utuh.

Jika opsi sebelumnya tidak dimungkinkan, jalankan pembersihan manual pada tabel.

Mengatasi masalah syscommittab

Bagian ini mencakup langkah-langkah untuk memecahkan masalah dan mengurangi masalah dengan sistem tabel syscommittab, jika menggunakan banyak ruang penyimpanan, atau jika memiliki backlog besar baris usang.

Pembersihan tabel sistem syscommittab tergantung pada pembersihan tabel pendukung. Hanya setelah semua tabel samping dibersihkan, syscommittab dapat dihapus. Pastikan semua langkah dalam bagian Pembersihan otomatis berjalan tetapi tidak membuat kemajuan dilakukan.

Untuk secara eksplisit memanggil pembersihan syscommittab , gunakan prosedur tersimpan sys.sp_flush_commit_table_on_demand .

Catatan

Prosedur sys.sp_flush_commit_table_on_demand tersimpan dapat memakan waktu jika menghapus backlog baris yang besar.

Seperti yang ditunjukkan di bagian contoh dari artikel sys.sp_flush_commit_table_on_demand, prosedur tersimpan ini mengembalikan nilai dari safe_cleanup_version() dan jumlah baris yang dihapus. Jika nilai yang dikembalikan tampaknya 0, dan jika isolasi rekam jepret diaktifkan, pembersihan mungkin tidak menghapus apa pun dari syscommittab.

Jika periode retensi lebih besar dari satu hari, aman untuk menjalankan kembali sys.sp_flush_commit_table_on_demand prosedur simpan setelah mengaktifkan bendera pelacakan 8239 secara global. Menggunakan bendera pelacakan ini ketika isolasi rekam jepret nonaktif selalu aman tetapi dalam beberapa kasus, mungkin tidak diperlukan.

Pemanfaatan CPU tinggi selama pembersihan

Masalah yang dijelaskan di bagian ini mungkin terlihat pada versi SQL Server yang lebih lama. Jika ada sejumlah besar tabel yang dilacak perubahan dalam database, dan pembersihan otomatis atau pembersihan manual menyebabkan pemanfaatan CPU yang tinggi. Masalah ini juga dapat disebabkan karena tabel riwayat, yang disebutkan secara singkat di bagian sebelumnya.

Gunakan kode T-SQL berikut untuk memeriksa jumlah baris dalam tabel riwayat:

SELECT COUNT(*) from dbo.MSChange_tracking_history;

Jika jumlah baris cukup besar, coba tambahkan indeks berikut jika tidak ada. Gunakan kode T-SQL berikut untuk menambahkan indeks:

IF NOT EXISTS (
    SELECT *
    FROM sys.indexes
    WHERE name = 'IX_MSchange_tracking_history_start_time'
        AND object_id = OBJECT_ID('dbo.MSchange_tracking_history')
)
BEGIN
    CREATE NONCLUSTERED INDEX IX_MSchange_tracking_history_start_time
    ON dbo.MSchange_tracking_history (start_time)
END

Menjalankan pembersihan lebih sering dari 30 menit

Tabel-tabel tertentu dapat mengalami tingkat perubahan yang cepat, dan Anda mungkin menemukan bahwa tugas pembersihan otomatis tidak dapat membersihkan tabel tambahan dan syscommittab dalam interval 30 menit. Jika ini terjadi, Anda dapat menjalankan pekerjaan pembersihan manual dengan frekuensi yang ditingkatkan untuk memfasilitasi proses.

Untuk SQL Server dan Azure SQL Managed Instance, buat tugas latar belakang menggunakan sp_flush_CT_internal_table_on_demand dengan interval yang lebih pendek dari default 30 menit. Untuk Azure SQL Database, Azure Logic Apps dapat digunakan untuk menjadwalkan pekerjaan ini.

Kode T-SQL berikut dapat digunakan untuk membuat pekerjaan untuk membantu membersihkan tabel samping untuk pelacakan perubahan:

-- Loop to invoke manual cleanup procedure for cleaning up change tracking tables in a database
-- Fetch the tables enabled for change tracking
SELECT IDENTITY(INT, 1, 1) AS TableID,
    (SCHEMA_NAME(tbl.Schema_ID) + '.' + OBJECT_NAME(ctt.object_id)) AS TableName
INTO #CT_Tables
FROM sys.change_tracking_tables ctt
INNER JOIN sys.tables tbl
    ON tbl.object_id = ctt.object_id;

-- Set up the variables
DECLARE @start INT = 1,
    @end INT = (
        SELECT COUNT(*)
        FROM #CT_Tables
        ),
    @tablename VARCHAR(255);

WHILE (@start <= @end)
BEGIN
    -- Fetch the table to be cleaned up
    SELECT @tablename = TableName
    FROM #CT_Tables
    WHERE TableID = @start

    -- Execute the manual cleanup stored procedure
    EXEC sp_flush_CT_internal_table_on_demand @tablename

    -- Increment the counter
    SET @start = @start + 1;
END

DROP TABLE #CT_Tables;

Konten terkait

• Tentang Pelacakan Perubahan (SQL Server)
• Fungsi Pelacakan Perubahan (Transact-SQL)
• Prosedur tersimpan Pelacakan Perubahan (Transact-SQL)
• Tabel Pelacakan Perubahan (Transact-SQL)
• Menampilkan data dan informasi ruang log untuk database
• Memecahkan masalah penggunaan CPU tinggi di Server SQL