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 SmallVLFcom 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.

  • LargeVLF_Full com 4 VLFs de “256Kb”.
  • LargeVLF_Simple 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 dos scripts 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!

Explore posts in the same categories: Transaction Log

Tags:

You can comment below, or link to this permanent URL from your own site.

2 Comentários em “Fragmentação do Transaction Log – Parte II”


  1. […] Fragmentação do Transaction Log – Parte II […]


Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s


%d blogueiros gostam disto: