Olá Pessoal,

Hoje vou compartilhar com vocês um script pronto para você monitorar o Transaction Log do seu SQL Server.

Cenário real do Dia a Dia:

Já perdi as contas de quantos clientes já me procuraram com uma base de dados parada e com uma mensagem de Transaction Log FULL.

Por default, o recovery model das bases do SQL Server é FULL. Isso significa que ele vai manter todas as operações de insert/update/delete no seu transaction log até que você realize um backup de log, o que muitas vezes o cliente não faz (o cliente só roda um backup FULL).

O log vai crescendo até acabar o espaço em disco, e quando isso acontece, a base de dados para.

Outros cenários do Dia a Dia:

Alguém realiza um update/delete gigante e o log cresce muito.

Alguém deixa uma transação aberta por muito tempo e o log cresce muito.

É realizado a desfragmentação de todos os índices e o log cresce muito.

Seja proativo!

Para evitar que seu banco de dados pare devido a um LOG FULL, o mínimo que você tem que fazer é monitorar a utilização desse arquivo de log. Quando seu arquivo de log passar de 85% de utilização, você receberá um e-mail e já vai validar o que está enchendo o Log para resolver antes que um problema mais grave aconteça.

Segue abaixo o e-mail que será enviado pelo ALERTA:

Na primeira parte temos a base, o tamanho atual do log e o % de utilização desse arquivo.

Na segunda parte temos quais as queries que estavam rodando no servidor no momento do alerta. Com isso você pode ter uma ideia de quem é o culpado do log ter passado de 85% de utilização.

Nesse caso do exemplo tinham dois desenvolvedores rodando uma query no server (exemplo meramente ilustrativo).

Massa demais!!!

Segue abaixo o e-mail que será enviado quando ocorrer o CLEAR:

Como crio esse alerta?

Pré-requisitos:

• Configurar o envio de e-mail do SQL Server (claro). Segue um link como referência.
• Criar a a procedure “sp_WhoIsActive” do Adam Machanic, segue o link. Usamos ela no alerta.

 

Segue o script para criar esse alerta: Download Script

Esse script vai criar uma tabela chamada Alerta para controlar o ALERTA e o CLEAR.

E também vai criar uma procedure chamada stpAlerta_Log_Full que fará a validação. Você deve colocar essa procedure em um job para rodar a cada 1 minuto (dever de casa).

No início do script tem uma documentação dizendo que você precisa alterar o Profile e o E-mail de envio do Alerta.

Você pode configurar os parâmetros da melhor maneira que for atender ao seu ambiente:

Eu monitoro quando o log passa de 85% e só monitoro bases que tem o arquivo de log maior que 100 MB. Bases que tem um log menor que isso, deixo eles crescerem até atingir esse tamanho (normalmente não são críticas).

Para verificar o tamanho e a utilização atual dos arquivos de log, basta executar o script abaixo:

Em todos os meus clientes onde realizo um suporte e monitoramento mensal eu crio esse alerta para me ajudar na administração do ambiente.

 

Gostou dessa Dica?

Curta, comente, compartilhe…

Assine meu canal no Youtube e curta minha página no Facebook para receber Dicas de Leituras, Vídeos e Eventos sobre SQL Server.

Veja esse e outros Alertas no meu Treinamento de Tarefas do Dia a Dia de um DBA.

Até a próxima.

Fabrício Lima

MCITP – Database Administrator

Consultor e Instrutor SQL Server

Trabalha com SQL Server desde 2006