Solucionar problemas de limpeza automática de controle de alterações

Artigo
2025-03-05

Aplica-se a:SQL Server Banco de Dados SQL do Azure Instância Gerenciada SQL do Azure

Este artigo fornece maneiras de solucionar problemas comuns observados na limpeza automática de acompanhamento de alterações.

Sintomas

Geralmente, se a limpeza automática não estiver funcionando conforme o esperado, você poderá ver um ou mais dos seguintes sintomas:

Alto consumo de armazenamento por uma ou mais tabelas auxiliares de rastreamento de alterações ou pela tabela do sistema syscommittab.
As tabelas laterais (tabelas internas cujo nome começa com o prefixo change_tracking, por exemplo, change_tracking_12345) ou syscommittab ou ambas, mostram um número significativo de linhas que estão fora do período de retenção configurado.
A tabela dbo.MSChange_tracking_history tem entradas com erros de limpeza específicos.
O desempenho de CHANGETABLE tem-se degradado ao longo do tempo.
A limpeza automática ou a limpeza manual relata o alto uso da CPU.

Depuração e mitigação

Para identificar a causa raiz de um problema com a limpeza automática do controle de alterações, use as etapas a seguir para depurar e mitigar o problema.

Estado da limpeza automática

Verifique se a limpeza automática foi executada. Para verificar isso, consulte a tabela de histórico de limpeza no mesmo banco de dados. Se a limpeza tiver sido executada, a tabela tem entradas com as horas de início e fim da limpeza. Se a limpeza não estiver em execução, a tabela está vazia ou tem entradas obsoletas. Se a tabela de histórico tiver entradas com a tag cleanup errors na coluna comments, a limpeza está falhando devido a erros de limpeza no nível da tabela.

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

A limpeza automática é executada periodicamente com um intervalo padrão de 30 minutos. Se a tabela de histórico não existir, muito provavelmente, a limpeza automática nunca foi executada. Caso contrário, verifique os valores da coluna start_time e end_time. Se as entradas mais recentes não foram atualizadas recentemente, ou seja, têm horas ou dias, a limpeza automática pode não estar a ser executada. Se for esse o caso, use as etapas a seguir para solucionar problemas.

1. A limpeza está desativada

Verifique se a limpeza automática está ativada para o banco de dados. Se não estiver, ligue-o e aguarde pelo menos 30 minutos antes de olhar para a tabela de histórico para novas entradas. Monitore o progresso na tabela de histórico em seguida.

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

Um valor diferente de zero no is_auto_cleanup_on indica que a limpeza automática foi ativada. O valor do período de retenção controla a duração durante a qual os metadados de controlo de alterações são retidos no sistema. O valor padrão para o período de retenção de controle de alterações é 2 dias.

Para habilitar ou desabilitar o controle de alterações, consulte Habilitar e desabilitar o controle de alterações (SQL Server).

2. A limpeza está ativada, mas não está em execução

Se a limpeza automática estiver ativada, o thread de limpeza automática provavelmente parou devido a erros inesperados. Atualmente, reiniciar o thread de limpeza automática não é viável. Você deve iniciar um failover para um servidor secundário (ou reiniciar o servidor na ausência de um secundário) e confirmar se a configuração de limpeza automática está habilitada para o banco de dados.

A limpeza automática é executada, mas não está progredindo

Se uma ou mais tabelas laterais mostrarem um consumo de armazenamento significativo ou contiverem um grande número de registros além da retenção configurada, siga as etapas nesta seção, que descrevem as soluções para uma única tabela lateral. Os mesmos passos podem ser repetidos para mais tabelas, se necessário.

1. Avalie a lista de pendências da limpeza automática

Identifique tabelas auxiliares que tenham uma grande acumulação de registros expirados, que necessitam de mitigação. Corra as consultas a seguir para identificar as tabelas auxiliares com um grande número de registos expirados. Lembre-se de substituir os valores nos scripts de exemplo, conforme mostrado.

Obtenha a versão de limpeza inválida:
```
SELECT * FROM sys.change_tracking_tables;
```
O valor cleanup_version das linhas retornadas representa a versão de limpeza inválida.

Execute a seguinte consulta dinâmica Transact-SQL (T-SQL), que gera a consulta para obter a contagem de linhas expiradas de tabelas laterais. Substitua o valor de <invalid_version> na consulta pelo valor obtido na etapa anterior.

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;

Copie o conjunto de resultados da consulta anterior e remova a palavra-chave UNION da última linha. Se você executar a consulta T-SQL gerada por meio de uma conexão de administrador dedicada (DAC), a consulta fornecerá as contagens de linhas expiradas de todas as tabelas laterais. Dependendo do tamanho da tabela sys.syscommittab e do número de tabelas laterais, essa consulta pode levar muito tempo para ser concluída.

Importante

Esta etapa é necessária para avançar com as etapas de mitigação. Se a consulta anterior não for executada, identifique as contagens de linhas expiradas para as tabelas laterais individuais usando as consultas fornecidas a seguir.

Execute as seguintes etapas de mitigação para as tabelas laterais, na ordem decrescente das contagens de linhas expiradas, até que as contagens de linhas expiradas cheguem a um estado controlável para que a limpeza automática possa acompanhar.

Depois de identificar as tabelas laterais com grandes contagens de registros expirados, reúna informações sobre a latência das instruções de exclusão da tabela lateral e a taxa de exclusão por segundo nas últimas horas. Em seguida, estime o tempo necessário para limpar a tabela lateral considerando a contagem de linhas obsoletas e a latência de exclusão.

Use o seguinte trecho de código T-SQL substituindo modelos de parâmetro por valores apropriados.

Consulte a taxa de limpeza por segundo:

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;

Você também pode usar granularidade de minuto ou hora para a função DATEDIFF.

Encontre a contagem de linhas obsoletas na tabela lateral. Esta consulta ajuda-o a encontrar o número de linhas pendentes a limpar.

Os valores <internal_table_name> e <cleanup_version> para a tabela do usuário estão na saída retornada na seção anterior. Usando essas informações, execute o seguinte código T-SQL por meio de uma conexão de administrador dedicada (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>
);
```
Essa consulta pode levar algum tempo para ser concluída. Nos casos em que a consulta atinge o tempo limite, calcule as linhas obsoletas localizando a diferença entre o total de linhas e as linhas ativas, ou seja, as linhas a serem limpas.

Localize o número total de linhas na tabela lateral executando a seguinte consulta:

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

Localize o número de linhas ativas na tabela lateral executando a seguinte consulta:
```
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>);
```
Você pode calcular o tempo estimado para limpar a tabela usando a taxa de limpeza e a contagem de linhas obsoletas. Considere a seguinte fórmula:

Tempo de limpeza em minutos = (contagem de linhas obsoletas) / (taxa de limpeza em minutos)

Se o tempo para concluir a limpeza da tabela for aceitável, monitore o progresso e permita que a limpeza automática continue o seu trabalho. Caso contrário, prossiga com as próximas etapas para aprofundar ainda mais.

2. Verifique os conflitos de bloqueio da tabela

Determine se a limpeza não está progredindo devido a conflitos de escalonamento de bloqueio de tabela, que impedem consistentemente a limpeza de adquirir bloqueios na tabela auxiliar para excluir linhas.

Para confirmar um conflito de bloqueio, execute o seguinte código T-SQL. Essa consulta busca registros para a tabela problemática para determinar se há várias entradas indicando conflitos de bloqueio. Alguns conflitos esporádicos espalhados por um período não devem qualificar-se para as etapas de mitigação processual. Os conflitos devem ser recorrentes.

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

Se a tabela de histórico tiver várias entradas nas colunas comments com o valor Cleanup error: Lock request time out period exceeded, é uma indicação clara de que falhas nas tentativas de limpeza foram causadas por conflitos de bloqueio ou sucessivos tempos limite de bloqueio. Considere os seguintes remédios:

Desative e habilite o controle de alterações na tabela problemática. Isso faz com que todos os metadados de acompanhamento mantidos para a tabela sejam eliminados. Os dados da tabela permanecem intactos. Este é o remédio mais rápido.
Se a opção anterior não for possível, prossiga para efetuar a limpeza manual na tabela, ativando o sinalizador de rastreamento 8284 da seguinte maneira:
```
DBCC TRACEON (8284, -1);
GO
EXEC [sys].[sp_flush_CT_internal_table_on_demand] @TableToClean = '<table_name>';
```

3. Verifique outras causas

Outra possível causa do atraso na limpeza é a lentidão das instruções DELETE. Para determinar se sim, verifique o valor de hardened_cleanup_version. Esse valor pode ser recuperado por meio de uma conexão de administrador dedicada (DAC) ao banco de dados em questão.

Encontre a versão de limpeza reforçada executando a seguinte consulta:

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

Encontre a versão de limpeza executando a seguinte consulta:

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

Se os valores hardened_cleanup_version e cleanup_version forem iguais, ignore esta seção e prossiga para a próxima seção.

Se ambos os valores forem diferentes, isso significa que uma ou mais tabelas laterais encontraram erros. A mitigação mais rápida é desativar & e habilitar o controle de alterações na tabela problemática. Isso faz com que todos os metadados de rastreamento mantidos para a tabela sejam eliminados. Os dados na tabela permanecem intactos.

Se a opção anterior não for possível, execute a limpeza manual na mesa.

Solucionar problemas de syscommittab

Esta seção aborda as etapas para depurar e mitigar problemas com a tabela do sistema syscommittab, se ela usa muito espaço de armazenamento ou se tem uma grande lista de pendências de linhas obsoletas.

A limpeza da mesa do sistema syscommittab depende da limpeza da mesa lateral. Só depois de limpas todas as mesas laterais, syscommittab pode ser expurgado. Certifique-se de que todas as etapas na seção Limpeza automática são realizadas, mas não estão a progredir.

Para invocar explicitamente a limpeza de syscommittab, use o procedimento armazenado sys.sp_flush_commit_table_on_demand.

Observação

O procedimento armazenado sys.sp_flush_commit_table_on_demand pode demorar se estiver a apagar um grande volume de linhas em atraso.

Conforme mostrado na seção de exemplo do artigo sys.sp_flush_commit_table_on_demand, esse procedimento armazenado retorna o valor de safe_cleanup_version()e o número de linhas excluídas. Se o valor retornado for 0e se o isolamento de instantâneo estiver ativado, a limpeza pode não excluir nada do syscommittab.

Se o período de retenção for maior que um dia, deve ser seguro executar novamente o procedimento armazenado sys.sp_flush_commit_table_on_demand depois de habilitar o sinalizador de rastreamento 8239 globalmente. Usar esse sinalizador de rastreamento quando o isolamento de instantâneo está desativado é sempre seguro, mas, em alguns casos, pode não ser necessário.

Alta utilização da CPU durante a limpeza

O problema descrito nesta seção pode ser visto em versões mais antigas do SQL Server. Se houver um grande número de tabelas com rastreamento de alterações numa base de dados, e a limpeza automática ou manual causar uma utilização elevada do processador. Esse problema também pode ser causado devido à tabela de histórico, que foi mencionada brevemente nas seções anteriores.

Use o seguinte código T-SQL para verificar o número de linhas na tabela de histórico:

SELECT COUNT(*) from dbo.MSChange_tracking_history;

Se o número de linhas for suficientemente grande, tente adicionar o seguinte índice se ele estiver ausente. Use o seguinte código T-SQL para adicionar o índice:

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

Execute a limpeza com mais frequência do que 30 minutos

Tabelas específicas podem experimentar uma alta taxa de alterações, e pode-se achar que o trabalho de limpeza automática não consegue limpar as tabelas laterais e syscommittab dentro do intervalo de 30 minutos. Se isso ocorrer, você pode executar um trabalho de limpeza manual com maior frequência para facilitar o processo.

Para o SQL Server e a Instância Gerenciada SQL do Azure, criar um de trabalho em segundo plano usando sp_flush_CT_internal_table_on_demand com um interno mais curto do que os 30 minutos padrão. Para o Banco de Dados SQL do Azure, de Aplicativos Lógicos do Azure pode ser usado para agendar esses trabalhos.

O seguinte código T-SQL pode ser usado para criar um trabalho para ajudar a limpar as tabelas laterais para controle de alterações:

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

Partilhar via