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



  • I want to create an alert like the ones from Brent Ozar's https://www.brentozar.com/blitz/configure-sql-server-alerts/ , but I couldn't find a message related to the error I want to monitor on https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/messages-for-errors-catalog-views-sys-messages?view=sql-server-ver15 as the https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-add-alert-transact-sql?view=sql-server-ver15 doc says and the problem I want to monitor didn't generate any logged error. If I just use https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-addmessage-transact-sql?view=sql-server-ver15 , 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 ( https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-user-connections-server-configuration-option?view=sql-server-ver15 ). 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.



  • Following the instructions of both https://dba.stackexchange.com/users/52344/amtwo and https://dba.stackexchange.com/users/151544/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 https://am2.co/2017/12/alerting-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:
    https://github.com/amtwo/dba-database/blob/production/functions-scalar/dbo.EmailCss_Get.sql
    https://github.com/amtwo/dba-database/blob/production/functions-scalar/dbo.EmailServerInfo_Get.sql




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2