«

»

maio 30

Arquitetura do Transaction Log

Muitas bases são criadas por desenvolvedores que não conhecem a fundo o SQL Server. Esta prática pode ocasionar uma série de problemas, dentre eles, um Transaction Log com baixa performance e que não pode ser compactado para liberar espaço em disco.

Vamos entender como funciona o Transaction Log e quais as melhores práticas para termos melhor performance, eliminando alguns problemas que podem até parar a base como falta de espaço em disco.

Uma base pode ter um ou mais arquivos de TLog, que possuem uma estrutura interna dividida em Virtual Log Files (VLF):

Quando a base é criada, o TLog é utilizado no início do arquivo e enquanto não existe o primeiro backup full, o recovery mode utilizado é o SIMPLE mesmo que esteja configurado como FULL. Neste exemplo, possui 6 VLFs e todo o seu conteúdo está concentrado no VL1 e VL2 enquanto os demais estão vazios, portanto, marcados como inativos.

A classificação dos VLFs é feita da seguinte forma pelo SQL:

Recuperável: o VLF está usado mas ainda não foi feito backup do T-Log, portanto, não é possível utilizá-lo.
Reutilizável: neste caso o VLF foi usado, um backup do T-Log foi gerado e então ficou disponível para ser utilizado novamente; 

Estes VLFs são criados automaticamente pela Engine do SQL Server e não há como o Administrador do servidor interferir em seu tamanho, quantidade ou posição diretamente, mas podemos fazer isso indiretamente. Para entendermos melhor como funciona vamos utilizar um exemplo:

Se criarmos uma base e definirmos o TLog com tamanho inicial de 10MB e  autogrow de 1MB (mais comum do que se imagina), teremos VLFs de 1MB. Se esta base entrar em produção e o TLog atingir 1GB, por exemplo, teremos então mais de 1024 VLFs neste arquivo, quando poderíamos ter muito menos. O tamanho mínimo do VLF é de 256KB, imaginem um TLog de 10GB com VLFs deste tamanho.

Veja como são criados os VLFs de acordo com o autogrow configurado:

Autogrowth <   1MB = 2VLFs
Autogrowth < 64MB = 4VLFs
Autogrowth <   1GB = 8VLFs
Autogrowth >   1GB = 16VLFs

 

Vamos entender um pouco mais sobre o Transaction Log utilizando a mesma estrutura do TLog mostrado acima, mas já utilizado durante algum tempo. Suponhamos que seja um TLog de 3GB, dividido em 6 VLFs de 1GB cada:

 

 

  • Start of logical log: é onde está o início do bloco de informações de transações. Neste caso não coincide com o início do arquivo de TLog por vários possíveis motivos e o VL1 e VL2 está marcado como inativo pelo SQL.
  • End of logical log: local do arquivo onde terminam os registros de transações, no exemplo acima é bem no final do arquivo. Se novas transações fossem realizadas, um Virtual log 7 de 1GB seria criado por estar configurado como autogrow de 1024MB.
  • MinLSN:  Minimum recovery log sequence number  – número da última transação registrada no log, indispensável em caso de rollback e portanto não pode ser apagado. Ao truncar o TLog, todos os registros entre End of logical log e MinLSN são removidos. Os dados antes deste ponto permanecem.

Vamos ver como ficaria nosso TLog depois de um backup do Log. Como sabemos, o backup de Transaction Log realiza um truncate no arquivo, ou seja, apaga todos os dados que estão entre o MinLSN e o final das transações:

 

Notamos que agora temos 4 VLFs vazios – VL1, VL2, VL5 e VL6. Levando em conta que cada VLF ocupa 1GB do disco, poderíamos liberar 4GB de espaço. Se executarmos um DBCC SHRINKFILE(TesteDBLog, 2048) para tentarmos devolver os 4GB ao sistema operacional e deixarmos o arquivo de TLog com 2GB, teremos o seguinte resultado:

 

VLF_apos_shrink

 

Apesar de termos determinado que o TLog deveria ficar com 2GB, ele liberou somente o espaço do VL5 e VL6. Isto acontece pois não é possível remover os VLFs que ficam antes dos dados, ou seja, antes dos VLFs marcados como ativos. Outra observação interessante, é que o shrink é feito por VLF e não por espaço como MB e GB. Veja que a parte vazia do VL4 continua lá. Para resolver esta situação, rodarmos o DBCC SHRINK novamente e o SQL preenche o espaço vazio do VL4 com dados fictícios sem qualquer importância, apenas para completar o espaço vazio do VL4.

 

Dados fictícios

Automaticamente as transações passam a ser registradas no começo do arquivo de TLog que está em branco e em breve teremos um novo começo, um novo MinLSN e um novo fim.

Vamos executar um novo backup dos logs e ver como ficou nosso TLog:


após a movientação dos dados

Percebemos que os dados anteriores foram salvos no backup e os novos dados já estão sendo registrados no VL1.

Agora se rodarmos um DBCC SHRINKFILE novamente com o parâmetro de 1024MB, conseguiremos liberar 3GB de espaço, através do VL2, VL3 e VL4 que serão excluídos pois estão inativos.

Com esta explicação, espero ter ficado claro que é muito melhor termos um arquivo de TLog com tamanho fixo, devidamente calculado, monitorando sua utilização para evitar que se encha até a base cair.

Em breve farei outro artigo sobre como obter as informações que aprendemos aqui e outro sobre como monitorar o espaço do TLog.

Fonte: http://msdn.microsoft.com/en-us/library/aa174524(v=sql.80).aspx

Como prometi, segue link sobre como obter uma informação importante de utilização do Transaction Log. Em breve tem mais!

 

 

 

 

 

 .

2 comentários

  1. Angelo Máximo

    Luiz, parabéns pelo artigo, muito interessante e esclarecedor sobre a arquitetura do Tlog, porém fiquei com uma dúvida: não é mais prático controlar o crescimento dos Tlogs através de backups regulares dos mesmos ?
    A prática de executar o DBCC SHRINKFILE me parece ser uma “ação de emergência”, para os casos em que o database está com recovery Full, porém não há backups regulares dos Tlogs.

  2. Luiz Mercante

    Caro Angelo, que bom que gostou do artigo.

    Este artigo refere-se exclusivamente a arquitetura do TLog e ajudará muitas pessoas que encontram TLogs gigantescos e com muitos VLFs a entenderem como funciona para tomarem as ações emergenciais corretivas necessárias.

    Em breve farei um artigo descrevendo possíveis estratégias de backup com seus prós e contras.

    Abs!

Deixe uma resposta