Post reply

clientprocessid in profiler

  • January 31, 2008 at 3:43 pm

    #183667

    I am not exactly sure what clientprocessid in profiler trace. Could I please let an explanation of it?

    Thanks in adavance!

  • January 31, 2008 at 9:55 pm

    #774042

    From SQLHelp.

    ClientProcessID

    The ID assigned by the host computer to the process where the client application is running. This data column is populated if the client process ID is provided by the client.

    Such as looks like SSMS always have ClientProcessID = 1000, true?

    Cheers.

  • January 31, 2008 at 11:07 pm

    #774061

    Tony (1/31/2008)

    From SQLHelp.

    Such as looks like SSMS always have ClientProcessID = 1000, true?

    No. Depends on what other apps are running on the client, and when SSMS was started. Open task manager, go to Processes and look at the PID column. That's what comes through to profiler.

    On my machine at the moment, SSMS is 1188. 1000 is svchost.

    It's useful for identifying connections from a machine used by multiple remote desktop/teminal services connections from different people. ou can check the ClientProcessId and see which instance of SSMS/QA has that PID and who's running it.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • February 1, 2008 at 8:03 am

    #774227

    Thanks for the reply.

    I guess I am still not quite clear yet. This is what I am trying to find out. We have an internal web site where all customer service agents log on to process orders. It uses two web servers and one database server. So if I run profiler on the database server, can I nail down on which transaction is run from which computer(user) through the clientprocessid?

  • February 1, 2008 at 11:59 am

    #774373

    P.S. we are only using sql server authentication. So in profiler, it seems there is no way to track down which computer/user the query is running from. So how do I know who is doing what?

    Thanks!

  • February 1, 2008 at 1:27 pm

    #774428

    The host name column will give you the machine name from which the query came. You can use the program name to identify the name of the app that submitted the query.

    ClientProcess is only really useful if you have multiple instances of one application on one machine accessing the DB and you know the machine, but not which instance. It's useless for identifying a machine

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • February 1, 2008 at 1:34 pm

    #774432

    Thanks!

    In our case, the hostname is always the web server name and application name is iis. So seems like there is no way to tell which user machine the query is from because they all use sql authentication.

  • February 1, 2008 at 1:46 pm

    #774443

    Not from SQL. SQL Server only knows who the client machine is, in this case, the web server. If you need to be able to track back from there, you'll need some form of monitoring/logging on IIS.

    In this case, all the ClientProcessID will give you it the process of the IIS worker.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 18, 2009 at 11:00 pm

    #929026

    Hello,

    I'm making some research how to use the SQL Profiler and I want to know the machine which execute the SQL command - I've read on and seen you have answered to see the ClientProcessID . One question is the ClientprocessID is link to the SQL executed or to the machine which execute the SQL command. Because in my case The NTUserName and LoginName is giving me the IUSR logging. So i'm not able to trace the machine doing it as all are connected on server and using IUSR login.

    Thanks if anyone can clear me out on this.

    N

  • January 18, 2009 at 11:59 pm

    #929034

    Client process won't give you the machine. It gives you the processid on that machine. For the machine, use the hostname column.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 19, 2009 at 12:14 am

    #929039

    Thanks for the reply ..

    But in SQL Profiler 2000, you could filter your query by the client's hostname.. I can't find how to do that with the SQL Server 2005 version(am using this version) . where should i go to get this option to select the hostname?

  • January 19, 2009 at 1:06 am

    #929050

    nashreen.mosaheb (1/19/2009)

    But in SQL Profiler 2000, you could filter your query by the client's hostname.. I can't find how to do that with the SQL Server 2005 version(am using this version)

    Add the column to the columns included in the trace (if it's not included by default) in then go to filters and add a filter on it.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 19, 2009 at 2:19 am

    #929060

    Ok fine , I could get hold of the HostName column , but when i'm connecting via the program interface(ASP) its using the IUSR login and , its only giving me the ServerMachine name, not the client/user hostname. Is there anyway i get hold of the user machine name please ...

    Thanks

  • January 19, 2009 at 4:16 am

    #929083

    SQL only knows what machine initiated the database connection. In the case of a web application (asp, asp.net, php), that's the web server. If you want to trace who's connecting to the web server, you have to do that on the web server.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply