SQL Talent Uncategorized SQLSessions

SQLSessions

May 2, 2025May 2, 2025|

SQL Server Session Handling and Monitoring

 

Monitoring SQL Server sessions helps DBAs track user activity, identify session growth trends, and troubleshoot issues related to connection leaks or poor application behavior.

Purpose

The goal of this script is to extract and present key configuration details of the SQL Server sessions, including:

Step 1: Create the Session Tracking Table

We first create a table in the msdb database to store session data collected from sys.dm_exec_sessions.

SQL Script:

USE [msdb];
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SQLSession](
[session_id] [smallint] NOT NULL,
[host_name] [nvarchar](128) NULL,
[login_name] [nvarchar](128) NOT NULL,
[login_time] [datetime] NOT NULL,
[DBName] [nvarchar](128) NULL,
[status] [nvarchar](30) NOT NULL,
[Program_name] [nvarchar](128) NULL,
[client_interface_name] [nvarchar](32) NULL,
[cpu_time] [int] NOT NULL,
[memory_usage] [int] NOT NULL,
[last_request_start_time] [datetime] NOT NULL,
[last_request_end_time] [datetime] NULL,
[ReportAsOn] [datetime] NOT NULL
) ON [PRIMARY]
GO

This table captures active session details with a timestamp (ReportAsOn) to support historical analysis.

Step 2: Create SQL Agent Job to Log Sessions Periodically

This SQL Agent job:

  • Collects session data every hour
  • Deletes data older than 7 days
  • Shrinks the MSDB log file to manage space

SQL Script:

USE [msdb]
GO

/****** Object:  Job [DB_SQLSessions]   ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [Data Collector]     ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Data Collector' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Data Collector'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'DB_SQLSessions', 
		@enabled=1, 
		@notify_level_eventlog=0, 
		@notify_level_email=0, 
		@notify_level_netsend=0, 
		@notify_level_page=0, 
		@delete_level=0, 
		@description=N'By DBA', 
		@category_name=N'Data Collector', 
		@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [DB_SQLSessions]    Script Date: 5/2/2025 6:57:12 PM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'DB_SQLSessions', 
		@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'USE msdb 
go
Insert into msdb.dbo.SQLSession 
select 
	session_id, 
	host_name,
	login_name, 
	login_time, 
	DB_NAME(database_id) DBName ,
	status, 
	Program_name,
	client_interface_name , 
	cpu_time, memory_usage, 
	last_request_start_time, 
	last_request_end_time, 
	getdate() as ReportAsOn
from sys.dm_exec_sessions 
where host_name is not null
go 
USE [msdb]
GO
delete msdb.dbo.SQLSession where convert(varchar(10),ReportAsOn,112) < convert(varchar(10),getdate()-7,112)
go
USE [msdb]
GO
DBCC SHRINKFILE (N''MSDBLog'' , 0, TRUNCATEONLY)
GO', 
		@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_jobschedule @job_id=@jobId, @name=N'DB_SQLSessions', 
		@enabled=1, 
		@freq_type=4, 
		@freq_interval=1, 
		@freq_subday_type=8, 
		@freq_subday_interval=1, 
		@freq_relative_interval=0, 
		@freq_recurrence_factor=0, 
		@active_start_date=20190627, 
		@active_end_date=99991231, 
		@active_start_time=0, 
		@active_end_time=65959
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

The job runs hourly and stores point-in-time snapshots of SQL sessions.

Session Analysis Queries

View Current Session Logs

SQL Script:

USE [msdb] 
GO
SELECT TOP 10000 *
FROM [msdb].[dbo].[SQLSession]
ORDER BY ReportAsOn DESC;

Analyze Session Count by Date and Hour

SQL Script:

USE [msdb] 
GO
SELECT 
CAST(ReportAsOn AS DATE) AS ReportDate,
DATEPART(HOUR, ReportAsOn) AS ReportHour,
COUNT(*) AS SessionCount
FROM [msdb].[dbo].[SQLSession]
GROUP BY 
CAST(ReportAsOn AS DATE),
DATEPART(HOUR, ReportAsOn)
ORDER BY 
ReportDate DESC, ReportHour DESC;

Database Context to Hourly Grouping

SQL Script:

USE [msdb] 
GO
SELECT 
DBName,
CAST(ReportAsOn AS DATE) AS ReportDate,
DATEPART(HOUR, ReportAsOn) AS ReportHour,
COUNT(*) AS SessionCount
FROM [msdb].[dbo].[SQLSession]
GROUP BY 
DBName,
CAST(ReportAsOn AS DATE),
DATEPART(HOUR, ReportAsOn)
ORDER BY 
ReportDate DESC, ReportHour DESC, DBName;

Troubleshoot High Session Counts

Use this query to detect which applications or users are generating the most sessions:

SQL Script:

USE [msdb] 
GO
SELECT 
program_name,
host_name,
login_name,
COUNT(*) AS SessionCount
FROM sys.dm_exec_sessions
WHERE is_user_process = 1
GROUP BY program_name, host_name, login_name
ORDER BY SessionCount DESC;

Common Causes of Increasing SQL Sessions

Cause Description Recommendation
App Not Closing Connections Poorly written apps leave connections open Ensure proper Dispose() or using blocks in code
Connection Pool Leaks Connection pools are mismanaged Review and configure pooling behavior in app/ORM
High Web Traffic Frequent short connections flood SQL Server Tune idle timeouts, pool reuse settings
Idle or Long-running Sessions Sessions remain open due to unfinished work Use timeouts, kill idle sessions, optimize queries
Monitoring Jobs or Tools Tools may create sessions regularly Identify tools via program_name and tune schedule
Agent Jobs or Background ETLs Background processes create periodic sessions Audit scheduled jobs and assess frequency

Summary

This solution offers a lightweight, effective way to:

  • Capture SQL Server session data
  • Retain historical logs for analysis
  • Troubleshoot session spikes
  • Proactively manage SQL Server load

 

Loading

Leave a Reply

Your email address will not be published. Required fields are marked *