CODEWITHSUNDEEP
c#sqlsql-serverconnection-string
DooDoo
DooDoo

Reputation: 13447

Get Number of open connection string in my application

I'm programming an application with C# and SQL Server 2008. How can I get the number of opened connections that are not already closed?

Also if I open a connection with 20 minute timeout, and do not close it - will it closed after 20 minutes?

Upvotes: 3

Views: 2185

Answers (2)

davidsleeps
davidsleeps

Reputation: 9503

You should be able to use PerfMon SQL Server counters against the SQL Server instance to monitor open connections to the server external to your application...particularly as your application (likely) is abstracted from specific connections, definitely if you are using Connection Pooling

Here is a quick article on someone demonstrating the problem and monitoring it

Upvotes: 1

sam
sam

Reputation: 685

This shows the no of connections per each DB:

SELECT 
    DB_NAME(dbid) as DBName, 
    COUNT(dbid) as NumberOfConnections,
    loginame as LoginName
FROM
    sys.sysprocesses
WHERE 
    dbid > 0
GROUP BY 
    dbid, loginame

And this gives the total:

SELECT 
    COUNT(dbid) as TotalConnections
FROM
    sys.sysprocesses
WHERE 
    dbid > 0

If you need more detail, run:

sp_who2 'Active'

Upvotes: 4

Related Questions