How can I find out how many SQL Connections I open in a Windows service?

Question

I see some errors that indicate a "connection leak." That is, connections that were not closed properly, and the pool has ended. So, how do I do this to understand how much is open at the moment?

Answer 1

If you use .net, there is a .net data provider for SQL server in PerfMon. You can look at NumberOfPooledConnections there

Answer 2

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

Answer 3

Implement a service in which all connections are created, opened, and closed. Keep the counter there. Log into the registration system every time you open or close a connection.

Answer 4

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

You can open the profiler from the enterprise manager

Answer 5

If you are using SQL 2000, you can check SQL 2000 Enterprise Manager:

To view the current SQL Server Enterprise Manager activity window, expand the server groups and then the server. Expand Management, and then expand Current Activity. Click "Process" Information.

Current server activity is displayed in the details pane.

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

(From a Google search: current sql 2000 activity)

Answer 6

You can run sp_who2 in SQL Server Management Studio or Query Analyzer to see all your current connections. This is SQL Server. I'm not sure which RDBMS you are using.

Also, take a look at your code and make sure you close the connection as soon as you no longer need it. Be anal in this!

Answer 7

Use the "using" statement to verify that your connections are always closed and this problem will not occur again:

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