———————————————————————————-

Atualização (09/01/2014 ): No mesmo dia (06/01/2014) em que publiquei este post, o Paul Randal (Twitter | Blog) publicou um post (Important change to VLF creation algorithm in SQL Server 2014), que consiste em uma importante mudança na fórmula de criação de novos VLFs.

For SQL Server 2014, the algorithm is now:

• Is the growth size less than 1/8 the size of the log?
  • Yes: create 1 new VLF equal to the growth size
  • No: use the formula above

———————————————————————————-

Existem diversos textos, blogs e postagens sobre VLFs, sua definição, melhores práticas para configuração e manutenção na internet, incluindo alguns que selecionei abaixo, para que possam servir como uma leitura prévia e conceitual sobre o assunto.

Meu amigo infracitado Leandro Ribeiro, escreveu dois excelentes posts sobre como identificar e corrigir fragmentação no transaction log.

Kimberly Tripp (dispensa apresentações), fez também alguns posts que são referências quando o assunto é “Virtual Log Files”.

Leandro Ribeiro (Blog | Twitter)

• Fragmentação do Transaction Log – Parte I
• Fragmentação do Transaction Log – Parte II

Kimberly L. Tripp (Blog | Twitter)

• 8 Steps to better Transaction Log Throughput
• Transaction Log VLFs – too many or too few?

Greg Larsen (Perfil Simple Talk)

• SQL Server Transaction Log Fragmentation: a Primer

Devido à qualidade e riqueza de detalhes dos posts supracitados, partiremos direto ao objetivo deste.

Existem diversas abordagens diferentes quando há a intenção de controle da fragmentação, quantidade e tamanho dos VLFs. Neste artigo, será tratado a padronização pelo tamanho de cada VLFs individualmente, deixando que o SQL Server, cresça o arquivo de log de transações até o tamanho necessário para utilização, sem que haja a necessidade do efeito elástico (shrink e auto growth frequentes).

Por exemplo, se houver uma padronização por quantidades de VLFs, dois bancos de dados distintos com necessidades diferentes de sizing do Log de Transações, terão tamanhos variados por VLF.

O tamanho de um VLF não é configurado diretamente, mas existe uma tabela de crescimento, que indiretamente define o tamanho e a quantidade dos VLFs.

De acordo com a tabela acima, podemos simular a relação crescimento X Tamanho dos VLFs:

Controle automático dos VLFs

Deste ponto em diante, Será apresentado a Solução em Si, e o método para implantação da mesma.

Disclaimer:

Os passos e scripts a seguir foram desenvolvidos para automatizar a configuração e controle dos Virtual Log Files, e podem causar algum tipo de bloqueio ou indisponibilidade temporária do SQL Server. Antes de implantar ou utilizar partes ou todo o conteúdo aqui disponibilizado, sugiro a leitura e entendimento do que cada passo realiza. Implante primeiro em um ambiente de testes controlado, e caso veja benefícios, faça em seu ambiente de produção, POR SUA CONTA E RISCO.

1º Passo – Setup do ambiente

Clique aqui  para fazer o download deste script (download completo ao final do artigo)

Existe um comando no SQL Server que retorna informações sobre o Log de Transações, o DBCC LOGINFO. Este DBCC retorna informações como, quantidade de VLFs, tamanho destes VLFs, LSN, etc. Com base nestas informações, trataremos o ambiente atual e a criação dos padrões que posteriormente servirão de base para as verificações periódicas automáticas.

Estou considerando “Tamanho Ideal”, o tamanho atual do arquivo físico do Log de transações. Para que isto seja algo fidedigno, sugiro deixar o Log de todos os bancos crescer o tanto quanto for necessário, afim de que este atinja seu maior tamanho, e este será o tamanho considerado “ideal”, ou o máximo que esta base já precisou em uma rotina.

Neste ponto, o Script cria uma tabela chamada “ControlaVLF” na base de dados de sistema “master” com os dados da instância e de todos os bancos de dados de usuários contidos nesta instância.

O tamanho padrão definido para cada VLF individualmente foi de 256 MB (lembrando que alterações podem ser realizadas no script para adequar às necessidades de cada ambiente).

Execute o script uma primeira vez, para a criação da stored procedure “sp_SetupVLFControl” e em seguida execute o comando para execução da stored procedure (uma única vez):

EXECUTE master..sp_SetupVLFControl

2º Passo – Ajustes iniciais

Clique aqui para fazer o download deste script (download completo ao final do artigo)

O segundo passo é a alteração inicial da instância onde o procedimento está sendo realizado. Este passo deve ser realizado preferencialmente em um horário em que não há atividade intensa na base de dados, pois o processo de SHRINK e GROWTH do arquivo pode causar bloqueios temporários na base.

Neste script é criado a stored procedure “sp_InitialSetupVLFControl” cuja execução realiza um LOOP em todos bancos de dados de usuário que estão online. Em cada um dos bancos de dados de usuário são realizadas as seguintes tarefas:

• Verificação do Recovery Model do banco de dados
• Realização de Backup de Log (em caso de Recovery Model Full)
• Shrink do arquivo de T-Log para com a opção “EMPTY FILE”
• Crescimento do arquivo em tamanhos pré-definidos (de acordo com a tabela dos VLFs)

Ao final, todos os bancos de dados de usuário terão o mesmo tamanho de VLF com quantidades diferentes, devido ao fato de que o Tamanho Ideal (indicado na tabela master..ControlaVLF) de cada banco é diferente.

Execute o script uma primeira vez, para a criação da stored procedure “sp_InitialSetupVLFControl” e em seguida execute o comando para execução da stored procedure (uma única vez):

EXECUTE master.. sp_InitialSetupVLFControl

3º Passo – Verificação OnDemand

Clique aqui  para fazer o download deste script (download completo ao final do artigo)

Após realizados os passos 1 (Setup do ambiente) e 2 (Ajuste inicial) o passo final é a configuração de uma rotina (automatizada ou não) para a manutenção do padrão pré-estabelecido de tamanho dos VLFs.

O script cria uma stored procedure que realiza uma validação em todos os bancos de dados de usuários e execução das seguintes tarefas:

• Verificação se o tamanho atual do arquivo de log é maior que o tamanho ideal
• Caso a condição seja verdadeira:
  • Verificação do Recovery Model do banco
  • Backup de Log (em caso de Recovery Model Full)
• Shrink do arquivo do T-log somente ATÉ O TAMANHO IDEAL.
• Incremento do campo ShrinkCounter e atualização do campo LastShrinkDate na tabela master..ControlaVLF

Execute o script uma primeira vez, para a criação da stored procedure “sp_OnDemandCheck” e em seguida execute o comando para execução da stored procedure (sempre que quiser realizar a verificação):

EXECUTE master.. sp_OnDemandCheck

Um ponto importante que pode ser observado neste terceiro passo, é o último item onde há a atualização dos campos ShrinkCounter e LastShrinkDate, permitindo assim a verificação periódica da quantidade de vezes em que um arquivo de T-Log de uma base de dados sofreu o processo de shrink e em seguida readequando o tamanho ideal deste arquivo.

Resumo Implantação:

1. Faça o download de todos os scripts (caso não os tenha baixado individualmente)

2. Leia e entenda o que cada um dos scripts faz, (Caso tenha alguma dúvida, por favor deixe um comentário no post)

3. Execute o script do Passo 1: 01-Setup.sql
   1. Execute a stored procedure criada, através do comando:
      1. EXECUTE master..sp_SetupVLFControl

4. Execute o script do Passo 2: 02- 02-InitialConfig.sql
   1. Execute a stored procedure criada, através do comando:
      1. EXECUTE master..sp_InitialSetupVLFControl

5. Execute o script do Passo 3: 03-OnDemandCheck.sql
   1. Crie um Job no SQL Server Agent com o seguinte comando, a ser executando na periodicidade desejada (Uma vez por semana aos domingos, por exemplo):
      1. EXECUTE master.. sp_OnDemandCheck

Para consultas aos dados, basta acessar a tabela master..ControlaVLF.

Caso perceba que algum banco está com o campo ShrinkCounter muito alto, considere alterar o tamanho do valor ideal, para que a frequência de ShrinkFile reduza ou desapareça.

Qualquer dúvida, fique à vontade para comentar aqui mesmo no post.

Obrigado pela leitura.

Edvaldo Castro