active users and concurrent users

Question

active users and concurrent users

crazylifescary

SSC Eights!

Points: 922
More actions
September 22, 2011 at 4:30 pm

#248356

Hello all, i am new to sql server dba and have been a oracle guy. Is there a view that i can query to get how many active users i have connected to my database right now (i know i can start active monitor tool) but looking for a way to query it
And also is there a view or anything where i can get number of concurrent user or lets says max number of user ever connected to my database in the past ??

Viewing 6 posts - 1 through 5 (of 5 total)

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

bkubicek SSChampion Points: 10948 More actions · Answer 1

abdul.irfan2 (9/22/2011)
Hello all, i am new to sql server dba and have been a oracle guy. Is there a view that i can query to get how many active users i have connected to my database right now (i know i can start active monitor tool) but looking for a way to query it
And also is there a view or anything where i can get number of concurrent user or lets says max number of user ever connected to my database in the past ??

There is a stored procedure called sp_who or sp_who2. This will show you all the active spids on your sql server. One of the columns that is returned is the username. Several people have written some custom sp_who type procs that do a lot of different things, such as showing what sql is currently being executed.

Hope that helps.

crazylifescary SSC Eights! Points: 922 More actions · Answer 2

Thanks for the info,

what about any historical data for number for user that logged on ?

bkubicek SSChampion Points: 10948 More actions · Answer 3

abdul.irfan2 (9/22/2011)
Thanks for the info,
what about any historical data for number for user that logged on ?

I am not familiar with anything in sql server. Most of the stuff I have seen use sp_who2 to collect data and then put it into a historical data table over time. Still, there might be some place that it is kept that I don't know about.

Shazme Ten Centuries Points: 1292 More actions · Answer 4

Hi Abdul,

I do use the below which I have done when asked to check for active user on a specific Db.

you might have to tweak a bit.

USE [DataBaseName]

select suser_sname(SID) as ServerLevelLogin,

name as DatabaseLevelUser,

principal_id,

type_desc,

create_date,

modify_date

from sys.database_principals

where principal_id >= 5 and is_fixed_role <> 1

Thanks

Imran

asiaindian SSCrazy Points: 2493 More actions · Answer 5

Hello,

Please look up sys.dm_exec_sessions.

Run the query below to get all active users spid, their login names and other information.

SELECT * FROM sys.dm_exec_sessions

WHERE is_user_process =1

AND session_id <>@@SPID

Hope that helps.

Thanks,

Vasu