Arquivos de log virtual (VLFs) são um conceito-chave no gerenciamento de log de transações do SQL Server. Eles são partições lógicas de arquivos de log físicos usados para registrar transações de banco de dados. O Mecanismo de banco de dados do SQL Server cria e gerencia VLFs dinamicamente em arquivos de log físicos.

O tamanho e o número de VLFs são determinados pelo SQL Server Database Engine e são baseados no tamanho e na frequência do crescimento do arquivo de log. No SQL Server 2014 e versões posteriores, se o próximo crescimento for menor que 1/8 do tamanho físico do log atual, crie 1 VLF que cubra o tamanho do crescimento. Se o próximo crescimento for maior que 1/8 do tamanho do log atual, o método anterior a 2014 é usado, onde são criados 4 VLFs que cobrem o tamanho do crescimento se o crescimento for menor que 64 MB.

No Banco de Dados SQL do Azure e no SQL Server 2022, o método é um pouco diferente. Se o crescimento for menor ou igual a 64 MB, apenas 1 VLF será criado para cobrir o tamanho do crescimento. Se o crescimento for de 64 MB até 1 GB, 8 VLFs serão criados para cobrir o tamanho do crescimento e, se o crescimento for maior que 1 GB, 16 VLFs serão criados para cobrir o tamanho do crescimento.

Ter muitos VLFs pode retardar a inicialização do banco de dados, backup de log e operações de restauração, enquanto ter muito poucos pode causar problemas com backups de log de transações, replicação e recuperação. Portanto, é essencial estimar adequadamente o tamanho necessário e as configurações de crescimento automático de um log de transação para garantir a distribuição ideal dos VLFs.

É recomendável atribuir aos arquivos de log um valor de tamanho próximo ao tamanho final necessário, usando os incrementos necessários para obter a distribuição VLF ideal e ter um valor growth_increment relativamente grande. Isso ajudará a garantir a criação e o gerenciamento eficientes dos VLFs.

Como resolver esse problema em bancos de dados com um grande número de VLFs?

1. Shrink o arquivo de Transaction Log
2. Cresça o arquivo de Log em apenas um passo usando o seguinte script:

ALTER DATABASE <database name> MODIFY FILE (NAME='Logical file name of transaction log', SIZE = <required size>);

Depois de definir o novo layout do arquivo de log de transações com menos VLFs, revise e faça as alterações necessárias nas configurações de crescimento automático do log de transações. Isso garante que o arquivo de log evite encontrar o mesmo problema no futuro.

Antes de executar qualquer uma dessas operações, certifique-se de ter um backup restaurável válido caso encontre problemas posteriormente.