2 Replies Latest reply on Apr 2, 2015 2:18 PM by mbarrasso

    How to find machine name lookup? DPA 9 , SQL Server

    mbarrasso

      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!

        • Re: How to find machine name lookup? DPA 9 , SQL Server
          mandevil

          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 > ''  

                 AND s.spid>50 AND s.spid<>@@SPID)  

                 AND lastwaittype NOT IN ('BROKER_EVENTHANDLER', 'CLR_MANUAL_EVENT', 'SLEEP_BPOOL_FLUSH', 'SLEEP_TASK', 'WAITFOR')