Felsöka problem med automatisk rensning av ändringsspårning

gäller för:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

Den här artikeln innehåller sätt att felsöka vanliga problem som observerats vid automatisk rensning av ändringsspårning.

Symtom

Om den automatiska rensningen inte fungerar som förväntat kan du se ett eller flera av följande symtom:

• En hög lagringsförbrukning på grund av en eller flera ändringsspårningstilläggstabeller eller syscommittab-systemtabellen.
• Sidotabeller (interna tabeller vars namn börjar med prefixet change_tracking, till exempel change_tracking_12345) eller syscommittab eller båda, visar ett betydande antal rader som ligger utanför den konfigurerade kvarhållningsperioden.
• dbo.MSChange_tracking_history tabellen innehåller poster med specifika rensningsfel.
• CHANGETABLE prestanda har försämrats med tiden.
• Automatisk rensning eller manuell rensning rapporterar hög CPU-användning.

Felsökning och åtgärd

Vill du identifiera grundorsaken till ett problem med automatisk rensning av ändringsspårning, använd följande steg för att felsöka och åtgärda problemet.

Automatisk rensningsstatus

Kontrollera om automatisk rensning har körts. Kontrollera detta genom att utföra en fråga mot rensningshistoriktabellen i samma databas. Om rensningen har körts har tabellen poster med start- och sluttiderna för rensningen. Om rensningen inte har körts är tabellen tom eller har inaktuella poster. Om historiktabellen har poster med taggen cleanup errors i kolumnen commentsmisslyckas rensningen på grund av rensningsfel på tabellnivå.

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

Automatisk rensning körs regelbundet med ett standardintervall på 30 minuter. Om historiktabellen inte finns har den automatiska rensningen förmodligen aldrig körts. Annars kontrollerar du kolumnvärdena start_time och end_time. Om de senaste posterna inte är nya, dvs. de är timmar eller dagar gamla, kanske automatisk rensning inte körs. Om så är fallet använder du följande steg för att felsöka.

1. Rensningen är avstängd

Kontrollera om automatisk rensning är aktiverat för databasen. Om den inte är det aktiverar du den och väntar i minst 30 minuter innan du tittar på historiktabellen för nya poster. Övervaka förloppet i historiktabellen därefter.

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

Ett värde som inte är noll i is_auto_cleanup_on anger att automatisk rensning är aktiverat. Värdet för kvarhållningsperioden styr hur länge metadata för ändringsspårning behålls i systemet. Standardvärdet för kvarhållningsperioden för ändringsspårning är 2 dagar.

Information om hur du aktiverar eller inaktiverar ändringsspårning finns i Aktivera och inaktivera ändringsspårning (SQL Server).

2. Rensning är aktiverat men körs inte

Om automatisk rensning är på stoppas troligen tråden för automatisk rensning på grund av oväntade fel. För närvarande är det inte möjligt att starta om tråden för automatisk rensning. Du måste initiera en redundansväxling till en sekundär server (eller starta om servern i avsaknad av en sekundär) och bekräfta att inställningen för automatisk rensning är aktiverad för databasen.

Automatisk rensning körs men gör inte framsteg

Om en eller flera sidotabeller visar betydande lagringsförbrukning, eller innehåller ett stort antal poster utöver konfigurerad kvarhållning, följer du stegen i det här avsnittet som beskriver åtgärder för en tabell på en sida. Samma steg kan upprepas för fler tabeller om det behövs.

1. Utvärdera kvarvarande uppgifter om automatisk rensning

Identifiera sidotabeller som har ett stort antal utgångna poster som behöver åtgärdas. Kör följande sökfrågor för att identifiera sidotabellerna med stora mängder utgångna poster. Kom ihåg att ersätta värdena i exempelskripten på det sätt som visas.

1. Hämta den ogiltiga rensningsversionen:

   SELECT * FROM sys.change_tracking_tables;
   

   Värdet cleanup_version från de returnerade raderna representerar den ogiltiga rensningsversionen.

2. Kör följande fråga för dynamisk Transact-SQL (T-SQL), som genererar frågan för att hämta antalet utgångna rader i sidotabeller. Ersätt värdet för <invalid_version> i frågan med värdet som erhölls i föregående steg.

   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. Kopiera resultatuppsättningen från föregående fråga och ta bort nyckelordet UNION från den sista raden. Om du kör den genererade T-SQL-frågan via en dedikerad administratörsanslutning (DAC) ger frågan antalet utgångna rader för alla sidotabeller. Beroende på storleken på tabellen sys.syscommittab och antalet sidotabeller kan det ta lång tid att slutföra den här frågan.

   Viktig

   Det här steget är nödvändigt för att kunna gå vidare med åtgärdsstegen. Om den föregående sökfrågan inte kan köras, identifiera antalet utgångna rader för de enskilda sidotabellerna med hjälp av de frågor som anges nedan.

Utför följande åtgärdssteg för sidotabellerna, i fallande ordning efter antal utgångna rader, tills antalet utgångna rader kommer ned till ett hanterbart tillstånd så att den automatiska rensningen kommer ikapp.

När du har identifierat de sekundära tabellerna med stora antal förfallna poster i databasen samlar du in information om latensen för borttagningskommandona i de sekundära tabellerna och borttagningshastigheten per sekund under de senaste timmarna. Beräkna sedan den tid som krävs för att rensa sidotabellen genom att överväga både antalet inaktuella rader och svarstiden för borttagning.

Använd följande T-SQL-kodfragment genom att ersätta parametermallar med lämpliga värden.

• Fråga rensningshastigheten per sekund:

  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;
  

  Du kan också använda minut- eller timkornighet för funktionen DATEDIFF.

• Hitta inaktuellt radantal i sidotabellen. Den här frågan hjälper dig att hitta antalet rader som väntar på att rensas.

  <internal_table_name> och <cleanup_version> för användartabellen finns i utdata som returnerades i föregående avsnitt. Med den här informationen kör du följande T-SQL-kod via en dedikerad administratörsanslutning (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>
  );
  

  Den här frågan kan ta lite tid att slutföra. I de fall då frågan överskrider tidsgränsen beräknar du inaktuella rader genom att hitta skillnaden mellan totalt antal rader och aktiva rader, dvs. rader som ska rensas.

• Leta reda på det totala antalet rader i sidotabellen genom att köra följande fråga:

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

• Hitta antalet aktiva rader i sidotabellen genom att köra följande fråga:

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

  Du kan beräkna den uppskattade tiden för att rensa tabellen med hjälp av rensningshastigheten och antalet inaktuella rader. Tänk på följande formel:

  Tid att rensa i minuter = (antal föråldrade rader) / (rensningshastighet i minuter)

  Om tiden för att slutföra tabellrensningen är acceptabel övervakar du förloppet och låter den automatiska rensningen fortsätta sitt arbete. Om inte fortsätter du med nästa steg för att öka detaljnivån ytterligare.

2. Kontrollera tabelllåskonflikter

Kontrollera om rensningen inte fortsätter på grund av konflikter orsakade av tabellåsupptrappning, som konsekvent förhindrar rensningen från att få lås på sidotabellen för att ta bort rader.

Bekräfta en låskonflikt genom att köra följande T-SQL-kod. Den här frågan hämtar poster för den problematiska tabellen för att avgöra om det finns fler poster som anger låskonflikter. Några sporadiska konflikter som är fördelade över tid bör inte kvalificera sig för de fortsatta åtgärderna. Konflikterna bör vara återkommande.

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

Om historiktabellen har flera poster i comments-kolumnerna med värdet Cleanup error: Lock request time out period exceeded, är det en tydlig indikation på att flera rensningsförsök misslyckades på grund av låskonflikter eller låsningstidgränser i följd. Överväg följande åtgärder:

• Inaktivera och aktivera ändringsspårning i den problematiska tabellen. Detta gör att alla spårningsmetadata som underhålls för tabellen rensas. Tabellens data förblir intakta. Detta är det snabbaste botemedlet.

• Om det föregående alternativet inte är möjligt fortsätter du att köra manuell rensning i tabellen genom att aktivera spårningsflagga 8284 på följande sätt:

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

3. Kontrollera andra orsaker

En annan möjlig orsak till att rensningen släpar efter är borttagningsinstruktionernas långsamhet. Kontrollera i så fall värdet för hardened_cleanup_version. Det här värdet kan hämtas via en dedikerad administratörsanslutning (DAC) till databasen som övervägs.

Hitta den härdade rensningsversionen genom att köra följande fråga:

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

Hitta rensningsversionen genom att köra följande fråga:

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

Om hardened_cleanup_version och cleanup_version värden är lika hoppar du över det här avsnittet och fortsätter till nästa avsnitt.

Om båda värdena skiljer sig innebär det att en eller flera sidotabeller påträffade fel. Den snabbaste åtgärden är att inaktivera & och aktivera ändringsspårning på den problematiska tabellen. Detta gör att alla spårningsmetadata som underhålls för tabellen rensas. Data i tabellen förblir intakta.

Om det föregående alternativet inte är möjligt kör du manuell rensning i tabellen.

Felsöka problem med syscommittab

Det här avsnittet beskriver steg för att felsöka och åtgärda problem med syscommittab-systemtabellen, om den använder mycket lagringsutrymme eller om den har en stor kvarvarande mängd inaktuella rader.

Rensningen av syscommittab-systemtabellen är beroende av sidotabellernas rensning. Först när alla sidotabeller har rensats kan syscommittab raderas. Kontrollera att alla steg utförs i avsnittet Automatisk rensning, som körs men inte gör några framsteg.

För att särskilt anropa syscommittab-rensningen, använd den lagrade proceduren sys.sp_flush_commit_table_on_demand.

Anteckning

Den sys.sp_flush_commit_table_on_demand lagrade proceduren kan ta tid om den tar bort en stor kvarvarande rad.

Som du ser i exempelavsnittet från artikeln sys.sp_flush_commit_table_on_demand returnerar den här lagrade proceduren värdet för safe_cleanup_version()och antalet rader som tagits bort. Om värdet som returneras verkar vara 0och om ögonblicksbildisolering är aktiverat kanske rensningen inte tar bort något från syscommittab.

Om kvarhållningsperioden är större än en dag bör det vara säkert att köra den sys.sp_flush_commit_table_on_demand lagrade proceduren igen när du har aktiverat spårningsflagga 8239 globalt. Att använda den här spårningsflaggan när ögonblicksbildisolering är av är alltid säkert, men i vissa fall kanske det inte är nödvändigt.

Hög CPU-användning vid rensning

Problemet som beskrivs i det här avsnittet kan visas på äldre versioner av SQL Server. Om det finns ett stort antal ändringsspårade tabeller i en databas och automatisk rensning eller manuell rensning orsakar hög CPU-användning. Det här problemet kan också orsakas på grund av historiktabellen, som nämndes kort i tidigare avsnitt.

Använd följande T-SQL-kod för att kontrollera antalet rader i historiktabellen:

SELECT COUNT(*) from dbo.MSChange_tracking_history;

Om antalet rader är tillräckligt stort kan du prova att lägga till följande index om det saknas. Använd följande T-SQL-kod för att lägga till indexet:

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

Kör rensning oftare än 30 minuter

Vissa tabeller kan uppleva en hög ändringshastighet och du kanske upptäcker att autocleanup-jobbet inte kan rensa sidotabellerna och syscommittab inom 30-minutersintervallet. Om detta inträffar kan du köra ett manuellt rensningsjobb med ökad frekvens för att underlätta processen.

För SQL Server och Azure SQL Managed Instance skapa ett bakgrundsjobb med hjälp av sp_flush_CT_internal_table_on_demand med ett kortare intervall än standardvärdet på 30 minuter. För Azure SQL Database kan Azure Logic Apps- användas för att schemalägga dessa jobb.

Följande T-SQL-kod kan användas för att skapa ett jobb för att rensa sidotabellerna för ändringsspårning:

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

Relaterat innehåll

• om ändringsspårning (SQL Server)
• Ändringsspårningsfunktioner (Transact-SQL)
• lagrade procedurer för ändringsspårning (Transact-SQL)
• spårningstabell för ändringar (Transact-SQL)
• Visa information om data och loggutrymme för en databas
• Felsöka problem med hög CPU-användning i SQL Server