Eu tenho uma tabela com 2,6B linhas e preciso alterar uma coluna de Char(1) para Varchar(64). a tabela tem mais de 300 GB com alguns índices. A coluna em nullable, então a transação que estou executando é:
ALTER TABLE XXXX ALTER COLUMN YYYYY varchar(64) NULL
Eu entendo que esta é uma operação registrada, então eu pré-dimensionei o log de transações para 300 GB pensando que seria suficiente especialmente com backups de log a cada 5 minutos para permitir a reutilização do espaço de log. bem, o log de transações cresceu para 812 GB antes de eu ter que cancelar a transação devido a problemas de espaço em disco.
Também experimentei um aumento muito grande no tamanho do arquivo de dados USADO, que não sei por que isso aconteceria. O tamanho do arquivo de dados usado chegou a cerca de 200 GB (transações muito mínimas são feitas neste banco de dados, então eu sei que o aumento é desse comando alter table).
Eu tenho algumas questões:
- Por que os arquivos de dados experimentariam um aumento ao mudar apenas de char(1) para varchar(64)? Fiquei com a impressão de que isso não deve alterar a quantidade de dados armazenados, a menos que esse espaço seja realmente necessário desde seu varchar. todos os valores existentes eram nulos ou 1 byte, pois char(1) é o tipo de dados existente, nenhum dado existente precisa ser expandido.
- Enquanto tentava descobrir como fazer isso melhor, me deparei com esta resposta de Aaron Bertrand e parece que funcionaria no meu caso também. essa seria uma maneira melhor de realizar essa tarefa?
- esse banco de dados está em um AG de confirmação síncrona de 2 nós e notei que o redo_queue_size chegou a mais de 130 GB, o que significa que o primário estava enviando dados de log em um ritmo mais rápido do que o secundário poderia aplicá-los. Isso significa que o log no primário não pôde ser truncado quando os backups de log foram concluídos. isso é um comportamento normal para algo assim? a instrução alter column é processada como uma grande transação? se assim for, isso explicaria por que o log continuou crescendo.
armazenamento mínimo para um VARCHAR é de 4 bytes, eu acho, então você alterou o tamanho da coluna de 1 byte para pelo menos 4 bytes, causando (como você observou) grandes quantidades de movimento de dados à medida que a coluna cresce e embaralha outros coisas ao seu redor.
Eu recomendaria a abordagem de Aaron como um método mais rápido para alcançar o mesmo objetivo. A desvantagem disso é que a ordem das colunas não é preservada... mas como devemos sempre (lol) usar colunas nomeadas para nossas seleções e inserções, a ordem de armazenamento não deve importar. (pode ser necessário atualizar os frameworks...)
Você está certo. DDL é uma transação registrada e, portanto, o comportamento observado em seu arquivo de log/saúde do AG é esperado.