Değişiklik izleme otomatik temizleme sorunlarını giderme

Şunlar için geçerlidir:SQL ServerAzure SQL VeritabanıAzure SQL Yönetilen Örneği

Bu makale, değişiklik izleme otomatik temizlemede gözlenen yaygın sorunları gidermenin yollarını sağlar.

Belirti -leri

Genellikle, otomatik temizleme beklendiği gibi çalışmıyorsa aşağıdaki belirtilerden birini veya birkaçını görebilirsiniz:

• Bir veya daha fazla değişiklik izleme yan tablosu ya da syscommittab sistem tablosunun aşırı depolama tüketimi.
• Yan tablolar (adı change_trackingön ekiyle başlayan iç tablolar, örneğin, change_tracking_12345) veya syscommittab ya da her ikisi, yapılandırılan bekletme süresinin dışında kalan önemli sayıda satır gösterir.
• dbo.MSChange_tracking_history tablosunda belirli temizleme hataları olan kayıtlar vardır.
• CHANGETABLE performansı zaman içinde düştü.
• Otomatik temizleme veya el ile temizleme yüksek CPU kullanımını bildirir.

Hata ayıklama ve azaltma

Değişiklik izleme otomatik temizleme ile ilgili bir sorunun kök nedenini belirlemek için, sorunun hatalarını ayıklamak ve azaltmak için aşağıdaki adımları kullanın.

Otomatik temizleme durumu

Otomatik temizlemenin çalışıp çalışmadığını denetleyin. Bunu denetlemek için aynı veritabanındaki temizleme geçmişi tablosunu sorgula. Temizleme işlemi çalışıyorsa, tabloda temizlemenin başlangıç ve bitiş saatlerini içeren girdiler vardır. Temizleme çalışmamışsa, tablo boş veya eski kayıtlar içeriyor olabilir. Geçmiş tablosunun cleanup errorssütununda etiketi comments girdileri varsa, tablo düzeyinde temizleme hataları nedeniyle temizleme başarısız oluyor demektir.

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

Otomatik temizleme, varsayılan aralık olarak 30 dakika olacak şekilde düzenli aralıklarla çalışır. Geçmiş tablosu yoksa, büyük olasılıkla otomatik temizleme hiçbir zaman çalışmamıştır. Aksi takdirde, start_time ve end_time sütun değerlerini denetleyin. En son girdiler güncel değilse, yani saat veya gün öncesine aitse otomatik temizleme çalışmıyor olabilir. Böyle bir durum söz konusuysa, sorun gidermek için aşağıdaki adımları kullanın.

1. Temizleme kapalı

Veritabanı için otomatik temizlemenin açık olup olmadığını denetleyin. Değilse açın ve yeni girdiler için geçmiş tablosuna bakmadan önce en az 30 dakika bekleyin. Bundan sonra geçmiş tablosundaki ilerleme durumunu izleyin.

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

is_auto_cleanup_on'da sıfır olmayan bir değer, otomatik temizlemenin etkinleştirildiğini gösterir. Bekletme süresi değeri, değişiklik izleme meta verilerinin sistemde tutuldığı süreyi denetler. Değişiklik izleme saklama süresi için varsayılan değer 2 gündür.

Değişiklik izlemeyi etkinleştirmek veya devre dışı bırakmak için bkz. Değişiklik İzlemeyi Etkinleştirme ve Devre Dışı Bırakma (SQL Server).

2. Temizleme açık ancak çalışmıyor

Otomatik temizleme açıksa, beklenmeyen hatalar nedeniyle otomatik temizleme iş parçacığı büyük olasılıkla durduruldu. Şu anda otomatik temizleme iş parçacığını yeniden başlatmak mümkün değildir. İkincil sunucuya yük devretme başlatmanız (veya ikincil sunucu olmadığında sunucuyu yeniden başlatmanız) ve veritabanı için otomatik temizleme ayarının etkinleştirildiğini onaylamanız gerekir.

Otomatik temizleme çalışıyor ancak ilerleme kaydedemiyor

Bir veya daha fazla yan tablo önemli depolama tüketimi gösteriyorsa veya yapılandırılmış saklamanın ötesinde çok sayıda kayıt içeriyorsa, tek bir yan tablo için çözümleri açıklayan bu bölümdeki adımları izleyin. Gerekirse daha fazla tablo için aynı adımlar yinelenebilir.

1. Otomatik temizleme birikimini değerlendirme

Süresi dolmuş kayıtların büyük bir kapsamına sahip olan ve bunlar üzerinde azaltma yapılması gereken yan tabloları belirleyin. Süresi dolmuş büyük kayıt sayılarına sahip yan tabloları belirlemek için aşağıdaki sorguları çalıştırın. Örnek betiklerdeki değerleri gösterildiği gibi değiştirmeyi unutmayın.

1. Geçersiz temizleme sürümünü alın:

   SELECT * FROM sys.change_tracking_tables;
   

   Döndürülen satırlardan cleanup_version değeri geçersiz temizleme sürümünü temsil eder.

2. Yan tabloların süresi dolan satır sayısını almak için sorguyu oluşturan aşağıdaki dinamik Transact-SQL (T-SQL) sorgusunu çalıştırın. Sorgudaki <invalid_version> değerini önceki adımda elde edilen değerle değiştirin.

   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. Önceki sorgudan sonuç kümesini kopyalayın ve son satırdan UNION anahtar sözcüğünü kaldırın. Oluşturulan T-SQL sorgusunu ayrılmış bir yönetici bağlantısı (DAC) aracılığıyla çalıştırırsanız, sorgu tüm yan tabloların süresi dolan satır sayısını verir. sys.syscommittab tablosunun boyutuna ve yan tablo sayısına bağlı olarak, bu sorgunun tamamlanması uzun sürebilir.

   Önemli

   Bu adım, risk azaltma adımlarına devam etmek için gereklidir. Önceki sorgu yürütülemezse, bir sonraki sorguyu kullanarak tek tek yan tablolar için süresi dolan satır sayılarını belirleyin.

Süresi dolan satır sayıları otomatik temizlemenin yetişebilmesi için yönetilebilir duruma gelene kadar, süresi dolan satır sayılarının azalan sırasına sahip olarak yan tablolar için aşağıdaki azaltma adımlarını gerçekleştirin.

Süresi dolmuş büyük kayıt sayılarına sahip yan tabloları tanımladıktan sonra, yan tablo silme deyimlerinin gecikme süresi ve son birkaç saat içindeki saniye başına silme hızı hakkında bilgi toplayın. Ardından, hem eski satır sayısını hem de silme gecikmesini göz önünde bulundurarak yan tabloyu temizlemek için gereken süreyi tahmin edin.

Parametre şablonlarını uygun değerlerle değiştirerek aşağıdaki T-SQL kod parçacığını kullanın.

• Saniye başına temizleme hızını sorgula:

  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;
  

  DATEDIFF işlevi için dakika veya saat ayrıntı düzeyini de kullanabilirsiniz.

• Yan tabloda bayat satır sayısını bulun. Bu sorgu, temizlenmek üzere bekleyen satır sayısını bulmanıza yardımcı olur.

  Kullanıcı tablosunun <internal_table_name> ve <cleanup_version> önceki bölümde döndürülen çıktıda yer alır. Bu bilgileri kullanarak, ayrılmış bir yönetici bağlantısı (DAC) aracılığıyla aşağıdaki T-SQL kodunu yürütebilirsiniz:

  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>
  );
  

  Bu sorguyu tamamlamak biraz zaman alabilir. Sorgunun zaman aşımına uğradıklarında, toplam satırlar ile etkin satırlar arasındaki farkı (temizlenecek satırlar) bularak eski satırları hesaplayın.

• Aşağıdaki sorguyu yürüterek yan tablodaki toplam satır sayısını bulun:

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

• Aşağıdaki sorguyu yürüterek yan tablodaki etkin satır sayısını bulun:

  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>);
  

  Temizleme oranını ve eski satır sayısını kullanarak tabloyu temizlemek için tahmini süreyi hesaplayabilirsiniz. Aşağıdaki formülü göz önünde bulundurun:

  Dakika cinsinden temizleme süresi = (eski satır sayısı) / (dakika cinsinden temizleme hızı)

  Tablo temizleme işlemini tamamlama süresi kabul edilebilirse ilerleme durumunu izleyin ve otomatik temizleme işleminin çalışmaya devam etmesine izin verin. Aksi takdirde detaya gitmek için sonraki adımlarla devam edin.

2. Tablo kilidi çakışmalarını denetleme

Temizleme işleminin tablo kilidi yükseltme çakışmaları nedeniyle ilerlemediğini ve bu çakışmaların temizlik işlemini yan tablodaki satırları silmek için sürekli olarak kilit almaktan alıkoyduğunu saptayın.

Kilit çakışmasını onaylamak için aşağıdaki T-SQL kodunu çalıştırın. Bu sorgu, kilit çakışmalarını gösteren birden çok giriş olup olmadığını belirlemek için sorunlu tabloya yönelik kayıtları getirir. Bir döneme yayılan birkaç düzensiz çakışma, devam eden risk azaltma adımlarına uygun olmamalıdır. Çakışmalar yinelenmeli.

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

Geçmiş tablosunun comments sütunlarında değeri Cleanup error: Lock request time out period exceededolan birden fazla giriş varsa, bu, ardışık olarak meydana gelen kilit çakışmaları veya kilit zaman aşımları nedeniyle birden çok temizleme girişiminin başarısız olduğunun açık bir göstergesidir. Aşağıdaki çözümleri göz önünde bulundurun:

• Sorunlu tabloda değişiklik izlemeyi devre dışı bırakın ve etkinleştirin. Bu, tablo için tutulan tüm izleme meta verilerinin temizlenmesine sebep olur. Tablonun verileri değişmeden kalır. Bu en hızlı çözümdür.

• Önceki seçenek mümkün değilse, aşağıdaki gibi izleme bayrağı 8284 etkinleştirerek tabloda el ile temizleme işlemi gerçekleştirmeye devam edin:

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

3. Diğer nedenleri denetleyin

Temizleme gecikmesinin bir diğer olası nedeni de delete deyimlerinin yavaşlığıdır. Bunun olup olmadığını belirlemek için hardened_cleanup_versiondeğerini denetleyin. Bu değer, dikkate alınan veritabanına ayrılmış bir yönetici bağlantısı (DAC) aracılığıyla alınabilir.

Aşağıdaki sorguyu yürüterek sağlamlaştırılmış temizleme sürümünü bulun:

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

Aşağıdaki sorguyu yürüterek temizleme sürümünü bulun:

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

hardened_cleanup_version ve cleanup_version değerleri eşitse, bu bölümü atlayın ve sonraki bölüme geçin.

Her iki değer de farklıysa, bir veya daha fazla yan tablo hatalarla karşılaştı demektir. En hızlı azaltma, sorunlu tabloda değişiklik izlemeyi devre dışı bırakmak ve &'ı etkinleştirmektir. Bu, tablo için tutulan tüm izleme meta verilerinin temizlenmesine sebep olur. Tablodaki veriler değişmeden kalır.

Önceki seçenek mümkün değilse tabloda el ile temizlemeyi çalıştırın.

Syscommittab sorunlarını giderme

Bu bölüm, çok fazla depolama alanı kullanıyorsa veya büyük bir eski satır kapsamına sahipse syscommittab sistem tablosuyla ilgili sorunları ayıklama ve azaltma adımlarını kapsar.

syscommittab sistem tablosunun temizliği, yan tablonun temizlenmesine bağlıdır. Yalnızca tüm yan tablolar temizlendikten sonra syscommittab temizlenebilir. Otomatik temizleme çalışırken ancak ilerleme kaydedilmediğinde, bölümündeki tüm adımların gerçekleştirildiğinden emin olun.

syscommittab temizlemeyi açıkça çağırmak için sys.sp_flush_commit_table_on_demand saklı yordamını kullanın.

Not

sys.sp_flush_commit_table_on_demand saklı yordamı, eğer büyük bir satır birikimini siliyorsa zaman alabilir.

sys.sp_flush_commit_table_on_demand makalesindeki örnek bölümde gösterildiği gibi, bu saklı yordam safe_cleanup_version()değerini ve silinen satır sayısını döndürür. Döndürülen değer 0gibi görünüyorsa ve anlık görüntü yalıtımı açıksa, temizleme syscommittabhiçbir şeyi silemez.

Bekletme süresi bir günden uzunsa, izleme bayrağı 8239 genel olarak etkinleştirildikten sonra saklı yordamın yeniden çalıştırılması sys.sp_flush_commit_table_on_demand güvenli olmalıdır. Anlık görüntü yalıtımı kapalı olduğunda bu izleme bayrağını kullanmak her zaman güvenlidir, ancak bazı durumlarda gerekli olmayabilir.

Temizleme sırasında yüksek CPU kullanımı

Bu bölümde açıklanan sorun SQL Server'ın eski sürümlerinde görülebilir. Veritabanında çok sayıda değişiklik izlenen tablo varsa ve otomatik temizleme veya el ile temizleme yüksek CPU kullanımına neden oluyorsa. Bu sorun, önceki bölümlerde kısaca bahsedilen geçmiş tablosundan da kaynaklanabilir.

Geçmiş tablosundaki satır sayısını denetlemek için aşağıdaki T-SQL kodunu kullanın:

SELECT COUNT(*) from dbo.MSChange_tracking_history;

Satır sayısı yeterince büyükse, aşağıdaki dizin eksikse eklemeyi deneyin. Dizini eklemek için aşağıdaki T-SQL kodunu kullanın:

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

Temizlemeyi 30 dakikadan daha sık çalıştır.

Belirli tablolarda yüksek değişiklik oranları yaşanabilir ve otomatik temizleme işinin yan tabloları ile syscommittab'ı 30 dakikalık zaman dilimi içinde temizleyemeyebileceğini fark edebilirsiniz. Böyle bir durumda, işlemi kolaylaştırmak için artan sıklıkta el ile temizleme işi çalıştırabilirsiniz.

SQL Server ve Azure SQL Yönetilen Örneği için, bir arka plan işi oluşturun. Azure SQL Veritabanı için Azure Logic Apps bu işleri zamanlamak için kullanılabilir.

Aşağıdaki T-SQL kodu, değişiklik izleme için yan tabloları temizlemeye yardımcı olacak bir iş oluşturmak için kullanılabilir:

-- 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;

İlgili içerik

• Değişiklik İzleme hakkında (SQL Server)
• Değişiklik İzleme İşlevleri (Transact-SQL)
• Değişiklik İzleme saklı yordamları (Transact-SQL)
• Değişiklik İzleme Tabloları (Transact-SQL)
• Veritabanı için veri ve günlük alanı bilgilerini görüntüleme
• SQL Server yüksek CPU kullanımı sorunlarını giderme