How can I tell how many SQL Connections I have open in a windows service?

Question

I'm seeing some errors that would indicate a "connection leak". That is, connections that were not closed properly and the pool is running out. So, how do I go about instrumenting this to see exactly how many are open at a given time?

Answer 1

sp_who2 stored procedure in the master table is nice for this from a database side. It will show you connections to the database. If you're looking for more data try profiling as well.

Answer 2

Implement a service that all connections are created, opened and closed through. Hold a counter there. Log with your logging framework each time a connection is opened or closed.

Answer 3

you can use the profiler tool to trace all existing and opening and closing connections

You can open profiler from enterprise manager

Answer 4

If you're using .net, there's the .net data provider for SQL server in PerfMon. You can look at NumberOfPooledConnections there

Answer 5

If you're using SQL 2000, you can check in SQL 2000 Enterprise Manager:

To view the Current Activity window In SQL Server Enterprise Manager, expand a server group, and then expand a server. Expand Management, and then expand Current Activity. Click Process Info.

The current server activity is displayed in the details pane.

(http://technet.microsoft.com/en-us/library/cc738560.aspx)

(From Google search: sql 2000 current activity)

Answer 6

You could run sp_who2 in SQL Server Management Studio or Query Analyser to see all of your curent connections. That is SQL Server. I'm not sure which RDBMS that you are using.

Also, look in your code and make sure that you close a connection as soon as you don't need it anymore. Be anal about this!

Answer 7

Use the "using" statement to ensure your connections are always closed and you'll never have this problem again:

using(SqlConnection connection = new SqlConnection())
{
...
} // connection is always disposed (i.e. closed) here, even if an exception is thrown