Truncar uma tabela com 17 bilhões de linhas em um AG

Extensões de registro

A quantidade de dados de log gerados (e, portanto, enviados pela rede para seus outros nós AG) depende do tamanho das linhas em sua tabela de 17 bilhões de linhas. definitivamenteTRUNCATE será uma quantia pequena comparada a fazer um . Mas ainda pode ser significativo, dependendo de sua infraestrutura e expectativas.DELETE

Considere a dbo.Votestabela no banco de dados de amostra do Stack Overflow:

Cada linha tem 28 bytes. Uma página no SQL Server tem 8 KB (8.192 bytes), portanto você pode ajustar cerca de 292 linhas em uma página. Isso não é exatamente correto, pois há sobrecarga para páginas e linhas, mas é uma aproximação decente para este exemplo.

Isso significa que são necessárias cerca de 58.219.178 páginas para conter todas as 17 bilhões de linhas. Ao fazer um DROPou TRUNCATE, uma tarefa em segundo plano desaloca extensões (grupos de 8 páginas). Cada uma dessas desalocações é registrada. Isso significa que cerca de 7.277.397 registros de log serão criados truncando esta tabela.

Testedbo.Votes

Eu tentei isso na minha cópia desse banco de dados de exemplo, depois de definir o modelo de recuperação como completo e fazer backups completos e de log para inicializar a cadeia de backup. A dbo.Votestabela tem 10.146.802 linhas. Com base em nossos cálculos anteriores, isso deve ser em torno de 34.749 páginas, ou 4.343 extensões.

Na realidade, essa tabela usa 47.721 páginas alocadas a ela (por causa da sobrecarga mencionada anteriormente), o que equivale a 5.965.125 extensões.

Agora vou TRUNCATEa mesa:

TRUNCATE TABLE dbo.Votes;

Isso é concluído instantaneamente, mas acabei com 17.605 registros de log. Parece que há realmente 3 registros de log por extensão (2 para atualizar as páginas IAM e GAM, um para atualizar a página PFS para desalocar a página de dados).

Esses registros de log totalizaram apenas até ~1,28 MB de uso do arquivo de log. Mas sua tabela real tem 1.600 vezes mais linhas que essa, e os tamanhos das linhas podem ser maiores. Isso pode significar mais de 1 GB de dados de log gerados e enviados para cada réplica pela rede.

A quantidade de dados aumenta ainda mais se você tiver índices não clusterizados nesta tabela, que são registrados da mesma maneira.

O tamanho da linha faz uma grande diferença

Outro estudo de caso poderia ser a dbo.Commentsmesa. Tem 3.907.472 linhas, mas cada linha tem 1.424 bytes (máximo - a Textcoluna é nvarchar(700)).

Apesar de ter um número significativamente menor de linhas que dbo.Votes, essa tabela possui 176.722 páginas alocadas a ela. TRUNCATEing dbo.Commentsresulta em 63.792 registros de log e 4,86 ​​MB de dados de log.

Se o tamanho real da sua linha for maior neste estádio, isso poderá ser superior a 7,5 GB de dados de log.

O que fazer

Talvez sua infraestrutura e arquivos de log possam lidar com vários GB de dados facilmente - se você tiver uma tabela de 17 linhas B, parece que eles deveriam! Mas achei que valeria a pena mencionar que a quantidade de tráfego não é necessariamente insignificante, pois as respostas existentes não trouxeram isso à tona.

Teste em um ambiente sem produção, se puder. Meça o uso do arquivo de log antes e depois e certifique-se de que sua infraestrutura de produção esteja configurada para lidar com essa quantidade de dados. Certifique-se de que o arquivo de log foi pré-dimensionado para lidar com esse truncado - ter um crescimento automático no meio desta operação tornará as coisas muito lentas e causará bloqueio.

Se você não puder testar, faça o possível para estimar qual será o impacto. Use uma consulta como esta para obter o número de páginas na tabela. Em seguida, divida isso por 8 (para obter o número de extensões) e multiplique por 3 para obter o número aproximado de registros de log.

Meus testes tiveram tamanhos médios de registro de log de cerca de 70 bytes, mas não sei se isso é típico. Mas você pode tentar multiplicar os registros de log aproximados por 70 para obter o número de bytes de log em potencial produzidos pelo truncado.

Com o AG na mistura, você também pode brincar com a compressão do fluxo de log . Eu não usei esses sinalizadores de rastreamento, então estou apenas mencionando que é outro botão que você pode ajustar.

Se você achar que a TRUNCATEabordagem é muito imprevisível ou demais para seus sistemas, você sempre pode usar um normal DELETEem lotes. Isso usa mais log no total, mas você pode distribuí-lo por qualquer período de tempo que desejar. No entanto, certifique-se de tomar cuidado ao criar scripts em lotes se você seguir esse caminho.

Truncate tablecomando trunca linhas instantaneamente e não grava linhas excluídas no arquivo de log de transações

Geralmente Truncate tableé executado instantaneamente e não há tráfego de rede perceptível entre as réplicas no AG, não haverá backups de log perceptíveis como consequência etc., em comparação com quando você faz Delete from. No entanto, pode haver tráfego perceptível e backup de log perceptível , porque 17 bilhões de linhas é muito .

PS Considere fazer backup do banco de dados e salve o backup no arquivo antes de truncar, para que você possa restaurar a tabela de 17 bilhões mais tarde, se necessário