1

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.

2 Answers 2

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.

4
  • Thanks for your reply, Tibor Karaszi. If I got it right, you're suggesting the email to be sent by the application itself when it cannot connect to SQL Server. If that's the case, it won't be a trustful approach on my environment since it would require the developers to change their application (both new and old applications). I can't rely on their actions, so I prefer to monitor that on SQL Server side where I have control. But if that's not what you meant, could you explain your method a bit more, please?
    – Ronaldo
    Apr 26, 2022 at 17:30
  • 2
    @Ronaldo -- you can have a job that checks the number of connections & sends an email server-side. But as Tibor said, you're better off just writing that code than trying to squeeze this into the Agent Alerts mechanism.
    – AMtwo
    Apr 26, 2022 at 18:40
  • 1
    Exactly, what AMtwo said. If SQL Server doesn't deliver an error when this happens, and you can't connect, then you have to capture the problem situation before you can't connect. There's a perfmon counter for number of connections (methink), so you can have an Agent perf alert, or some other mechanism to yourself check this before the problem occurs. And, consider if there is some issue that need to be fixed at the client side! Apr 27, 2022 at 10:51
  • I aborted the Agent Alerts method and wrote the job I just added as another answer here. Thanks, @AMtwo and Tibor Karaszi.
    – Ronaldo
    Apr 27, 2022 at 18:59
3

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 = '[email protected]',
        @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()

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.