Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Automated collection of SQL Server database connections for monitoring


By:   |   Read Comments (4)   |   Related Tips: More > Monitoring


Share your SQL Server knowledge and make some money too!


Problem

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.

Solution

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
) ON [PRIMARY]
GO

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], 
 [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

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:

BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories 
    WHERE name=N'DBA' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'DBA'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Sessions Monitoring', 
 @enabled=1, 
 @notify_level_eventlog=0, 
 @notify_level_email=2, 
 @notify_level_netsend=0, 
 @notify_level_page=0, 
 @delete_level=0, 
 @description=N'Inserts log records when number of connections is higher than 50', 
 @category_name=N'DBA', 
 @owner_login_name=N'sa', 
 @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', 
  @step_id=1, 
  @cmdexec_success_code=0, 
  @on_success_action=1, 
  @on_success_step_id=0, 
  @on_fail_action=2, 
  @on_fail_step_id=0, 
  @retry_attempts=0, 
  @retry_interval=0, 
  @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', 
  @database_name=N'master', 
  @flags=0
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
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO

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', 
 @message_id=0, 
 @severity=0, 
 @enabled=1, 
 @delay_between_responses=120, 
 @include_event_description_in=1,  
 @performance_condition=N'SQLServer:General Statistics|User Connections||>|50', 
 @job_name=N'Sessions Monitoring'
GO
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
GO

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]
      ,[program_name]
      ,[login_name]
      ,[num_sessions]
      ,[capture_time]
  FROM [dbo].[_demo_sessions_alert]
  ORDER BY [capture_time] DESC, [num_sessions] DESC
GO

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.


Last Update:


signup button

next tip button



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.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Tuesday, June 02, 2015 - 7:28:56 AM - Konstantin Back To Top

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

Hi Ricardo,

 

I would probably use Windows performance alerts as described in this Microsoft article: http://technet.microsoft.com/en-us/magazine/ff458614.aspx

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.

Thanks,

Svetlana


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

 

 
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

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.AddNew
    objRS("Compiles") = objEvent.SQLReCompilationsPersec
    objRS.Update
Next
objRS.Close
objConn.Close


Learn more about SQL Server tools