USER CONNECTION

Question

Post reply

USER CONNECTION

venkateswarlu.kakuru

Grasshopper

Points: 13
More actions
April 23, 2009 at 11:05 pm

#155600

I WANT TO KNOW TO FIND HOW MANY USERS ARE PRESENTLY
CONNECT TO THE SQLSERVER

Viewing 9 posts - 1 through 8 (of 8 total)

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

VastSQL SSC-Dedicated Points: 36097 More actions · Answer 1

Use select * from sysprocesses.

U can also use sp_who2 stored procedures which is an undocumented one.

manoj2001 SSCrazy Points: 2225 More actions · Answer 2

you can use below query:

select spid, status, loginame, hostname, blocked, db_name(dbid), cmd from master..sysprocesses where db_name(dbid) = 'yourdb'

Manoj

BLADE-1043594 SSCrazy Points: 2576 More actions · Answer 3

Use sp_who2

[font="Comic Sans MS"]+++BLADE+++[/font]:cool:

Rajesh kasturi SSCarpal Tunnel Points: 4007 More actions · Answer 4

As manoj said, query is more efficiant than sp_who or sp_who2

select spid, status, loginame, hostname, blocked, db_name(dbid), cmd from master..sysprocesses where db_name(dbid) = 'yourdb'

Rajesh Kasturi

matthew.peters Ten Centuries Points: 1138 More actions · Answer 5

Hello

I have been monitoring the following counter SQLServer: General Statistics\User Connections through performance monitor but the user connection count is lower than the number of rows returned by sysprocesses, sp_who or sp_who???

sachnam SSCrazy Points: 2317 More actions · Answer 6

matthew.peters (5/8/2011)
Hello
I have been monitoring the following counter SQLServer: General Statistics\User Connections through performance monitor but the user connection count is lower than the number of rows returned by sysprocesses, sp_who or sp_who???

sp_who or sp_who2 returns the details for all the connections to sql server instance. These SQL server connection are generally Categorised in 2 parts 1. Sql server connections(Backgroud processes)

2. User Connections. Generally spids <50 denotes System connection.

So, when you use SQLServer: General Statistics\User Connections through performance monitor

It only returns user connections on the Instance.

To get the user connection details on any instance you could also run.

Select * from Master..sysprocesses where spid >50

I hope it is understood now..

Regards,

Sachin

sachnam SSCrazy Points: 2317 More actions · Answer 7

Rajesh kasturi (4/24/2009)
As manoj said, query is more efficiant than sp_who or sp_who2
select spid, status, loginame, hostname, blocked, db_name(dbid), cmd from master..sysprocesses where db_name(dbid) = 'yourdb'

Sorry but i contradict on this statement, the query has nothing to do with what sp_who or sp_who2 returns.

Infact, you have modified the query based on your requirement.

The OP said he wants the list of all sql server connections but your query is confined to a specific database only. An instance could host more than 1 user database so running the above query could result only connection made on a specific user DB ,rather all the connections on the instance as requested by OP.

Correct me if i am wrong..

Regards,

Sachin

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 8

You can also check in the 'sys.dm_exec_sessions' view to get the details against the 'session_id' which is the SPID in the sysprocesses table!

Thanks.