Current connections to SQL Server

There are a few system functions for returning the number of connections but,they are many times misunderstood.

@@CONNECTIONS : Returns login attempts since the server was started.
@@MAX_CONNECTIONS: Returns maximum number of connections supported.

The simplest way to list all users and processes is to use the system stored procedure: sp_who
To list all active users: sp_who ‘active’
to list details about a particular user: sp_who ‘login-name’
Now to obtain the current number of connections to the server, you can use:

SELECT COUNT(*) AS CONNECTIONS FROM master..sysprocesses

And just to get the user connections, omitting the system processes, use:

SELECT cntr_value AS User_Connections FROM master..sysperfinfo as p
WHERE p.object_name = ‘SQLServer:General Statistics’ And p.counter_name = ‘User Connections’

To see current number of connection for each db in overall:
SELECT db_name(dbid) AS db, count(dbid) AS connection FROM sys.sysprocesses WHERE dbid > 0 GROUP BY dbid

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s