Question: How to Identify Session Used by SQL Server Management Studio?
Answer: This is indeed a very interesting question for sure. I have been working as a consultant for a while and I have yet not heard this question in any of the interviews I have participated. The best part was that this question was actually not asked by interviewer by an interview candidate. At the end of the interview when we asked if he has any question or not, he said he would love to know the answer of this question if we know.
Well, let us see the answer in the simple most possible way.
To identify which sessions are consumed by SQL Server Management Studio, we can take help of sp_who2.
First run following query in SQL Server Management Studio
EXEC sp_who2
Once the result is displayed it, scroll down the resultset beyond SPID 50. Here you can check the column ProgramName. The column ProgramName will give display the name of the program which is consuming any particular SPID.
Here you will see either Microsoft SQL Server Management Studio or Microsoft SQL Server Management Studio – Query. Any Session ID used by SSMS will be prefixed by SQL Server Management Studio. Let us me know if there are any other way to answer this question. I will be interested to know your solution to identify session as well.
Reference:Â Pinal Dave (https://blog.sqlauthority.com)
