Technical Article

Tracking processes

,

This script quick tip for those who will accurately monitor sessions on SQL Server, but not a basic monitor with id of sessions, blocks, username and hostname, but with IP, port of destination and origin, status, etc ...

--- Information about all the connections> 50
SELECT
C.session_id Sessão,
P.blocked Bloqueio,
P.Loginame Usuário,
P.nt_username,
P.hostname [Estação\Srv],
C.client_net_address IP_Origem,
C.client_tcp_port Porta_Origem,
C.local_net_address IP_Destino,
C.local_tcp_port Porta_Destino,
C.auth_scheme Proc_Rede,
P.program_name Aplicação,
P.Status Status1,
P.CMD Status2,
P.last_batch Ultima_Execução
FROM SYS.dm_exec_connections as C
INNER JOIN SYSprocesses as P
ON C.session_id = P.spid
ORDER BY P.last_batch desc

--- Information blockade
SELECT
C.session_id Sessão,
P.blocked Bloqueio,
P.Loginame Usuário,
P.hostname [Estação\Srv],
C.client_net_address IP_Origem,
C.client_tcp_port Porta_Origem,
C.local_net_address IP_Destino,
C.local_tcp_port Destino,
P.program_name Aplicação,
P.Status,
P.last_batch Ultima_Execução
FROM SYS.dm_exec_connections as C
INNER JOIN SYSprocesses as P
ON C.session_id = P.spid
WHERE P.blocked <> 0
ORDER BY P.status

--- Active sessions > 50
SELECT
C.session_id Sessão,
P.Loginame Usuário,
P.hostname [Estação\Srv],
C.client_net_address IP_Origem,
C.local_net_address IP_Destino,
R.command Comando,
D.name,
P.program_name Aplicação,
P.Status
FROM SYS.dm_exec_connections as C
INNER JOIN SYSprocesses as P
ON C.session_id = P.spid
INNER JOIN SYS.dm_exec_requests as R
ON C.session_id = R.session_id
INNER JOIN SYSdatabases as D
ON P.dbid = D.dbid

Rate

2.75 (4)

You rated this post out of 5. Change rating

Share

Share

Rate

2.75 (4)

You rated this post out of 5. Change rating