Behandlung von Problemen bei der automatischen Bereinigung von Änderungen

Gilt für: SQL Server Azure SQL-Datenbank Azure SQL Managed Instance

Dieser Artikel bietet Möglichkeiten, häufige Probleme zu beheben, die bei der automatischen Bereinigung der Änderungsnachverfolgung beobachtet werden.

Problembeschreibung

Wenn die automatische Bereinigung nicht wie erwartet funktioniert, können Sie ein oder mehrere der folgenden Symptome sehen:

• Hoher Speicherverbrauch durch eine oder mehrere Änderungsnachverfolgungsseitentabellen oder die syscommittab Systemtabelle.
• Seitliche Tabellen (interne Tabellen, deren Name mit dem Präfix change_trackingbeginnt , z. B. change_tracking_12345) oder syscommittab oder beides, zeigen eine signifikante Anzahl von Zeilen an, die sich außerhalb des konfigurierten Aufbewahrungszeitraums befinden.
• dbo.MSChange_tracking_history Die Tabelle enthält Einträge mit bestimmten Bereinigungsfehlern.
• CHANGETABLE Die Leistung hat sich im Laufe der Zeit verschlechtert.
• Automatische Bereinigung oder manuelle Bereinigung meldet eine hohe CPU-Auslastung.

Debuggen und Ausgleich

Um die Ursache eines Problems mit der automatischen Bereinigung von Änderungen zu identifizieren, führen Sie die folgenden Schritte aus, um das Problem zu debuggen und zu beheben.

Status der automatischen Bereinigung

Überprüfen Sie, ob die automatische Bereinigung ausgeführt wurde. Um dies zu überprüfen, durchsuchen Sie die Tabelle der Bereinigungshistorie in derselben Datenbank. Wenn die Bereinigung ausgeführt wurde, enthält die Tabelle Einträge mit den Anfangs- und Endzeiten der Bereinigung. Wenn die Bereinigung nicht ausgeführt wurde, ist die Tabelle leer oder beinhaltet veraltete Einträge. Wenn die Verlaufstabelle Einträge mit dem Tag cleanup errors in der Spalte commentsenthält, tritt aufgrund von Bereinigungsfehlern auf Tabellenebene ein Fehler auf.

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

Die automatische Bereinigung wird regelmäßig mit einem standardmäßigen Intervall von 30 Minuten durchgeführt. Wenn die Verlaufstabelle nicht vorhanden ist, wurde die automatische Bereinigung wahrscheinlich nie ausgeführt. Überprüfen Sie andernfalls die Spaltenwerte start_time und end_time. Wenn die letzten Einträge nicht aktuell sind, d.h. Stunden oder Tage alt, dann läuft die automatische Bereinigung möglicherweise nicht. Wenn dies der Fall ist, führen Sie die folgenden Schritte aus, um Probleme zu beheben.

1. Die Bereinigung ist deaktiviert

Sehen Sie nach, ob für die Datenbank die automatische Bereinigung aktiviert ist. Wenn dies nicht der Fall ist, aktivieren Sie sie und warten Sie mindestens 30 Minuten, bis Sie in der Verlaufstabelle nach neuen Einträgen suchen. Anschließend können Sie den Fortschritt in der Verlaufstabelle überprüfen.

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

Ein Wert ungleich Null in is_auto_cleanup_on gibt an, dass die automatische Bereinigung aktiviert ist. Der Wert für den Aufbewahrungszeitraum steuert die Dauer, für die Metadaten zur Änderungsverfolgung im System aufbewahrt werden. Der Standardwert für den Aufbewahrungszeitraum für die Änderungsnachverfolgung beträgt 2 Tage.

Wie Sie die Änderungsverfolgung aktivieren oder deaktivieren, erfahren Sie unter Aktivieren und Deaktivieren der Änderungsverfolgung (SQL Server).

2. Bereinigung ist aktiviert, wird aber nicht ausgeführt

Wenn die automatische Bereinigung aktiviert ist, wurde der automatische Bereinigungs-Thread wahrscheinlich aufgrund unerwarteter Fehler beendet. Derzeit ist der Neustart des automatischen Bereinigungs-Threads nicht möglich. Sie müssen ein Failover auf einen Sekundärserver einleiten (oder den Server neu starten, wenn es keinen Sekundärserver gibt) und sicherstellen, dass die automatische Bereinigung für die Datenbank aktiviert ist.

Automatische Bereinigung wird ausgeführt, zeigt aber keinen Fortschritt

Wenn eine oder mehrere Seitentabellen einen erheblichen Speicherverbrauch aufweisen oder eine große Anzahl von Datensätzen enthalten, die über die konfigurierte Aufbewahrungszeit hinausgehen, führen Sie die Schritte in diesem Abschnitt aus, die Abhilfemaßnahmen für eine einzelne Seitentabelle beschreiben. Bei Bedarf können dieselben Schritte für weitere Tabellen wiederholt werden.

1. Bewerten des automatischen Bereinigungs-Backlogs

Identifizieren Sie Seitentabellen, die einen großen Rückstand an abgelaufenen Datensätzen aufweisen, die entschärft werden müssen. Führen Sie die folgenden Abfragen aus, um die Seitentabellen mit einer hohen Anzahl abgelaufener Datensätze zu ermitteln. Denken Sie daran, die Werte in den Beispielskripten wie gezeigt zu ersetzen.

1. Abrufen der ungültigen Bereinigungsversion:

   SELECT * FROM sys.change_tracking_tables;
   

   Der cleanup_version Wert aus den zurückgegebenen Zeilen stellt die ungültige Bereinigungsversion dar.

2. Führen Sie die folgende dynamische Transact-SQL-Abfrage (T-SQL) aus, die die Abfrage generiert, um die Anzahl der abgelaufenen Zeilen von Seitentabellen abzurufen. Ersetzen Sie den Wert <invalid_version> in der Abfrage durch den Wert, der im vorherigen Schritt abgerufen wurde.

   SELECT 'SELECT ''' + QUOTENAME(name) + ''', count(*) 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. Kopieren Sie das Resultset aus der vorherigen Abfrage, und entfernen Sie das Schlüsselwort UNION aus der letzten Zeile. Wenn Sie die generierte T-SQL-Abfrage über eine dedizierte Administrator-Verbindung (DAC) ausführen, gibt die Abfrage die Anzahl der abgelaufenen Zeilen aller Seitentabellen an. Abhängig von der Größe der Tabelle sys.syscommittab und der Anzahl der Seitentabellen kann die Ausführung dieser Abfrage sehr lange dauern.

   Wichtig

   Dieser Schritt ist erforderlich, um mit den Entschärfungsschritten fortzufahren. Wenn die vorherige Abfrage nicht ausgeführt werden kann, identifizieren Sie die Anzahl der abgelaufenen Zeilen für die einzelnen Seitentabellen mithilfe der folgenden Abfragen.

Führen Sie die folgenden Entschärfungsschritte für die Seitentabellen aus, wobei die abnehmende Reihenfolge der Anzahl abgelaufener Zeilen bis zu einem verwaltbaren Zustand für die automatische Bereinigung führt, um nachzuholen.

Nachdem Sie die Seitentabellen mit einer großen Anzahl abgelaufener Datensätze identifiziert haben, sammeln Sie Informationen über die Wartezeit der Löschanweisungen der Seitentabelle und die Löschrate pro Sekunde in den letzten Stunden. Schätzen Sie als Nächstes die Zeit, die erforderlich ist, um die Seitentabelle zu bereinigen, indem Sie sowohl die veraltete Zeilenanzahl als auch die Lösch-Wartezeit berücksichtigen.

Verwenden Sie den folgenden T-SQL-Codeausschnitt, indem Sie Parametervorlagen durch entsprechende Werte ersetzen.

• Abfrage der Bereinigungsrate pro Sekunde:

  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;
  

  Sie können auch die Granularität von Minuten oder Stunden für die Funktion DATEDIFF verwenden.

• Suchen Sie die Anzahl der veralteten Zeilen in der Seitentabelle. Mit dieser Abfrage können Sie die Anzahl der Zeilen ermitteln, die noch bereinigt werden müssen.

  Die <internal_table_name> und <cleanup_version> für die Benutzertabelle befinden sich in der Ausgabe, die im vorherigen Abschnitt zurückgegeben wird. Führen Sie mithilfe dieser Informationen den folgenden T-SQL-Code über eine dedizierte Administrator-Verbindung (DAC) aus:

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

  Diese Abfrage kann einige Zeit in Anspruch nehmen. In Fällen, in denen die Abfrage eine Zeitüberschreitung aufweist, berechnen Sie veraltete Zeilen, indem Sie den Unterschied zwischen den Gesamtzeilen und den aktiven Zeilen ermitteln, die bereinigt werden sollen.

• Suchen Sie die Gesamtzahl der Zeilen in der Seitentabelle, indem Sie die folgende Abfrage ausführen:

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

• Ermitteln Sie die Anzahl der aktiven Zeilen in der Seitentabelle, indem Sie die folgende Abfrage ausführen:

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

  Sie können die geschätzte Zeit für die Bereinigung der Tabelle anhand der Bereinigungsrate und der Anzahl der veralteten Zeilen berechnen. Betrachten Sie die folgende Formel:

  Zeit zum Bereinigen in Minuten = (Anzahl der veralteten Zeilen) / (Bereinigungsrate in Minuten)

  Wenn die Zeit zum Bereinigen der Tabelle akzeptabel ist, überwachen Sie den Fortschritt, und lassen Sie die automatische Bereinigung ihre Arbeit fortsetzen. Wenn nicht, fahren Sie mit den nächsten Schritten fort, um einen weiteren Drilldown auszuführen.

2. Überprüfen von Tabellensperrkonflikten

Stellen Sie fest, ob die Bereinigung aufgrund von Konflikten bei der Eskalation von Tabellensperren nicht vorankommt, die die Bereinigung konsequent daran hindern, die Sperren für eine Seitentabelle zu erhalten, um Zeilen zu löschen.

Führen Sie den folgenden T-SQL-Code aus, um einen Sperrkonflikt zu bestätigen. Mit dieser Abfrage rufen Sie die Datensätze der Tabelle ab, in der das Problem auftritt. Anhand dieser Daten können Sie herausfinden, ob es mehrere Einträge gibt, die auf Konflikte mit der Sperre hinweisen. Einige sporadische Konflikte, die über einen Zeitraum verteilt sind, sollten sich nicht für die fortschreitenden Entschärfungsschritte qualifizieren. Die Konflikte sollten immer wieder auftreten.

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

Wenn die Verlaufstabelle mehrere Einträge in den Spalten comments mit dem Wert Cleanup error: Lock request time out period exceeded enthält, ist es ein klarer Hinweis darauf, dass mehrere Bereinigungsversuche aufgrund von Sperrkonflikten oder Sperr-Timeouts nacheinander fehlgeschlagen sind. Berücksichtigen Sie die folgenden Abhilfemaßnahmen:

• Deaktivieren und aktivieren Sie die Änderungsnachverfolgung für die Tabelle, in der das Problem auftritt. Dadurch werden alle Nachverfolgungsmetadaten, die für die Tabelle aufbewahrt werden, bereinigt. Die Daten der Tabelle bleiben intakt. Dies ist der schnellste Lösungsweg.

• Wenn die vorherige Option nicht möglich ist, bereinigen Sie die Tabelle manuelle. Aktivieren Sie dazu das Ablaufverfolgungsflag 8284 wie folgt:

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

3. Überprüfung anderer Ursachen

Eine weitere mögliche Ursache für die Bereinigungsverzögerung ist die Langsamkeit der Löschanweisungen. Um festzustellen, ob dies der Fall ist, überprüfen Sie den Wert von hardened_cleanup_version. Dieser Wert kann über eine dedizierte Administrator-Verbindung (DAC) mit der zu berücksichtigenden Datenbank abgerufen werden.

Suchen Sie die verschärfte Bereinigungsversion, indem Sie die folgende Abfrage ausführen:

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

Suchen Sie die Bereinigungsversion, indem Sie die folgende Abfrage ausführen:

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

Wenn hardened_cleanup_version und cleanup_version Werte gleich sind, überspringen Sie diesen Abschnitt, und fahren Sie mit dem nächsten Abschnitt fort.

Wenn beide Werte unterschiedlich sind, bedeutet dies, dass mindestens in einer Seitentabelle Fehler aufgetreten sind. Die schnellste Entschärfung besteht darin, die Änderungsnachverfolgung in der problematischen Tabelle zu deaktivieren und zu aktivieren. Dadurch werden alle Nachverfolgungsmetadaten, die für die Tabelle aufbewahrt werden, bereinigt. Die Daten der Tabelle bleiben intakt.

Wenn die vorherige Option nicht möglich ist, führen Sie die manuelle Bereinigung in der Tabelle aus.

Problembehandlung von syscommittab-Problemen

In diesem Abschnitt werden die Schritte zum Debuggen und Beheben von Problemen mit der syscommittab-Systemtabelle behandelt, wenn sie viel Speicherplatz verwendet oder wenn ein großer Rückstand veralteter Zeilen vorhanden ist.

Die Bereinigung der syscommittab-Systemtabelle hängt von der Bereinigung der Seitentabelle ab. Erst nachdem alle Seitentabellen bereinigt wurden, kann syscommittab endgültig gelöscht werden. Vergewissern Sie sich, dass alle Schritte im Abschnitt Automatische Bereinigung läuft, macht aber keine Fortschritte ausgeführt werden.

Um die syscommittab Bereinigung explizit aufzurufen, verwenden Sie die gespeicherte Prozedur sys.sp_flush_commit_table_on_demand.

Hinweis

Die sys.sp_flush_commit_table_on_demand gespeicherte Prozedur kann Zeit in Anspruch nehmen, wenn ein großer Rückstand von Zeilen gelöscht wird.

Wie im Beispielabschnitt aus dem Artikel sys.sp_flush_commit_table_on_demand gezeigt, gibt diese gespeicherte Prozedur den Wert von safe_cleanup_version() und die Anzahl der gelöschten Zeilen zurück. Wenn der zurückgegebene Wert 0 angezeigt wird und die Momentaufnahme-Isolation aktiviert ist, löscht die Bereinigung möglicherweise nichts aus syscommittab.

Wenn der Aufbewahrungszeitraum größer als ein Tag ist, sollte es sicher sein, die gespeicherte Prozedur sys.sp_flush_commit_table_on_demand erneut auszuführen, nachdem Sie Trace Flag 8239 global aktiviert haben. Die Verwendung dieses Trace-Flags bei ausgeschalteter Momentaufnahme-Isolierung ist immer sicher, aber in manchen Fällen ist sie vielleicht nicht notwendig.

Hohe CPU-Auslastung während der Bereinigung

Das in diesem Abschnitt beschriebene Problem kann in älteren Versionen von SQL Server auftreten. Wenn eine große Anzahl von Änderungsnachverfolgungs-Tabellen in einer Datenbank vorhanden ist und die automatische Bereinigung oder manuelle Bereinigung eine hohe CPU-Auslastung verursacht. Dieses Problem kann auch aufgrund der Verlaufstabelle verursacht werden, die in früheren Abschnitten kurz erwähnt wurde.

Verwenden Sie den folgenden T-SQL-Code, um die Anzahl der Zeilen in der Verlaufstabelle zu überprüfen:

SELECT COUNT(*) from dbo.MSChange_tracking_history;

Wenn die Anzahl der Zeilen ausreichend groß ist, versuchen Sie, den folgenden Index hinzuzufügen, wenn er nicht vorhanden ist. Verwenden Sie den folgenden T-SQL-Code, um den Index hinzuzufügen:

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

Bereinigung häufiger als alle 30 Minuten durchführen

Bestimmte Tabellen können eine hohe Änderungsrate aufweisen, und möglicherweise stellen Sie fest, dass der AutoCleanup-Auftrag die Seitentabellen und syscommittab innerhalb des 30-Minuten-Intervalls nicht bereinigen kann. In diesem Fall können Sie einen manuellen Bereinigungsauftrag mit erhöhter Häufigkeit ausführen, um den Prozess zu erleichtern.

Für SQL Server und Azure SQL Managed Instance erstellen Sie einen Hintergrundauftrag mit sp_flush_CT_internal_table_on_demand mit einer kürzeren internen Zeitspanne als den standardmäßigen 30 Minuten. Für Azure SQL-Datenbank können Azure Logic Apps verwendet werden, um diese Aufträge zu planen.

Mit dem folgenden T-SQL-Code können Sie einen Auftrag erstellen, der Ihnen hilft, die Seitentabellen für die Änderungsverfolgung zu bereinigen:

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

Zugehöriger Inhalt

• Informationen zur Änderungsnachverfolgung (SQL Server)
• Änderungsnachverfolgungs-Funktionen (Transact-SQL)
• Änderungsnachverfolgung Gespeicherte Prozeduren (Transact-SQL)
• Änderungsnachverfolgung Tabellen (Transact-SQL)
• Anzeigen von Informationen zum Daten- und Protokollspeicherplatz einer Datenbank
• Behandeln von Problemen mit hoher CPU-Auslastung in SQL Server