Loading…

Casos do Dia a Dia – Você sabia que um arquivo de Log do SQL Server se fragmenta?

Fala Pessoal,

Vou ser sincero com vocês, até pouco tempo eu não tinha a menor idéia que um arquivo de log do SQL Server se fragmentava. Nunca tinha lido sobre o assunto. Entretanto, para isso que servem os mais 100 Blogs de SQL Server cadastrados no meu google reader e os vários profissionais SQL Server que sigo no twitter e que também divulgam dicas de excelentes artigos, whitepapers e etc.

Pouco tempo atrás, alguém “twitou” esse excelente artigo 8 Steps to better Transaction Log throughput.

Nesse artigo Kimberly L. Tripp (Blog|Twitter) da uma série de dicas para serem aplicadas nos arquivos de log do SQL Server.  Uma dessas dicas (a de número oito) é para checar e corrigir a fragmentação interna desses arquivos. Ela ainda disse que essa dica resolveu um problema de um cliente na Turquia.

Caso o seu arquivo de log tenha sofrido muitos autogrowths, ele pode ficar internamente fragmentado. O arquivo de log do SQL Server é dividido em pequenos pedaços chamados VLF(Virtual Log Files) que crescem a medida que seu arquivo de log cresce. Segundo Kimberly, geralmente, a maioria dos arquivos de logs devem ter somente entre 20 e 30 VLF. 50 ainda é aceitável dependendo do tamanho do seu arquivo de log.

Um número excessivo de VLF pode causar um impacto negativo em todas as atividades do transaction log e além disso, ainda pode ocorrer uma degradação da performance enquanto um backup do log está sendo executado. Para conferir quantos VLF você tem em uma database, basta verificar o número de linhas retornadas pelo comando DBCC LOGINFO conforme a figura abaixo:

Nesse caso, o arquivo de log dessa database possui 4 VLFs.

Seguindo o procedimento do artigo, realizei os seguintes passos para desfragmentar o arquivo de log:

1 – Esperei por um período de pouco movimento e limpei o transaction log realizando um backup do mesmo. Caso você utilize o recovery model SIMPLE, você não precisa realizar um backup do log, ao invés disso, você limpará o transaction log rodando um CHECKPOINT.

BACKUP LOG databasename TO devicename

2 – Realizei um Shrink no log para que ele ficasse com o menor tamanho possível (por isso que ele é limpo no passo 1).

DBCC SHRINKFILE(transactionloglogicalfilename, TRUNCATEONLY)

3 – Alterei o arquivo de log da database para um tamanho apropriado onde ele necessite crescer apenas em alguns casos raros.

ALTER DATABASE databasename
MODIFY FILE
( NAME = transactionloglogicalfilename,
SIZE = newtotalsize )

No meu ambiente eu possuía uma database com 275 VLF e as maiores estavam todas com mais de 150 VLF. Após realizar os passos acima, todas as databases estão com menos de 20 VLF, sendo que dentre elas, mantenho arquivos de log com 4 GB devido a algumas operações e manutenções que ocorrem durante a madrugada.

Com relação a performance, em um dos meus servidores, faço um backup do log a cada 7 minutos para minhas 5 principais databases, com isso, possuo um grande número de backups do log. Como eu monitoro toda query que demora mais de 3 segundos, constantemente a query que fazia backup do log demorava para ser executada e era armazenada no meu log. Após a realização dos passos descritos acima, é muito raro um backup do log  entrar nesse trace. Essa melhora foi bem perceptível no meu ambiente.

Então, cabe a vocês verificarem a quantidade de VLF de suas databases pois é um procedimento bem simples.

Gostou dessa dica?

Cadastre seu e-mail para receber novos Posts e curta minha Página no Facebook para receber Dicas de Leituras e Eventos sobre SQL Server.

Confira mais experiências do Dia a Dia de um DBA no meu Treinamento de Tarefas do Dia a Dia de um DBA.

Abraços,

Fabrício Lima

MCITP – Database Administrator

Consultor e Instrutor SQL Server

Trabalha com SQL Server desde 2006

8 thoughts on “Casos do Dia a Dia – Você sabia que um arquivo de Log do SQL Server se fragmenta?

  1. Fabricio,

    Excelente post Fabrício! Agora, 100 blogs no Google Reader é coisa de doido, meu amigo! Voce tem tempo para dormir ? :-))

    Abraços,
    Alexandre Lopes

  2. Muito bom o post. Não fazia ideia que o log fragmentava. Assumi o cargo de DBA numa empresa recentemente como PJ, rodei o script passado no artigo, encontrei 700 VLFs. É pouco? 🙂

      1. Se você achou 700 muito .. Veja isso:
        Um cliente estava em 19443 e desci para 404 após suas dicas.
        Ainda estou na verificação para uma melhora futura.

  3. Fala Fabrício,

    A questão de VLF´s realmente é um mistério do SQL Server e confesso também que tomei ciência deste cara ha pouco tempo…

    Só uma observação, dependendo do tamanho do arquivo do log, cada VLF ficará com um tamanho excessivo, sendo assim, este não será liberado num eventual backup de log, causando a não redução do espaço utilizado dentro do arquivo LDF.
    Neste caso, não seria interessante fazer um balanceamento entre Quantidade de VLFs e Tamanho dos mesmos?
    Obviamente posso estar enganado, mas talvez não seja uma boa idéia ter VLFs excessivamente grandes…

    Parabéns pelo post..

    Abraço

    Edvaldo Castro

Deixe uma resposta

%d blogueiros gostam disto: