Identificando e encerrando conexões no BD – Terminal de Informação

Identificando e encerrando conexões no BD

Hoje vou mostrar como identificar processos que estão consumindo recursos do SQL Server e encerrá-los.

1. Abra o Monitor de Atividades

Monitor de Atividades

2. Nas consultas caras ativas (que estão em execução / travadas), procure pelo ID da Sessão

<img decoding="async" aria-describedby="caption-attachment-99563" data-attachment-id="99563" data-permalink="https://terminaldeinformacao.com/2020/01/01/identificando-e-encerrando-conexoes-no-bd/db_02/" data-orig-file="https://i0.wp.com/terminaldeinformacao.com/wp-content/uploads/2019/09/db_02.png?fit=1430%2C736&ssl=1" data-orig-size="1430,736" data-comments-opened="1" data-image-meta="{"aperture":"0","credit":"","camera":"","caption":"","created_timestamp":"0","copyright":"","focal_length":"0","iso":"0","shutter_speed":"0","title":"","orientation":"0"}" data-image-title="db_02" data-image-description="" data-image-caption="<p>ID da sessão</p> " data-medium-file="https://i0.wp.com/terminaldeinformacao.com/wp-content/uploads/2019/09/db_02.png?fit=300%2C154&ssl=1" data-large-file="https://i0.wp.com/terminaldeinformacao.com/wp-content/uploads/2019/09/db_02.png?fit=720%2C371&ssl=1" class="size-full wp-image-99563" src="https://i0.wp.com/terminaldeinformacao.com/wp-content/uploads/2019/09/db_02.png?resize=720%2C371&ssl=1" alt="" width="720" height="371" srcset="https://i0.wp.com/terminaldeinformacao.com/wp-content/uploads/2019/09/db_02.png?w=1430&ssl=1 1430w, https://i0.wp.com/terminaldeinformacao.com/wp-content/uploads/2019/09/db_02.png?resize=300%2C154&ssl=1 300w, https://i0.wp.com/terminaldeinformacao.com/wp-content/uploads/2019/09/db_02.png?resize=768%2C395&ssl=1 768w, https://i0.wp.com/terminaldeinformacao.com/wp-content/uploads/2019/09/db_02.png?resize=1024%2C527&ssl=1 1024w, https://i0.wp.com/terminaldeinformacao.com/wp-content/uploads/2019/09/db_02.png?resize=740%2C381&ssl=1 740w" sizes="(max-width: 720px) 100vw, 720px" data-recalc-dims="1" />

ID da sessão

3. Veja se encontra o ID no DBAccess

<img decoding="async" aria-describedby="caption-attachment-99564" data-attachment-id="99564" data-permalink="https://terminaldeinformacao.com/2020/01/01/identificando-e-encerrando-conexoes-no-bd/db_03/" data-orig-file="https://i0.wp.com/terminaldeinformacao.com/wp-content/uploads/2019/09/db_03.png?fit=1336%2C824&ssl=1" data-orig-size="1336,824" data-comments-opened="1" data-image-meta="{"aperture":"0","credit":"","camera":"","caption":"","created_timestamp":"0","copyright":"","focal_length":"0","iso":"0","shutter_speed":"0","title":"","orientation":"0"}" data-image-title="db_03" data-image-description="" data-image-caption="<p>Encontrando no DbMonitor</p> " data-medium-file="https://i0.wp.com/terminaldeinformacao.com/wp-content/uploads/2019/09/db_03.png?fit=300%2C185&ssl=1" data-large-file="https://i0.wp.com/terminaldeinformacao.com/wp-content/uploads/2019/09/db_03.png?fit=720%2C444&ssl=1" class="size-full wp-image-99564" src="https://i0.wp.com/terminaldeinformacao.com/wp-content/uploads/2019/09/db_03.png?resize=720%2C444&ssl=1" alt="" width="720" height="444" srcset="https://i0.wp.com/terminaldeinformacao.com/wp-content/uploads/2019/09/db_03.png?w=1336&ssl=1 1336w, https://i0.wp.com/terminaldeinformacao.com/wp-content/uploads/2019/09/db_03.png?resize=300%2C185&ssl=1 300w, https://i0.wp.com/terminaldeinformacao.com/wp-content/uploads/2019/09/db_03.png?resize=768%2C474&ssl=1 768w, https://i0.wp.com/terminaldeinformacao.com/wp-content/uploads/2019/09/db_03.png?resize=1024%2C632&ssl=1 1024w, https://i0.wp.com/terminaldeinformacao.com/wp-content/uploads/2019/09/db_03.png?resize=740%2C456&ssl=1 740w, https://i0.wp.com/terminaldeinformacao.com/wp-content/uploads/2019/09/db_03.png?resize=310%2C190&ssl=1 310w" sizes="(max-width: 720px) 100vw, 720px" data-recalc-dims="1" />

Encontrando no DbMonitor

4. Tente encerrar pelo DBAccess

<img decoding="async" aria-describedby="caption-attachment-99565" data-attachment-id="99565" data-permalink="https://terminaldeinformacao.com/2020/01/01/identificando-e-encerrando-conexoes-no-bd/db_04/" data-orig-file="https://i0.wp.com/terminaldeinformacao.com/wp-content/uploads/2019/09/db_04.png?fit=347%2C251&ssl=1" data-orig-size="347,251" data-comments-opened="1" data-image-meta="{"aperture":"0","credit":"","camera":"","caption":"","created_timestamp":"0","copyright":"","focal_length":"0","iso":"0","shutter_speed":"0","title":"","orientation":"0"}" data-image-title="db_04" data-image-description="" data-image-caption="<p>Encerrando</p> " data-medium-file="https://i0.wp.com/terminaldeinformacao.com/wp-content/uploads/2019/09/db_04.png?fit=300%2C217&ssl=1" data-large-file="https://i0.wp.com/terminaldeinformacao.com/wp-content/uploads/2019/09/db_04.png?fit=347%2C251&ssl=1" class="size-full wp-image-99565" src="https://i0.wp.com/terminaldeinformacao.com/wp-content/uploads/2019/09/db_04.png?resize=347%2C251&ssl=1" alt="" width="347" height="251" srcset="https://i0.wp.com/terminaldeinformacao.com/wp-content/uploads/2019/09/db_04.png?w=347&ssl=1 347w, https://i0.wp.com/terminaldeinformacao.com/wp-content/uploads/2019/09/db_04.png?resize=300%2C217&ssl=1 300w" sizes="(max-width: 347px) 100vw, 347px" data-recalc-dims="1" />

Encerrando

5. Caso não dê, encerre pelo comando kill, via SQL

<img decoding="async" aria-describedby="caption-attachment-99566" data-attachment-id="99566" data-permalink="https://terminaldeinformacao.com/2020/01/01/identificando-e-encerrando-conexoes-no-bd/db_05/" data-orig-file="https://i0.wp.com/terminaldeinformacao.com/wp-content/uploads/2019/09/db_05.png?fit=344%2C159&ssl=1" data-orig-size="344,159" data-comments-opened="1" data-image-meta="{"aperture":"0","credit":"","camera":"","caption":"","created_timestamp":"0","copyright":"","focal_length":"0","iso":"0","shutter_speed":"0","title":"","orientation":"0"}" data-image-title="db_05" data-image-description="" data-image-caption="<p>Forçando encerramento via comando</p> " data-medium-file="https://i0.wp.com/terminaldeinformacao.com/wp-content/uploads/2019/09/db_05.png?fit=300%2C139&ssl=1" data-large-file="https://i0.wp.com/terminaldeinformacao.com/wp-content/uploads/2019/09/db_05.png?fit=344%2C159&ssl=1" class="size-full wp-image-99566" src="https://i0.wp.com/terminaldeinformacao.com/wp-content/uploads/2019/09/db_05.png?resize=344%2C159&ssl=1" alt="" width="344" height="159" srcset="https://i0.wp.com/terminaldeinformacao.com/wp-content/uploads/2019/09/db_05.png?w=344&ssl=1 344w, https://i0.wp.com/terminaldeinformacao.com/wp-content/uploads/2019/09/db_05.png?resize=300%2C139&ssl=1 300w" sizes="(max-width: 344px) 100vw, 344px" data-recalc-dims="1" />

Forçando encerramento via comando

Bom pessoal, por hoje é só.

Abraços e até a próxima.

Relacionado

Tags: SQL Server

1 de janeiro de 2020

<img alt='' src='https://secure.gravatar.com/avatar/49e8476dcf7c079a7d38808f17443657?s=128&d=mm&r=g' srcset='https://secure.gravatar.com/avatar/49e8476dcf7c079a7d38808f17443657?s=256&d=mm&r=g 2x' class='avatar avatar-128 photo' height='128' width='128' decoding='async'/>

Dan (Daniel Atilio)

Cristão de ramificação protestante. Especialista em Engenharia de Software pela FIB, graduado em Banco de Dados pela FATEC Bauru e técnico em informática pelo CTI da Unesp. Entusiasta de soluções Open Source e blogueiro nas horas vagas. Autor e mantenedor do portal Terminal de Informação.

2 Responses

Comments2
Pingbacks0

Julio Cesar Marques Pires disse:
2 de janeiro de 2020 às 8:13 am
Valeu Dan_Atilio ! Obrigado !
Responder
- Dan_Atilio disse:
  8 de janeiro de 2020 às 5:17 am
  Opa, obrigado pelo comentário jovem.
  Grande abraço.
  Responder

Deixe uma resposta para Dan_AtilioCancelar resposta