Résoudre les problèmes de suivi des modifications et de nettoyage automatique

Article
07/12/2024

S’applique à : SQL Server Azure SQL Database Azure SQL Managed Instance

Cet article fournit des moyens de résoudre les problèmes courants observés dans le suivi des modifications et le nettoyage automatique.

Symptômes

En général, si le nettoyage automatique ne fonctionne pas comme prévu, vous pouvez observer un ou plusieurs des symptômes suivants :

Consommation élevée d'espace de stockage pour une ou plusieurs tables secondaires de suivi des modifications ou pour la table système syscommittab.
Les tables secondaires (tables internes dont le nom commence par le préfixe change_tracking, par exemple, change_tracking_12345) ou syscommittab, ou les deux, présentent un nombre important de lignes en dehors de la période de retention configurée.
La table dbo.MSChange_tracking_historycomporte des entrées avec des erreurs de nettoyage spécifiques.
Les performances CHANGETABLE se sont détérioré au fil du temps.
Le nettoyage automatique ou le nettoyage manuel signale une utilisation élevée de l'UC.

Débogage et prévention

Pour identifier la cause racine d'un problème de suivi des modifications et de nettoyage automatique, suivez les étapes suivantes pour déboguer et résoudre le problème.

État du nettoyage automatique

Vérifiez si le nettoyage automatique est en cours d’exécution. Pour le vérifier, interrogez la table d'historique des nettoyages dans la même base de données. Si le nettoyage est en cours d'exécution, la table contient des entrées indiquant les heures de début et de fin du nettoyage. Si le nettoyage n'a pas été effectué, la table est vide ou contient des entrées obsolètes. Si la table d'historique contient des entrées avec l'étiquette cleanup errors dans la colonne comments, le nettoyage échoue en raison d'erreurs de nettoyage au niveau de la table.

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

Le nettoyage automatique s’exécute périodiquement, avec un intervalle par défaut de 30 minutes. Si la table d'historique n'existe pas, le nettoyage automatique n'a probablement jamais été exécuté. Dans le cas contraire, vérifier les valeurs des colonnes start_time et end_time. Si les dernières entrées ne sont pas récentes, c'est-à-dire qu'elles datent de quelques heures ou de quelques jours, il se peut que le nettoyage automatique ne soit pas en cours d’exécution. Si c'est le cas, suivez les étapes suivantes pour résoudre le problème.

1. Le nettoyage est désactivé

Vérifiez si le nettoyage automatique est activé pour la base de données. Si ce n’est pas le cas, activez-le et attendez au moins 30 minutes avant de consulter la table d’historique pour de nouvelles entrées. Surveillez ensuite les progrès dans la table d’historique.

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

Une valeur is_auto_cleanup_on différente de zéro indique que le nettoyage automatique est activé. La valeur de la période de rétention contrôle la durée pendant laquelle les métadonnées de suivi des modifications sont conservées dans le système. La valeur par défaut de la période de rétention du suivi des modifications est de 2 jours.

Pour activer ou désactiver le suivi des modifications, consultez Activer et désactiver le suivi des modifications (SQL Server).

2. Le nettoyage est activé, mais n’est pas en cours d’exécution

Si le nettoyage automatique est activé, le fil de nettoyage automatique s'est probablement arrêté en raison d'erreurs inattendues. Actuellement, le redémarrage du fil de nettoyage automatique n'est pas possible. Vous devez initier un basculement vers un serveur secondaire (ou redémarrer le serveur en l'absence d'un serveur secondaire) et confirmer que le paramètre de nettoyage automatique est activé pour la base de données.

Le nettoyage automatique s'exécute mais ne progresse pas

Si une ou plusieurs tables secondaires consomment beaucoup d'espace de stockage ou contiennent un grand nombre d'enregistrements dont la durée de rétention est dépassée, suivez les étapes de cette section, qui décrivent les correctifs à apporter à une seule table secondaires. Les mêmes étapes peuvent être répétées pour d'autres tables si nécessaire.

1. Évaluer le backlog de nettoyage automatique

Identifier les tables secondaires qui ont un grand backlog d'enregistrements expirés en attente et qui doivent faire l'objet de mesures de prévention. Exécutez les requêtes suivantes pour identifier les tables secondaires présentant un grand nombre d'enregistrements expirés. Rappelez-vous de remplacer les valeurs dans les scripts de l'exemple comme indiqué.

Obtenir la version invalide du nettoyage :
```
SELECT * FROM sys.change_tracking_tables;
```
La valeur cleanup_version des lignes retournées représente la version de nettoyage non valide.

Exécutez la requête dynamique Transact-SQL (T-SQL) suivante, qui génère la requête permettant d'obtenir le nombre de lignes expirées des tables secondaires. Remplacer la valeur de <invalid_version> dans la requête par la valeur obtenue à l'étape précédente.

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;

Copiez le jeu de résultats de la requête précédente et supprimez le mot clé UNION de la dernière ligne. Si vous exécutez la requête T-SQL générée via une connexion d'administration dédiée (DAC), la requête donne le nombre de lignes expirées de toutes les tables secondaires. En fonction de la taille de la table sys.syscommittab et du nombre de tables secondaires, le traitement de cette requête peut prendre beaucoup de temps.

Important

Cette étape est nécessaire pour passer aux étapes de prévention. Si la requête précédente ne s'exécute pas, identifiez le nombre de lignes expirées pour les tables secondaires individuelles à l'aide des requêtes suivantes.

Effectuez les étapes de prévention suivantes pour les tables secondaires, dans l'ordre décroissant du nombre de lignes expirées, jusqu'à ce que le nombre de lignes expirées revienne à un niveau gérable pour que le nettoyage automatique puisse le rattraper.

Une fois que vous avez identifié les tables secondaires dont le nombre d'enregistrements expirés est élevé, recueillez des informations sur la latence des instructions de suppression des tables côté et sur le taux de suppression par seconde au cours des dernières heures. Ensuite, estimez le temps nécessaire pour nettoyer la table secondaires en tenant compte à la fois du nombre de lignes obsolètes et de la latence de suppression.

Utilisez l'extrait de code T-SQL suivant en remplaçant les modèles de paramètres par les valeurs appropriées.

Interroger le taux de nettoyage par 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;

Vous pouvez également utiliser la granularité minute ou heure pour la fonction DATEDIFF.

Trouvez le nombre de lignes obsolètes dans la table secondaire. Cette requête vous aide à trouver le nombre de lignes en attente de nettoyage.

Les valeurs <internal_table_name> et <cleanup_version> de la table utilisateur figurent dans la production retournée dans la section précédente. À l'aide de ces informations, exécutez le code T-SQL suivant via une connexion d'administration dédiée (DAC) :
```
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>
);
```
Cette requête peut prendre du temps. Dans les cas où la requête n'aboutit pas, calculez les lignes obsolètes en trouvant la différence entre les lignes totales et les lignes actives, c'est-à-dire les lignes qui doivent être nettoyées.

Trouvez le nombre total de lignes dans la table secondaires en exécutant la requête suivante :

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

Trouvez le nombre de lignes actives dans la table secondaires en exécutant la requête suivante :
```
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>);
```
Vous pouvez calculer le temps estimé pour nettoyer la table en utilisant le taux de nettoyage et le nombre de lignes obsolètes. Tenez compte de la formule suivante :

Temps de nettoyage en minutes = (nombre de lignes obsolètes) / (taux de nettoyage en minutes)

Si le temps nécessaire au nettoyage de la table est acceptable, surveillez la progression et laissez le nettoyage automatique continuer son travail. Si ce n’est pas le cas, passez aux étapes suivantes pour descendre dans la hiérarchie.

2. Vérifier les conflits de verrou de table

Déterminez si le nettoyage ne progresse pas à cause de conflits d’escalade de verrouillage de table, qui privent constamment le nettoyage d’acquérir des verrous sur la table secondaire pour supprimer les lignes.

Pour confirmer un conflit de verrou, exécutez le code T-SQL suivant. Cette requête récupère des enregistrements pour la table problématique afin de déterminer s’il y a plusieurs entrées indiquant des conflits de verrouillage. Quelques conflits sporadiques se répartissent sur une période ne doivent pas se qualifier pour les étapes d’atténuation en cours. Les conflits doivent être récurrents.

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

Si la table d'historique contient plusieurs entrées dans les colonnes comments avec la valeur Cleanup error: Lock request time out period exceeded, cela indique clairement que plusieurs tentatives de nettoyage ont échoué en raison de conflits de verrou ou de dépassement de délai d'attente de verrou successifs. Les recours suivants peuvent également vous être utile :

Désactiver et réactiver le suivi des modifications sur la table problématique. Cela entraîne la suppression de toutes les métadonnées de suivi maintenues pour la table. Les données de la table restent intactes. Il s’agit du remède le plus rapide.
Si l’option précédente n’est pas possible, procédez alors à un nettoyage manuel sur la table en activant l’indicateur de trace 8284, comme suit :
```
DBCC TRACEON (8284, -1);
GO
EXEC [sys].[sp_flush_CT_internal_table_on_demand] @TableToClean = '<table_name>';
```

3. Vérifier d’autres causes

Une autre cause possible du décalage de nettoyage est la lenteur des instructions de suppresion. Pour le savoir, vérifiez la valeur de hardened_cleanup_version. Cette valeur peut être récupérée par le biais d'une connexion d'administration dédiée (DAC) à la base de données considérée.

Trouvez la version de nettoyage renforcée en exécutant la requête suivante :

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

Trouvez la version de nettoyage en exécutant la requête suivante :

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

Si les valeurs hardened_cleanup_version et cleanup_version sont égales, ignorez cette section et passez à la section suivante.

Si les deux valeurs sont différentes, cela signifie qu'une ou plusieurs tables secondaires ont rencontré des erreurs. La prévention la plus rapide consiste à désactiver et à activer le suivi des modifications sur la table qui pose problème. Cela entraîne la suppression de toutes les métadonnées de suivi maintenues pour la table. Les données contenues dans la table restent intactes.

Si l'option précédente n'est pas possible, exécutez un nettoyage manuel de la table.

Résoudre les problèmes liés à syscommittab

Cette section décrit les étapes à suivre pour déboguer et résoudre les problèmes liés à la table système syscommittab, si elle utilise beaucoup d'espace de stockage ou si elle contient un grand backlog de lignes obsolètes.

Le nettoyage de la table système syscommittab dépend du nettoyage de la table secondaires. syscommittab ne peut être supprimé définitivement qu'une fois que toutes les tables secondaires ont été nettoyées. Vérifiez que toutes les étapes de la section Nettoyage automatique en cours d'exécution mais sans progression ont été effectuées.

Pour invoquer explicitement le nettoyage syscommittab, utilisez la procédure stockée sys.sp_flush_commit_table_on_demand.

Remarque

La procédure stockée sys.sp_flush_commit_table_on_demand peut prendre du temps si elle supprime un grand backlog de lignes.

Comme indiqué dans l’exemple de section de l’article sys.sp_flush_commit_table_on_demand , cette procédure stockée retourne la valeur de safe_cleanup_version() et le nombre de lignes supprimées. Si la valeur retournée semble être 0, et si l'isolation des instantanés est activée, le nettoyage pourrait ne rien supprimer de syscommittab.

Si la période de rétention est supérieure à un jour, la procédure stockée sys.sp_flush_commit_table_on_demand peut être réexécutée en toute sécurité après avoir activé globalement l'indicateur de trace 8239. L'utilisation de cet indicateur de trace lorsque l'isolation de l'instantané est désactivée est toujours sans risque, mais dans certains cas, elle peut ne pas être nécessaire.

Utilisation élevée de l’UC pendant le nettoyage

Le problème décrit dans cette section peut être observé sur des versions plus anciennes de SQL Server. Si une base de données contient un grand nombre de tables de suivi des modifications et que le nettoyage automatique ou manuel entraîne une utilisation élevée de l'UC. Ce problème peut également être dû à la table d'historique, qui a été brièvement mentionnée dans les sections précédentes.

Utilisez le code T-SQL suivant pour vérifier le nombre de lignes dans la table d'historique :

SELECT COUNT(*) from dbo.MSChange_tracking_history;

Si le nombre de lignes est suffisamment important, essayez d'ajouter l'index suivant s'il est absent. Utilisez le code T-SQL suivant pour ajouter l’indexe :

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

Exécuter le nettoyage plus fréquemment que toutes les 30 minutes

Des tables spécifiques peuvent subir un taux de changement élevé, et vous pouvez constater que la tâche de nettoyage automatique ne peut pas nettoyer les tables secondaires et syscommittab dans l'intervalle de 30 minutes. Dans ce cas, vous pouvez exécuter une tâche de nettoyage manuel à une fréquence plus élevée pour faciliter le processus.

Pour SQL Server et Azure SQL Managed Instance, créez une tâche d'arrière-plan à l'aide de sp_flush_CT_internal_table_on_demand avec une durée interne plus courte que les 30 minutes par défaut. Pour la base de données Azure SQL, Azure Logic Apps peut être utilisé pour planifier ces tâches.

Le code T-SQL suivant peut être utilisé pour créer une tâche permettant de nettoyer les tables secondaires pour le suivi des modifications :

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

Partage via

Résoudre les problèmes de suivi des modifications et de nettoyage automatique

Symptômes

Débogage et prévention

État du nettoyage automatique

1. Le nettoyage est désactivé

2. Le nettoyage est activé, mais n’est pas en cours d’exécution

Le nettoyage automatique s'exécute mais ne progresse pas

1. Évaluer le backlog de nettoyage automatique

2. Vérifier les conflits de verrou de table

3. Vérifier d’autres causes

Résoudre les problèmes liés à syscommittab

Utilisation élevée de l’UC pendant le nettoyage

Exécuter le nettoyage plus fréquemment que toutes les 30 minutes

Commentaires

Ressources supplémentaires

Partage via

Résoudre les problèmes de suivi des modifications et de nettoyage automatique

Symptômes

Débogage et prévention

État du nettoyage automatique

1. Le nettoyage est désactivé

2. Le nettoyage est activé, mais n’est pas en cours d’exécution

Le nettoyage automatique s'exécute mais ne progresse pas

1. Évaluer le backlog de nettoyage automatique

2. Vérifier les conflits de verrou de table

3. Vérifier d’autres causes

Résoudre les problèmes liés à syscommittab

Utilisation élevée de l’UC pendant le nettoyage

Exécuter le nettoyage plus fréquemment que toutes les 30 minutes

Contenu connexe

Commentaires

Ressources supplémentaires