Olá Pessoal,

Hoje vou compartilhar com vocês o script de mais um alerta para nos ajudar na Administração do nosso SQL Server.

Esse alerta é muito útil no dia a dia. Ele vai monitorar os famosos “Locks” que acontecem no nosso banco de dados. Caso aconteça algum Lock com um processo que esteja executando a mais de 2 minutos (valor parametrizável), nós seremos alertados.

Simulando um problema em uma Base

Para testar o alerta fiz o seguinte procedimento:

• Criei uma tabela e inseri uma linha;

• Abri uma nova query (Query 1) e executei um UPDATE nessa tabela com um BEGIN TRAN mas sem finalizar (COMMIT). Dessa forma, temos uma transação aberta alocando exclusivamente essa linha da tabela.

• Abri uma outra query (Query 2) e tentei fazer o mesmo UPDATE, mas dessa vez o comando é bloqueado pelo passo anterior.

Após 2 minutos de lock, quando o job de alerta rodar, ele vai enviar o seguinte e-mail:

Olha quanta informação importante esse e-mail nos mostra:

TOP 50 – Processos Raiz Lock – Se você rodar a sp_whoisactive durante um lock grande vai ficar perdido com o resultado. Contudo, nessa parte do e-mail temos uma coluna (Nível Lock) que informa a raíz do lock (Nível Lock = 0). Dessa forma, conseguimos ver facilmente os processos que estão encadeando todos os outros locks.

TOP 50 – Processos executando no Banco de Dados – Essa parte do e-mail simplesmente mostra as TOP 50 queries que estão em execução na sp_whoisactive (ordenada por tempo de execução), estando elas envolvidas ou não no lock que está acontecendo.

Nas colunas temos tantas informações legais, que conseguimos até já realizar alguma ação sem precisar se conectar no SQL Server:

• Qual o usuário está executando cada processo;
• O horário de início e o tempo de execução do processo;
• A query que está sendo executada;
• De qual máquina a query está sendo executada;
• Qual a base;
• O Status das conexões (sleeping, suspended e etc…)
• Dentre outras informações…

Para finalizar o lock, dei um COMMIT nas duas transações que deixei aberta.

A próxima vez que o job de alerta rodar ele vai enviar o e-mail abaixo:

Pronto. Agora sei quando ocorre um Lock mais demorado no meu Banco de Dados sem mesmo ter que conectar no SQL Server.

Um detalhe importante é que você só vai receber um e-mail de Alerta (não vai encher sua caixa de entrada). O e-mail de CLEAR só será enviado após a resolução do problema.

Como crio esse alerta?

Pré-requisitos:

• Configurar o envio de e-mail do SQL Server (claro). Segue um link como referência.

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

Em todos os meus clientes onde realizo um suporte e monitoramento mensal eu crio esse alerta para me ajudar na administração do ambiente. Agora, fica aí todo o script prontinho para vocês também usarem caso ainda não tenham algo parecido.

Outros alertas que já disponibilizei:

• Alerta: Monitorando o Status das Bases de Dados no SQL Server
• Alerta: Monitorando o tamanho do Transaction Log no SQL Server
• Casos do Dia a Dia: Monitorando o tamanho do arquivo MDF do Tempdb e sua Version Store

Vem mais por aí.

Gostou dessa Dica?

Curta, comente, compartilhe com os coleguinhas…

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

Até a próxima.

Fabrício Lima

Microsoft Data Platform MVP

Consultor e Instrutor SQL Server

Trabalha com SQL Server desde 2006