SQL Agent job to email alert active sessions?

Question

Hello, would like to setup a job to send us email alerts if particular DB(s) have like more than 10 active sessions.

Could someone provide/point me to a script that can do this?

Thanks in advance.

Answer 1

Hi techresearch7777777-7743,

After some tests, it seems the email alert in the SQL Server agent can be triggered for user connections of SQL Server instead of the user connection of the specified database.
You could try to create a logon trigger for limiting the number of sessions.
For example:

CREATE TRIGGER Limit_Session_trigger
ON ALL SERVER 
FOR LOGON
AS
BEGIN
Declare @dbName varchar(150)
set @dbName = 'DATABASENAME'

DECLARE @SPWHO1 TABLE (DBName VARCHAR(1000) NULL, NoOfAvailableConnections VARCHAR(1000) NULL)
INSERT INTO @SPWHO1 
    SELECT db_name(dbid), count(dbid) FROM sys.sysprocesses WHERE dbid > 0 GROUP BY dbid 
IF (SELECT NoOfAvailableConnections FROM @SPWHO1 WHERE DBName = @dbName )>10 
    ROLLBACK;  
END;

Hope it helps.

Best Regards,
Amelia

Answer 2

Thanks AmeliaGu-msft for your reply and sorry for the delay.

Having new specs is there a script/functionality to email me if a SQL Server instance has the following criteria:

Greater than 10 connections where each of the 10 connections are in RUNNABLE or SUSPENDED Status?

Regards.