I need the number of connections for a specific database

Question

Post reply

I need the number of connections for a specific database

Lonnie Johnson

SSC Enthusiast

Points: 156
More actions
October 3, 2008 at 9:13 am

#123895

What script do I use to find the number of users in a specifc database?
I found this in another post but I believe it shows ALL connections.
Select * from master.dbo.sysperfinfo where counter_name = 'User connections'
Thanks a bunch.
Lonnie Johnson
May God Bless You Beyond Your Imagination
Prodev

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

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

Clive Strong SSChampion Points: 11222 More actions · Answer 1

SELECT SD.[Name], COUNT(1) AS [Users]

FROM master.dbo.sysdatabases SD INNER JOIN master.dbo.sysprocesses SP

ON SD.dbid = SP.dbid

GROUP BY SD.[Name]

David Benoit SSC-Dedicated Points: 34562 More actions · Answer 2

...or,

select db_name(dbid), count(*) from sys.sysprocesses

group by db_name(dbid)

David

@SQLTentmaker

“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

Clive Strong SSChampion Points: 11222 More actions · Answer 3

DavidB (10/3/2008)
...or,
select db_name(dbid), count(*) from sys.sysprocesses
group by db_name(dbid)

Nice, no wonder I could not find the sysprocesses table. Tried seperating it to sys.processes.

Lonnie Johnson SSC Enthusiast Points: 156 More actions · Answer 4

Thanks guys. That did it. I am moving on.

Hey, would anyone have script to force or clear those users from that database? Thanks again. I actually did this. I am writing an windows program.

Dim strSQL As String = "SELECT db_name(dbid) as DatabaseName, count(dbid) as NoOfConnections, " _

& "loginame as LoginName " _

& "FROM sysprocesses " _

& "WHERE dbid > 0 and db_name(dbid) = '" & Me.ddlDatabases.Text & "' " _

& "GROUP BY dbid, loginame"

Lonnie Johnson
May God Bless You Beyond Your Imagination
Prodev

Lonnie Johnson SSC Enthusiast Points: 156 More actions · Answer 5

I found the script to kill the users. Thanks.

Lonnie Johnson
May God Bless You Beyond Your Imagination
Prodev

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 6

lcjohnson (10/3/2008)
I found the script to kill the users. Thanks.

Use with great caution! If you hit the wrong SPID, you can tank the server or some critical poorly written sproc that can't withstand a kill. If you just want to get all the users out of a server so you can do maintenance, consider setting the database to "Single User" and then back to normal, instead. Still and again, use with great caution. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)