sysprocesses showing blanks for hostname and programname fields. why?

• vsamantha35

  SSChampion

  Points: 11551

  More actions

  November 28, 2019 at 9:16 am

  Go to Answer

  #3701863

  Hi All,

  For some of spids, I am seeing hostname and program_name as blanks. But, I am seeing sql text, login name etc...

  Why is it so? any thoughts why sql server is not showing hostname and program names?

  --sleeping spids with hostname and program name is showing blank

  select s.spid,s.[status],s.blocked,db_name(s.dbid) as dbname,s.open_tran,s.hostname,s.[program_name],s.cmd,s.loginame,s.nt_domain,s.nt_username,s.login_time,

  LEFT([sql].[text], 1000) as [text]

  from sys.sysprocesses s

  OUTER APPLY sys.dm_exec_sql_text (s.sql_handle) as [sql]

  where s.[status] = 'sleeping'

  and (s.hostname='' and s.[program_name]='')

  

  Thanks,

  Sam

• vsamantha35

  SSChampion

  Points: 11551

  More actions

  November 28, 2019 at 6:20 pm

  Answer

  #3701980

  I was able to figure out the client ip address and from using "ping -a <ip>" was able to find out from which machine or app server connections coming from

   

  SELECT

  s.session_id,

  s.host_name,

  s.program_name,

  c.client_net_address,

  c.local_net_address,

  db_name(s.database_id) as dbname,

  s.open_transaction_count,

  s.status,

  s.login_name,

  s.is_user_process,

  c.net_transport,

  c.encrypt_option,

  c.auth_scheme,

  c.net_packet_size,

  c.client_tcp_port,

  c.connection_id

  FROM

  sys.dm_exec_sessions as s

  INNER JOIN sys.dm_exec_connections c on s.session_id = c.session_id