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

Join the discussion and add your comment

Share

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

2.75 (4)

You rated this post out of 5. Change rating

Related content

Technical Article

Trace-scrubbing Tools

Andrew Zanevsky shares his trace-scrubbing procedures that make it easy for you to handle large trace files and aggregate transactions by type–even when captured T-SQL code has variations.

SQL Server Profiler is a veritable treasure trove when it comes to helping DBAs optimize their T-SQL code. But, the surfeit of riches (I'm reminded of the Arabian Nights tale of Aladdin) can be overwhelming. I recently had one of those "sinking" feelings when I first tried to make sense of the enormous amount of data collected by traces on a client's servers. At this particular client, the online transactions processing system executes more than 4 million database transactions per hour. That means that even a 30-minute trace that captures "SQL Batch Completed" events results in a table with 2 million rows. Of course, it's simply impractical to process so many records without some automation, and even selecting the longest or most expensive transactions doesn't necessarily help in identifying bottlenecks. After all, short transactions can be the culprits of poor performance when executed thousands of times per minute.

2005-01-11

2,003 reads

SQLServerCentral Article

Monitoring Drive and Database Free Space

SQL Server will autogrow your databases as they run out of space. But the process doesn't manage space, nor does it check the free space on the drive. Allowing autogrow to grow unchecked and unmanaged will eventually use up all the free disk space and potentially crash your server. New author Mark Nash brings you his system for monitoring space usage and generating a report that eases this process.

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

4.4 (5)

You rated this post out of 5. Change rating

2017-02-02 (first published: )

44,928 reads