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.

SQL Server active connections by database.

As a general practice please make sure you test the T-SQL Scripts on development or sandbox enviorment before running them on production database.



 4/9/2008


Add Comments
Name: *
Email: *
URL:
Comments: *
 


Advertisements

MySQL HOSTING
MySQL hosting provider, clustering and replication supported. DBA Services Included.

SQL SERVER HOSTING
Reliable, powerful SQL Server 2008 hosting with ASP.NET on Windows 2008 Servers.

SQL Server DBA

I am a SQL Server DBA with almost 9 years of experience in database technologies.

 I am again in process of redesigning this website using ASP.NET 2.0 and AJAX. The main purpose of new SQL DBA website is to offer more features and make it easy for me to update contents on regular basis.