Automated collection of SQL Server database connections for monitoring

By:   |   Comments (5)   |   Related: > Monitoring


We have quite a large number of databases and user connections to our SQL Server and we suspect that one application does not close connections properly. We need an automated way to monitor when this happens in order to contact developers to fix this issue.


In order to collect connection information, we will setup a SQL Server Agent alert that will execute a job every time there is more than N number of connections. We will use 50 connections in our example to simplify the test. The job then will populate a table with information about applications and connections.

Create a table to store monitoring data

First, we will create the table that will be populated by the job:

CREATE TABLE perf_warehouse.dbo._demo_sessions_alert(
 [host_name] nvarchar(128) NULL,
 [program_name] nvarchar(128) NULL,
 login_name nvarchar(128) NULL,
 num_sessions int NULL,
 capture_time datetime NULL

Create the SQL Server Agent Job

Create the SQL Server job with the following script as a step:

INSERT INTO perf_warehouse.dbo._demo_sessions_alert
SELECT  [host_name], 
 count(c.session_id ) num_sessions, 
 FROM sys.dm_exec_connections c JOIN 
      sys.dm_exec_sessions s on c.session_id = s.session_id
GROUP BY host_name, program_name, login_name ORDER BY 4 DESC

You can capture all connections or only the top N ("SELECT TOP 10 [host_name],...").

Here is the complete script that you can use to create the job:

SELECT @ReturnCode = 0
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories 
    WHERE name=N'DBA' AND category_class=1)
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'DBA'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Sessions Monitoring', 
 @description=N'Inserts log records when number of connections is higher than 50', 
 @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Log info', 
  @os_run_priority=0, @subsystem=N'TSQL', 
  @command=N'INSERT INTO perf_warehouse.dbo._demo_sessions_alert 
SELECT host_name, program_name, login_name, count(c.session_id ) num_sessions, getdate()
FROM sys.dm_exec_connections c JOIN sys.dm_exec_sessions s on c.session_id = s.session_id
GROUP BY host_name,program_name,login_name ORDER BY 4 DESC', 
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
GOTO EndSave

Create a SQL Server Agent alert

Now we will create a performance condition alert that will be triggered when there are more than 50 connections (with 2 minute intervals). This alert will start the job we created above:

Alert setup

Alert setup

Alert setup

Here is the complete script to create the alert (you will need to update parameter "@operator_name" with your value):

EXEC msdb.dbo.sp_add_alert @name=N'Perf: General Statistics: User Connections', 
 @performance_condition=N'SQLServer:General Statistics|User Connections||>|50', 
 @job_name=N'Sessions Monitoring'
EXEC msdb.dbo.sp_add_notification @alert_name=N'Perf: General Statistics: User Connections', 
 @operator_name=N'DBA_Operator', -- update with your value
 @notification_method = 1

Testing the Alert

Now we should be able to receive email notifications when there are more than 50 server connections and we can review the log table that contains the details:

SELECT TOP 5 [host_name]
  FROM [dbo].[_demo_sessions_alert]
  ORDER BY [capture_time] DESC, [num_sessions] DESC

Below is the output and we can see the application and host that had the largest number of connections:

Review results

A value of 50 connections could be low for your server and you may get a lot of alerts. For our issue to track applications not closing connections we used a value of 1000 connections.

Next Steps
  • Use this tip to monitor your connections and to get a baseline for the number of connections your SQL Server usually has.
  • After collecting some data, adjust your monitored number of connections in the alert (for example add 100 to the maximum connections in your baseline).
  • Read this tip about How to Automate SQL Server Monitoring with Email Alerts.
  • Read this tip about How to setup SQL Server alerts and email operator notifications.

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Svetlana Golovko Svetlana Golovko is a DBA with 13 years of the IT experience (including SQL Server and Oracle) with main focus on performance.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Monday, April 3, 2023 - 12:53:28 PM - Jean Luc Back To Top (91079)
Hi Svetlana,

I have successfully implemented your suggestion but now I need to go the extra mile. That is knowing more about those connections or better said which resources (running queries) are being accessed. I have been suggested to investigate the Extended Events feature but so far, not so good. Any idea?

Tuesday, June 2, 2015 - 7:28:56 AM - Konstantin Back To Top (37353)

Very useful post i was wondering from a long time how to keep logs of the comming connections on servers that might no longer been in use.

Sunday, July 13, 2014 - 6:53:13 PM - Svetlana Golovko Back To Top (32682)

Hi Ricardo,


I would probably use Windows performance alerts as described in this Microsoft article:

Then I would modify the Data Collector property and add under the "Alert Task" SQL Server script (started with sqlcmd.exe). The script will query the SQL Server instance activity and insert records to the history table.


I hope this helps.



Thursday, July 10, 2014 - 9:33:22 AM - Ricardo Back To Top (32630)


Great post, I wonder how in addition to monitor the number of connections receive alert procedures with high consumption of CPU and Disk

Friday, March 28, 2014 - 10:48:00 AM - Edward Pochinski Back To Top (29912)

Very nicely done, I'm old school use to load performance data like this into a perfmon database like below then have a few stored procedures to surf the data and send alerts. This is one of the many objects loaded.: The below was containerd in a .vbs script a called from a SQL agent jop step

Set objConn = CreateObject("ADODB.Connection")
Set objRS = CreateObject("ADODB.Recordset")
objConn.Open  "PROVIDER=SQLOLEDB;DATA SOURCE=sqlsharkpc;UID=loginid;PWD=sqldba;DATABASE=perfMon "
objRS.CursorLocation = 3
objRS.Open "SELECT * FROM Re_Compiles" , objConn, 3, 3
strComputer = "."
Set objWMIService = GetObject("winmgmts:" _
    & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
Set colRetrievedEvents = objWMIService.ExecQuery _
    ("Select * from Win32_PerfRawData_MSSQLSERVER_SQLServerSQLStatistics",,48)
For Each objEvent in colRetrievedEvents
    objRS("Compiles") = objEvent.SQLReCompilationsPersec

get free sql tips
agree to terms