How can I create a brand new alarm based on a verification not listed by default on SQL Server?

Question

I want to create an alert like the ones from Brent Ozar's How to Configure SQL Server Agent Alerts, but I couldn't find a message related to the error I want to monitor on sys.messages as the sp_add_alert doc says and the problem I want to monitor didn't generate any logged error. If I just use sp_addmessage, I believe I'll still lack the verification that triggers the error. And if I create a job to do the verification, I don't need the alarm since the job could send the email itself.

What's the proper way to create a new alert?

Background

Recently the applications on my environment started failing to connect on SQL Server and it took us some time to notice that the problem was due to the instance reaching that 32k limit of the instance (SQL Server allows a maximum of 32,767 user connections). The connection eater was an app badly configured and the developer has corrected it now, but I don't wanna be surprised again because some other app caused the same situation, hence the need for the alert. An alert to notify when the connection number reaches a limit like 10K would be great, but just alerting if the 32K limit was hit would help.

Answer 1

You're not missing anything. An Agent event alert requires an error from the sql server instance in the event log. Agent polls the event log every 20 seconds. Very simple and straight forward.

So if you need to produce that error yourself (as in this case), you might as well send the email from that code instead of taking the event alert detour.

Answer 2

Following the instructions of both AMtwo and Tibor Karaszi I decided to create an Agent job that verifies if a threshold was crossed and, if yes, would report the top 10 apps consuming the most connections. I can get the info I need with this:

--The number of connections to be considered abnormal for your environment.
--It should be bellow SQL Server max supported 32,767 user connections
DECLARE @ConnectionThreshold int = 1000;

IF @ConnectionThreshold < (SELECT COUNT(*) FROM sys.dm_exec_connections)

--List the top 10 apps using connections
SELECT TOP(10)
    s.login_name    AS [Login name],
    s.host_name     AS [Host name],
    d.name          AS [Database name],
    s.program_name  AS [Program name],
    COUNT(*)        AS [Connections]
FROM sys.dm_exec_connections AS c  
    INNER JOIN sys.dm_exec_sessions AS s  
        ON c.session_id = s.session_id
    INNER JOIN sys.databases AS d
        ON s.database_id = d.database_id
GROUP BY s.login_name,
    s.host_name,
    d.name,
    s.program_name
ORDER BY COUNT(*) DESC;

To generate a more informative and well formatted email I borrowed some ideas (not to say most of the code) from AMtwo's Alerting on SQL Server Blocking and the result was this:

--The number of connections to be considered abnormal for the environment.
--It should be bellow SQL Server max supported 32,767 user connections
DECLARE @ConnectionThreshold int = 1000;

IF @ConnectionThreshold < (SELECT COUNT(*) FROM sys.dm_exec_connections)
BEGIN
    DECLARE @EmailBody nvarchar(4000);
    SELECT @EmailBody = dbo.EmailCSS_Get();

    SELECT @EmailBody = @EmailBody + N'<h2>Top 10 apps connected:</h2>' + CHAR(10) +
            N'<table><tr>' +
            N'<th>Login name</th>' +
            N'<th>Host name</th>' +
            N'<th>Database name</th>' +
            N'<th>Program name</th>' +
            N'<th>Connections</th>' +
            N'</tr>' +
            CAST(( SELECT TOP(10)
                        td = s.login_name, '',
                        td = s.host_name, '',
                        td = d.name, '',
                        td = s.program_name, '',
                        td = COUNT(*), ''
                    FROM sys.dm_exec_connections AS c  
                        INNER JOIN sys.dm_exec_sessions AS s  
                            ON c.session_id = s.session_id
                        INNER JOIN sys.databases AS d
                            ON s.database_id = d.database_id
                    GROUP BY s.login_name,
                        s.host_name,
                        d.name,
                        s.program_name
                    ORDER BY COUNT(*) DESC
                    FOR XML PATH ('tr'), ELEMENTS
                    ) AS nvarchar(max)) +
            N'</table>';

    SELECT @EmailBody = @EmailBody + '<hr>' + dbo.EmailServerInfo_Get();

    EXEC msdb.dbo.sp_send_dbmail  
        @profile_name = 'YourMailProfile',  
        @recipients = 'yourEmail@job.com',
        @subject = 'Number of connections above defined threshold',  
        @body = @EmailBody,
        @body_format = 'HTML';
END

This code was added to a job that runs at some interval and sends the alert in a formatted email if needed. It did the trick.

---

Referenced content:
dbo.EmailCSS_Get()
dbo.EmailServerInfo_Get()