I want to create a report using below query for blocking session however I am not sure what script to use define the destination DB server so that it will fetch data from SQL database server not all other servers. Currently this query is fetching data from the poling engine servers.
select p1.spid,p1.blocked,p1.hostname,p1.program_name,p1.waittime,p1.lastwaittype,p1.dbid,p1.login_time,p1.loginame,
(CASE
WHEN txt.encrypted = 1 THEN 'Encrypted'
WHEN r.session_id IS NULL THEN txt.text
ELSE LTRIM( SUBSTRING(txt.text, r.statement_start_offset / 2 + 1 ,(
(CASE
WHEN r.statement_end_offset = -1 THEN DATALENGTH(txt.text)
ELSE r.statement_end_offset
END) - r.statement_start_offset) / 2
))
END) AS [Query] from
master..sysprocesses p1
INNER JOIN sys.dm_exec_connections c (NOLOCK) ON c.session_id = p1.spid CROSS APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) AS txt
LEFT JOIN sys.dm_exec_requests r ON c.session_id = r.session_id where p1.blocked > 0 or p1.spid in (select blocked from master..sysprocesses)
Could anyone suggest on that?
