How can we find a particular process id to belong which instance ?.

Question

How can we find a particular process id to belong which instance ?.

Joy Smith San

SSC-Insane

Points: 24877
More actions
November 4, 2011 at 4:26 am

#390765

Hi All,
Below was an interview question 🙂
Suppose there are 4 instances in a server.
How can we find a particular process id belong to which instance ?.
Thanks in advance.
Smith.

Viewing 12 posts - 1 through 11 (of 11 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

As in the windows process ID? Errorlog.

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

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 2

One more way is to use the serverproperty functions with the input 'ProcessID'

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Joy Smith San SSC-Insane Points: 24877 More actions · Answer 3

Thanks Gila. Infact I happend to say "SP_Who2" 🙂

One more way is to use the serverproperty functions with the input 'ProcessID'

can you pls give me the proper command ?. Sorry... I know I should have googled..

Thanks,

Smith.

Lowell SSC Guru Points: 323518 More actions · Answer 4

would they be asking you to use a DMV like sys.dm_exec_sessionsto get the column host_process_id?

I think that would require querying all four instances, right?

for example, ran Windows Task Manager and noted the PID for my SQL Server Management studio (at the time the # was 3460)

Then i ran either of these two queries, and for each Query window that was connected to that server instance, i found my process id:

select host_process_id,* from sys.dm_exec_sessions--one match for each query window i had open on that server...

select hostprocess, * from sys.sysprocesses --one match for each query window i had open on that server...

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!

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Joy Smith San (11/4/2011)
can you pls give me the proper command ?. Sorry... I know I should have googled..

Are you looking for the SQL process ID that connections to SQL have, or the windows process ID that the SQL process has? If the former, your answer. If the latter, I already told you (as did Adi)

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

Perry Whittle SSC Guru Points: 234013 More actions · Answer 6

to find the Windows process id for an instance of SQL Server you can also use SQL Server configuration manager 😎

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Jared SSC Guru Points: 61793 More actions · Answer 7

Joy Smith San (11/4/2011)
Hi All,
Below was an interview question 🙂
Suppose there are 4 instances in a server.
How can we find a particular process id belong to which instance ?.
Thanks in advance.
Smith.

You could also log into the server, open task manager, and go to the services tab...

Jared

Jared
CE - Microsoft

Joy Smith San SSC-Insane Points: 24877 More actions · Answer 8

You could also log into the server, open task manager, and go to the services tab...Jared

Windows task manager you mean. ? I am not able to find "services tab" there.

Thanks.

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 9

Joy Smith San (11/8/2011)
You could also log into the server, open task manager, and go to the services tab...Jared
Windows task manager you mean. ? I am not able to find "services tab" there.
Thanks.

PFA screenshot. 😉

Jared SSC Guru Points: 61793 More actions · Answer 10

Dev @ +91 973 913 6683 (11/9/2011)
Joy Smith San (11/8/2011)
You could also log into the server, open task manager, and go to the services tab...Jared
Windows task manager you mean. ? I am not able to find "services tab" there.
Thanks.
PFA screenshot. 😉

Hopefully the screenshot helps you find it. You may then have to change the column view to show the PID, if it is not already there.

Thanks,

Jared

Jared
CE - Microsoft

Joy Smith San SSC-Insane Points: 24877 More actions · Answer 11

. Thanks.

It's not there in my PC. reason may be bcose we r in client's network.

With limited rights.

Thanks again.