view database connections in sql server
To view database connections in SQL Server you can query the sysprocesses table in master database. This is a SQL Server 2000 table which is included as view in 2005 for backward compatibility. The script will show total number of connections to each database including internal connections, if you want to view only external connections change the WHERE clause to add " AND spid > 51 " to view only external connections to each database.
If you are running SQL Server 2000 use this script.
SELECT DB_NAME(dbid) as 'Database Name',
COUNT(dbid) as 'Total Connections'
FROM master.dbo.sysprocesses WITH (nolock)
WHERE dbid > 0
GROUP BY dbid
If you are using SQL Server 2005 edition then use sys.sysprocesses to get the same results.
SELECT DB_NAME(dbid) as 'Database Name',
COUNT(dbid) as 'Total Connections'
FROM sys.sysprocesses WITH (nolock)
WHERE dbid > 0
GROUP BY dbid
There are two more important global variables related to connections.
To view the maximum number of concurrent users allowed in SQL Server, use the @@MAX_CONNECTIONS. In most cases if you have not modified the server configuration you should get 32767 as its the default value.
SELECT @@MAX_CONNECTIONS AS 'Max Allowed Connections'
There is another global variable @@CONNECTIONS which shows the total login attempts. The value of @@CONNECTIONS increases with each new login attempt.
SELECT @@MAX_CONNECTIONS AS 'Max Allowed Connections'
The image of results from above TSQL scripts.
As a general practice please make sure you test the T-SQL Scripts on development or sandbox enviorment before running them on production database.