Oplossen van problemen met automatisch opschonen van het bijhouden van wijzigingen

Artikel
2025-03-05

van toepassing op:SQL Server Azure SQL Database Azure SQL Managed Instance

Dit artikel biedt manieren om veelvoorkomende problemen op te lossen die zich voordoen bij het automatisch opschonen van wijzigingsgeschiedenis.

Symptomen

Over het algemeen kunt u een of meer van de volgende symptomen zien als het automatisch opschonen niet werkt zoals verwacht:

Hoog opslagverbruik door een of meer tabellen aan de zijkant van het bijhouden van wijzigingen of de syscommittab systeemtabel.
Zijtabellen (interne tabellen waarvan de naam begint met voorvoegsel change_tracking, bijvoorbeeld change_tracking_12345) of syscommittab of beide, geven een aanzienlijk aantal rijen weer die buiten de geconfigureerde bewaarperiode vallen.
dbo.MSChange_tracking_history tabel bevat vermeldingen met specifieke schoonmaakfouten.
Na verloop van tijd zijn de prestaties van CHANGETABLE verslechterd.
Automatisch opschonen of handmatig opschonen rapporteert een hoog CPU-gebruik.

Foutopsporing en risicobeperking

Als u de hoofdoorzaak van een probleem met het automatisch opschonen van wijzigingen bijhouden wilt identificeren, gebruikt u de volgende stappen om fouten op te sporen en het probleem op te lossen.

Status van automatisch opschonen

Controleer of automatisch opschonen is uitgevoerd. U kunt dit controleren door een query uit te voeren op de opschoongeschiedenistabel in dezelfde database. Als het opschonen is uitgevoerd, bevat de tabel vermeldingen met de begin- en eindtijd van de opschoonactie. Als het opschonen niet is uitgevoerd, is de tabel leeg of bevat deze verouderde vermeldingen. Als de geschiedenistabel vermeldingen bevat met de tag cleanup errors in de kolom comments, mislukt het opschonen vanwege fouten bij het opschonen op tabelniveau.

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

Automatisch opschonen wordt periodiek uitgevoerd met een standaardinterval van 30 minuten. Als de geschiedenistabel niet bestaat, is automatisch opschonen waarschijnlijk nooit uitgevoerd. Controleer anders de kolomwaarden start_time en end_time. Als de meest recente items niet recent zijn, zijn ze uren of dagen oud, dan wordt automatisch opschonen mogelijk niet uitgevoerd. Als dat het geval is, gebruikt u de volgende stappen om problemen op te lossen.

1. Opschonen is uitgeschakeld

Controleer of automatisch opschonen is ingeschakeld voor de database. Als dit niet het geval is, schakelt u deze in en wacht u minstens 30 minuten voordat u naar de geschiedenistabel voor nieuwe vermeldingen kijkt. Controleer daarna de voortgang in de geschiedenistabel.

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

Een niet-nulwaarde in is_auto_cleanup_on geeft aan dat automatisch opschonen is ingeschakeld. De waarde van de bewaarperiode bepaalt de duur waarvoor metagegevens voor wijzigingen bijhouden in het systeem worden bewaard. De standaardwaarde voor de bewaarperiode voor het bijhouden van wijzigingen is 2 dagen.

Zie Wijzigingen bijhouden (SQL Server) in- of uitschakelenals u het bijhouden van wijzigingen wilt in- of uitschakelen.

2. Opschonen is ingeschakeld, maar wordt niet uitgevoerd

Als automatisch opschonen is ingeschakeld, is de thread voor automatisch opschonen waarschijnlijk gestopt vanwege onverwachte fouten. Momenteel is het opnieuw opstarten van de thread voor automatisch opschonen niet mogelijk. U moet een failover naar een secundaire server initiëren (of de server opnieuw opstarten als er geen secundaire server is) en controleren of de instelling voor automatisch opschonen is ingeschakeld voor de database.

Automatisch opschonen wordt uitgevoerd, maar er wordt geen voortgang geboekt.

Als een of meer zijtabellen een aanzienlijk opslagverbruik weergeven of een groot aantal records bevatten dat verder gaat dan geconfigureerde retentie, volgt u de stappen in deze sectie, waarin oplossingen voor één tabel worden beschreven. Dezelfde stappen kunnen zo nodig worden herhaald voor meer tabellen.

1. Automatische opschoningsachterstand beoordelen

Identificeer zijkanttabellen met een grote achterstand van verlopen records, waarop maatregelen moeten worden genomen. Voer de volgende query's uit om de zijtabellen met een groot aantal verlopen records te identificeren. Vergeet niet om de waarden in de voorbeeldscripts te vervangen, zoals wordt weergegeven.

Haal de ongeldige opschoonversie op:
```
SELECT * FROM sys.change_tracking_tables;
```
De cleanup_version waarde uit de geretourneerde rijen vertegenwoordigt de ongeldige opschoonversie.

Voer de volgende dynamische Transact-SQL -query (T-SQL) uit, waarmee de query wordt gegenereerd om het verlopen aantal rijen van side tables op te halen. Vervang de waarde van <invalid_version> in de query door de waarde die u in de vorige stap hebt verkregen.

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;

Kopieer de resultatenset uit de vorige query en verwijder het trefwoord UNION uit de laatste rij. Als u de gegenereerde T-SQL-query uitvoert via een toegewezen beheerdersverbinding (DAC), geeft de query het aantal verlopen rijen van alle side tables. Afhankelijk van de grootte van de sys.syscommittab tabel en het aantal zijtabellen kan het lang duren voordat deze query is voltooid.

Belangrijk

Deze stap is nodig om verder te gaan met de risicobeperkingsstappen. Als de vorige query niet kan worden uitgevoerd, identificeert u het aantal verlopen rijen voor de afzonderlijke zijtabellen met behulp van de volgende query's.

Voer de volgende mitigatiestappen uit voor de zijtabellen, in aflopende volgorde van verlopen rijen, totdat het aantal verlopen rijen tot een beheerbare status komt, zodat het automatisch opschonen kan bijhouden.

Zodra u de zijtabellen met grote verlopen recordaantallen hebt geïdentificeerd, verzamelt u informatie over de latentie van de verwijderingsinstructies voor de zijtabel en de snelheid van verwijdering per seconde gedurende de afgelopen uren. Maak vervolgens een schatting van de tijd die nodig is om de zijtabel op te schonen door rekening te houden met zowel het verouderde aantal rijen als de latentie voor verwijderen.

Gebruik het volgende T-SQL-codefragment door parametersjablonen te vervangen door de juiste waarden.

Voer een query uit voor de snelheid van opschoning per seconde:

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;

U kunt ook minuut- of uurgranulariteit gebruiken voor de functie DATEDIFF.

Zoek het aantal verouderde rijen in de zijtabel. Deze query helpt u bij het vinden van het aantal rijen dat moet worden opgeschoond.

De <internal_table_name> en <cleanup_version> voor de gebruikerstabel staan in de uitvoer die in de vorige sectie is geretourneerd. Voer met behulp van deze informatie de volgende T-SQL-code uit via een toegewezen beheerdersverbinding (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>
);
```
Het kan enige tijd duren voordat deze query is voltooid. In gevallen waarin de query een time-out heeft, berekent u verouderde rijen door het verschil te vinden tussen het totaal aantal rijen en de actieve rijen die moeten worden opgeschoond.

Zoek het totale aantal rijen in de zijtabel door de volgende query uit te voeren:

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

Zoek het aantal actieve rijen in de zijtabel door de volgende query uit te voeren:
```
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>);
```
U kunt de geschatte tijd berekenen om de tabel op te schonen met behulp van de schoonmaaksnelheid en het aantal verouderde rijen. Houd rekening met de volgende formule:

Tijd om op te ruimen in minuten = (aantal verouderde rijen) / (snelheid van opruimen in minuten)

Als de tijd voor het tabellen opschonen acceptabel is, houd dan de voortgang in de gaten en laat de automatische opschoning doorgaan. Zo niet, ga dan verder met de volgende stappen om verder in te zoomen.

Controleer op conflicten met tabelvergrendeling

Bepaal of het opschonen niet vordert door conflicten veroorzaakt door escalatie van tabelvergrendelingen, waardoor het opschonen voortdurend wordt verhinderd om vergrendelingen op de zijtabel te verkrijgen en rijen te verwijderen.

Voer de volgende T-SQL-code uit om een vergrendelingsconflict te bevestigen. Met deze query worden records opgehaald voor de problematische tabel om te bepalen of er meerdere vermeldingen zijn die duiden op vergrendelingsconflicten. Een aantal sporadische conflicten verspreid over een periode mag niet in aanmerking komen voor de stappen voor het beperken van de procedure. De conflicten moeten terugkeren.

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

Als de geschiedenistabel meerdere vermeldingen bevat in de comments-kolommen met de waarde Cleanup error: Lock request time out period exceeded, is het een duidelijke indicatie dat meerdere opschoonpogingen zijn mislukt vanwege vergrendelingsconflicten of vergrendelingstime-outs achtereenvolgens. Houd rekening met de volgende oplossingen:

Schakel wijzigingen bijhouden op de problematische tabel uit en weer in. Dit zorgt ervoor dat alle bijgehouden metagegevens voor de tabel worden opgeschoond. De gegevens van de tabel blijven intact. Dit is de snelste remedie.
Als de vorige optie niet mogelijk is, gaat u als volgt handmatig opschonen op de tabel uitvoeren door traceringsvlag 8284 als volgt in te schakelen:
```
DBCC TRACEON (8284, -1);
GO
EXEC [sys].[sp_flush_CT_internal_table_on_demand] @TableToClean = '<table_name>';
```

3. Controleer andere oorzaken

Een andere mogelijke oorzaak van het achterblijven van het opschonen is de traagheid van de verwijderingsopdrachten. Als u wilt bepalen of dat zo is, controleert u de waarde van hardened_cleanup_version. Deze waarde kan worden opgehaald via een toegewezen beheerdersverbinding (DAC) met de database die wordt overwogen.

Zoek de geharde opschoonversie door de volgende query uit te voeren:

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

Zoek de opschoonversie door de volgende query uit te voeren:

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

Als hardened_cleanup_version en cleanup_version waarden gelijk zijn, slaat u deze sectie over en gaat u verder met de volgende sectie.

Als beide waarden verschillen, betekent dit dat er fouten zijn opgetreden in een of meer zijtabellen. De snelste oplossing is het uitschakelen van & het bijhouden van wijzigingen in de problematische tabel in te schakelen. Dit zorgt ervoor dat alle bijgehouden metagegevens voor de tabel worden opgeschoond. De gegevens in de tabel blijven intact.

Als de vorige optie niet mogelijk is, voert u handmatig opschonen uit op de tabel.

Problemen met syscommittab oplossen

In deze sectie worden stappen beschreven voor het opsporen en oplossen van problemen met de syscommittab systeemtabel, als er veel opslagruimte wordt gebruikt of als er een grote achterstand is met verouderde rijen.

De opschoning van de syscommittab systeemtabel is afhankelijk van het opschonen van de zijtabel. Pas nadat alle zijtabellen zijn opgeschoond kan syscommittab worden gezuiverd. Zorg ervoor dat alle stappen in de sectie automatisch opschonen worden uitgevoerd, maar dat er in de sectie geen voortgang wordt geboekt.

Gebruik de opgeslagen procedure sys.sp_flush_commit_table_on_demand om expliciet de syscommittab opruiming uit te voeren.

Notitie

De sys.sp_flush_commit_table_on_demand stored procedure kan lang duren als er een grote achterstand aan rijen wordt verwijderd.

Zoals wordt weergegeven in de voorbeeldsectie uit het sys.sp_flush_commit_table_on_demand artikel, retourneert deze opgeslagen procedure de waarde van safe_cleanup_version()en het aantal rijen dat is verwijderd. Als de geretourneerde waarde 0lijkt te zijn en als isolatie van momentopnamen is ingeschakeld, verwijdert de opschoonbewerking mogelijk niets uit syscommittab.

Als de bewaarperiode groter is dan één dag, moet het veilig zijn om de sys.sp_flush_commit_table_on_demand opgeslagen procedure opnieuw uit te voeren nadat traceringsvlag 8239 wereldwijd is ingeschakeld. Het gebruik van deze traceringsvlag wanneer de isolatie van momentopnamen is uitgeschakeld, is altijd veilig, maar in sommige gevallen is dit mogelijk niet nodig.

Hoog CPU-gebruik tijdens het opschonen

Het probleem dat in deze sectie wordt beschreven, kan worden weergegeven in oudere versies van SQL Server. Wanneer er een groot aantal bijgehouden tabellen in een database aanwezig zijn en het automatisch of handmatig opschonen een hoog CPU-gebruik veroorzaakt. Dit probleem kan ook worden veroorzaakt door de geschiedenistabel, die kort in eerdere secties is vermeld.

Gebruik de volgende T-SQL-code om het aantal rijen in de geschiedenistabel te controleren:

SELECT COUNT(*) from dbo.MSChange_tracking_history;

Als het aantal rijen voldoende groot is, moet u de volgende index toevoegen als deze nog niet bestaat. Gebruik de volgende T-SQL-code om de index toe te voegen:

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

Opschonen vaker dan 30 minuten uitvoeren

Specifieke tabellen kunnen een hoge mate van veranderingen ondergaan, en mogelijk ontdekt u dat de taak voor automatisch opschonen de zijtabellen en syscommittab niet binnen het 30-minuten interval kan opruimen. Als dit gebeurt, kunt u een handmatige opschoontaak uitvoeren met een verhoogde frequentie om het proces te vergemakkelijken.

Voor SQL Server en Azure SQL Managed Instance maakt een achtergrondtaak met behulp van sp_flush_CT_internal_table_on_demand met een kortere interne waarde dan de standaard 30 minuten. Voor Azure SQL Database kan Azure Logic Apps- worden gebruikt om deze taken te plannen.

De volgende T-SQL-code kan worden gebruikt om een taak te maken om de zijtabellen op te ruimen voor het bijhouden van wijzigingen:

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

Delen via