Automated collection of SQL Server database connections for monitoring


By:   |   Updated: 2014-03-28   |   Comments (4)   |   Related: More > Monitoring


Is the Database the Culprit of Your Application Issues?

Free MSSQLTips Webinar: Is the Database the Culprit of Your Application Issues?

When you're troubleshooting application performance issues, have you ever found the problem residing a few layers deep in the database or not at all? How long did it take you to find and fix the issue? Don't worry if you said "a while". Learn how to solve performance problems fast.


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 @[email protected], @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 Updated: 2014-03-28


get scripts

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





Comments For This Article




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



download





Recommended Reading

How to Read Log File in SQL Server using TSQL

How to setup SQL Server alerts and email operator notifications

SQL Server Wait Stats Monitoring with PowerShell

Posting SQL Server Notifications to Slack

SQL Server High CPU Query Use Monitoring with PowerShell








get free sql tips
agree to terms


Learn more about SQL Server tools