Loading…

Alerta: Monitorando Processos Bloqueados no SQL Server

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;
USE NomeDatabase

GO

CREATE TABLE Teste_Lock (
ID INT
)

INSERT INTO Teste_Lock VALUES(1)
  • 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.
-- Executar na Query 1
BEGIN TRAN
	UPDATE Teste_Lock
	SET ID = ID

-- COMMIT
  • Abri uma outra query (Query 2) e tentei fazer o mesmo UPDATE, mas dessa vez o comando é bloqueado pelo passo anterior.
-- Executar na Query 2
BEGIN TRAN
	UPDATE Teste_Lock
	SET ID = ID

-- COMMIT

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:

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

3 thoughts on “Alerta: Monitorando Processos Bloqueados no SQL Server

  1. Boa tarde Fabricio.
    Primeiro quero dizer que estou fazendo seu curso “Tarefas do Dia a Dia de um DBA” que esta na UDEMY e estou achando o curso muito bom.
    Segundo, estou com um problema com esse alerta.
    O script é executado, porém o e-mail não é enviado. Tenho outros scripts que enviam o alerta:

    Esse é o log do job.

    Data 04/02/2020 15:54:00
    Log Histórico do Trabalho (DBA – Envia email com processos bloqueados)

    ID da Etapa 1
    Servidor XXXXXXX
    Nome do Trabalho DBA – Envia email com processos bloqueados
    Nome da Etapa Envia Email
    Duração 00:00:01
    Severidade do Sql 0
    ID da Mensagem Sql 8625
    Operador Notificado por Email
    Operador Notificado por Envio de Rede
    Operador Notificado por Pager
    Repetições Tentadas 0

    Mensagem
    Executed as user: NT SERVICE\SQLSERVERAGENT. Warning: Null value is eliminated by an aggregate or other SET operation. [SQLSTATE 01003] (Message 8153) Warning: The join order has been enforced because a local join hint is used. [SQLSTATE 01000] (Message 8625). The step succeeded.

Deixe uma resposta

%d blogueiros gostam disto: