how to find the SPID by hostname

• SQlLogic

  SSCrazy Eights

  Points: 9686

  More actions

  April 5, 2012 at 2:11 pm

  #266881

  Hi guys

  I have 7000 user connected to DB through application. So i want to know how i can find the particular person's SPID if i have his host name .

  Any Script or any ideas?

  Thanks

• Lowell

  SSC Guru

  Points: 323518

  More actions

  April 5, 2012 at 2:17 pm

  #1469817

  sure, something like this will give you all the spids that are coming from a given hostname...

  if i have 8 SSMS windows open, i will have 8 spids, so it's not 1 to 1;

  7000 users sounds like they might all be coming form a single application server though..hope this helps

  SELECT

  SPID,

  KPID,

  '[' + CAST(DBID AS VARCHAR(10)) + '] ' + DB_NAME(DBID) DBNAME,

  PROGRAM_NAME,

  HOSTNAME,

  CMD,

  OPEN_TRAN,

  LASTWAITTYPE,

  CPU,

  PHYSICAL_IO,

  STATUS,

  LOGINAME,

  NT_USERNAME,

  NT_DOMAIN

  FROM MASTER..SYSPROCESSES

  WHERE HOSTNAME = 'DEV223'

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• Jared

  SSC Guru

  Points: 61793

  More actions

  April 5, 2012 at 2:39 pm

  #1469836

  As Lowell mentioned... If they are ALL accessing the db through an application server, you will not be able to delineate it. The host is the application server, not the user that is using the application.

  Jared
  CE - Microsoft