Fragmentação do Transaction Log - Parte II

No post anterior, abordei sobre o Transaction Log, como ocorre sua fragmentação e como podemos identificá-la.

Como prometido, neste post vamos verificar como resolver a fragmentação interna do Log.

Mãos à massa!

Para exemplificar, vou criar os Databases LargeVLF_Full, LargeVLF_Simple e SmallVLF com as seguintes configurações para os arquivos de Log:

• LargeVLF_Full com Tamanho de 1MB e FileGrowth de 10% no recovery model FULL.
• LargeVLF_Simple com Tamanho de 1MB e FileGrowth de 10% no recovery model SIMPLE.
• SmallVLF com Tamanho de 8000MB e Filegrowth de 4000MB no recovery model FULL.

Após criar os Databases, vamos analisar como estão os VLFs dos arquivos de log através do comando DBCC LOGINFO.

• Database LargeVLF_Full com 4 VLFs de "256Kb".
• LargeVLF_Full com 4 VLFs de "256Kb".
• SmallVLF com 16 VLFs de 500MB.

Agora, vamos executar o script abaixo em todos os Databases.


Após a criação da tabela e execução dos inserts, vamos analisar como estão os arquivos de log de cada Database.

O Database LargeVLF_Full aumentou de 4 VLFs para 259 VLFs.
Observe que no modelo de recuperação Full, o status de todos os VLFs estão igual a 2 (Em uso).

Dica:
Você pode verificar o que está retendo o arquivo de log (status = 2) e impedindo a reutilização dos vlfs através das colunas log_reuse_wait e log_reuse_wait_desc na sys.databases.
Neste caso, é o Backup de Log!
As possíveis causas de retenção podem ser encontradas neste link.

O Database LargeVLF_Simple aumentou de 4 VLFs para 91 VLFs.
Pela característica do modelo de recuperação Simples, temos os status 0 (Livre) e 2 (Em uso).
No modelo Simple o log é automaticamente truncado, mas isso não o impede de ficar fragmentado. Cuidado!

Observe que o FileSize dos VLFs nos Databases LargeVLF_Full e LargeVLF_Simple são diferentes a cada crescimento. Isto é motivado pela configuração do FileGrowth em percentual.

O Database SmallVLF continua com os mesmos 16 VLFs.
Apenas o segundo e terceiro arquivo passaram a ser utilizados (Status = 2).

Podemos afirmar que os Databases LargeVLF_Full e LargeVLF_Simple estão fragmentados!
(Utilizo como "referência" o valor máximo de 50 para quantidade de VLFs. Fonte: Kimberly Tripp)

E em relação aos impactos das operações de Inserts e Updates mencionados no primeiro post por você Leandro?!
O arquivo de log não se beneficia do recurso Intant File Initialization. Quando é necessário o seu crescimento, seus Updates e Inserts ficarão bloqueados até a conclusão do crescimento do arquivo de log.

Vamos testar!
Para este teste, vamos realizar um update em todos os registros da Tabela nos Databases LargeVLF_Full e SmallVLF, medindo o tempo de execução.

A diferença entre os Updates é de 1896ms...

Para verificar o que motivou esta diferença, vamos ao Defult trace Log (Se não estiver desabilitado!).

Só para realizar o Auto Grow do arquivo de Log do Database LargeVLF_Full foram gastos 1486ms.
Dica: Evite crescimentos de arquivo do SQL Server durante o dia!

Bem, espero que tenha ficado claro!

Voltando para a resolução do problema de fragmentação...

Para "arrumar" nosso arquivo de log, devemos realizar os seguintes procedimentos:

1. Backup de Log.
2. Shirink do arquivo de Log.
3. Alteração do FileSize e AutoGrowth para um tamanho apropriado.

Para facilitar minha vida, criei o script "Analise de Fragmentação" que ao ser executado realiza as seguintes atividades:

1. Verifica e exibe os Databases que contenham mais de 50 VLFs.
2. Exibe o tamanho e o percentual de utilização dos arquivos de log através do comando DBCC SQLPERF(LOGSPACE).
3. Monta através de "Print", os comandos de ajustes para os Databases que apresentam mais de 50 VLFs.
• Checkpoint.
• Backup de Log se o modelo de recuperação não for Simple.
• Shirink (Recuperando o nome lógico do arquivo)
• Comando para alterar o FileSize e FileGrowth (Recuperando o nome lógico do arquivo)

Abaixo o retorno da query.

Após analisar o ambiente e copiar os comandos gerados para uma nova janela de query, informe:

1. FileSize e FileGrowth para os arquivos de Log identificados no script.
2. Execute o script de ajustes em um momento propício, ou seja, em uma janela de manutenção.
3. Execute novamente o script "Análise de fragmentação" para verificar se seu ambiente está normalizado.

Fazer o download do script aqui

Informação adicional
Ter poucos VLFs e de tamanho "considerável" também é prejudicial ao seu ambiente, pois irá impactar na liberação de espaço do arquivo de Log. VLFs de no máximo 400/500MB é o indicado.

Melhores práticas:

• Monitore seu ambiente.
• Escolha pelo modelo recuperação ideal para cada ambiente e necessidades de negócio.
• Ajuste o frequência de Backup de Log para controlar o tamanho do seu arquivo de Log.
• Não faça "BACKUP LOG ... WITH TRUNCATEONLY pois ele quebrará a sequência do Backup de Log.
• Utilize transações curtas para que não retenha os VLFs por muito tempo.
• Ajuste apropriadamente o FileSize e FileGrowth do seu arquivo de Log.
• Monitore seu ambiente.

E aí pessoal, como está o seu ambiente...

Espero que o post ajude.
Até o próximo!