Vi da il numero di sessione, se la sessione è bloccata da qualche altra sessione, lo stato, da quando è partita, minuti, secondi, utente, software, lo script lanciato e così via...
Funziona solo se avete diritti completi sull'istanza SQL
Codice: Seleziona tutto
SELECT distinct
UserGo.session_id, blocked As BlkBy, UserGo.login_time,
req.status As 'Status_R', UserGo.status As 'Status_U',
sp.dbid As 'DB_ID', sd.name As 'DB_Name', HOST_NAME,
client_net_address, login_name,
req.[total_elapsed_time]/1000 As Query_Sec_R,
req.[total_elapsed_time]/1000/60 As Query_Min_R,
req.cpu_time/1000 As CpuTime_Sec_R,
req.command As Command_R, wait_type, sqltext.TEXT,
client_interface_name, UserGo.PROGRAM_NAME,
UserGo.[total_elapsed_time]/1000/60 As SumAll_Query_Min_U,
req.reads As Reads_R, req.writes As Writes_R, UserGo.row_count As RecordEstratti,
req.logical_reads As Logical_Reads_R, req.total_elapsed_time As Total_Elapsed_Time_R
FROM sys.dm_exec_sessions As UserGo Left Join sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext on
req.session_id = UserGo.session_id
Left Join master.dbo.sysprocesses sp on
UserGo.session_id = spid
Left Join master.dbo.sysdatabases sd ON sp.dbid = sd.dbid
Left Join sys.dm_exec_connections cn on
UserGo.session_id = cn.session_id
where UserGo.status = 'running'
Order by [db_name] desc, req.command desc, login_time desc