We have a sql server instance that DPA is reporting several old machines connecting to it. When I look at the hosts in SQL Server's Activity Monitor, I don't see them. Where is DPA getting the connection information?
Thanks for any help!
We run a quickpoll once every second to extract information from SQL Server (see query below). We view this data from the lens of SQL Server, so my question is why would SQL Server be telling us this (s.hostname)? We had another case where a query was being executed from a spreadsheet where the hostname was hardcoded in, so it appeared as if it was coming from a discontinued app server... Could something similar be happening in your environment?
Feel free to run this query outside of DPA to see the raw data SQL Server is returning for us.
SELECT s.loginame, db_name(s.dbid) name, s.hostname,
s.program_name, s.sql_handle,
s.stmt_start, s.stmt_end, s.spid,
CONVERT(smallint, s.waittype) waittype, s.lastwaittype,
s.ecid, s.waittime, CONVERT(varchar(64), s.context_info) context_info,
RTRIM(r.wait_resource) waitresource, s.blocked ,
r.plan_handle, r.statement_start_offset, r.statement_end_offset, r.start_time,
r.query_plan_hash
FROM master..sysprocesses AS s WITH(NOLOCK)
LEFT OUTER JOIN sys.dm_exec_requests r WITH(NOLOCK) ON r.session_id = s.spid
WHERE (s.dbid<>0
AND s.cmd<>'AWAITING COMMAND'
AND s.cmd NOT LIKE '%BACKUP%'
AND s.cmd NOT LIKE '%RESTORE%'
AND s.cmd NOT LIKE 'FG MONITOR%'
AND s.hostprocess > ''
@SPID)
AND lastwaittype NOT IN ('BROKER_EVENTHANDLER', 'CLR_MANUAL_EVENT', 'SLEEP_BPOOL_FLUSH', 'SLEEP_TASK', 'WAITFOR')
Thank you for the information! It gives me a place to start - we had thought it was a spreadsheet because of the query, but I hadn't thought of information hard coded in the there.I'll use the query you provided to see if we can locate them!
