Monitor SQL Connections

If you have no monitoring tools a nice way to keep track of your connections to sql server for baseline purposes is to have a sql agent job that runs every x minutes and logs into a table within your dba database.

Below is the code to create the table and the also the code to execute in your sql agent job.

 

 

CREATE TABLE [dbo].[yourTableName](
[login_name] [varchar](50) NULL,
[program_name] [varchar](100) NULL,
[Session_Count] [int] NULL,
[Date] [datetime] NULL
) ON [PRIMARY]
GO

 

INSERT INTO yourTable
SELECT login_name, [program_name], COUNT(session_id) AS [session_count],getdate() AS[Date]
FROM sys.dm_exec_sessions WITH (NOLOCK)
where login_name like ‘yourLoginName‘ OR login_name like ‘yourLoginName‘
GROUP BY login_name, [program_name]
ORDER BY COUNT(session_id) DESC OPTION (RECOMPILE);