Situatie
Mai jos este o procedura ce listeaza toate user queries ce sunt in statusul sleeping de mai mult de x minute, unde x este primit ca parametru de catre procedura.
Solutie
create procedure FindSleepingUserQueris_prc
@minute int
as
SELECT
CURRENT_TIMESTAMP as currenttime,
datediff(minute,last_batch,GETDATE()) as ‘idletime_in_minute’ ,
sp.status,
sp.spid,
sp.login_time,
sp.program_name,
sp.hostprocess,
sp.loginame,
text
FROM sys.sysprocesses sp
CROSS APPLY sys.dm_exec_sql_text(sp.sql_handle) AS QT
where sp.status = ‘sleeping’ and
datediff(minute,last_batch,GETDATE()) > @minute
and spid>50
Leave A Comment?