Alerts 19-25

In SQL Server, Alerts are used to monitor specific events or errors that occur within the SQL Server engine, and the severity levels 19 through 25 are the most critical ones. These alerts indicate an internal error has occurred, making the continued operation of the database unlikely. These are some common alerts for severity levels 19-25:

• Severity 19: Indicates a non-configurable fatal runtime error has occurred. This error results in the termination of the client connection or SQL Server process.
  

• Severity 20: Indicates that a system malfunction has occurred, and the execution of the current query or stored procedure cannot continue. This severe level will trigger a fatal error and leads to SQL Server shutdown.
  

• Severity 21: Generated when SQL Server fails to retrieve memory needed to perform an operation, and the execution of the current query or stored procedure cannot continue; however, the database can continue to function.
  

• Severity 22: Indicates that the database is being shut down due to a serious internal error.
  

• Severity 23-25: Indicates that the database has run out of resources or system issues and, as a result, must shut down immediately.

When an alert is triggered in SQL Server, they can be configured to perform specific actions like sending an email, executing a job, or running a PowerShell script. Alert monitoring can help to prevent downtime, optimize performance, and reduce technical debt by allowing database administrators to respond to critical issues and resolve them proactively.

How To Setup Alerts T-SQL

USE [msdb] GO EXEC msdb.dbo.sp_add_alert @name=N'Severity 016', @message_id=0, @severity=16, @enabled=1, @delay_between_responses=60, @include_event_description_in=1, @job_id=N'00000000-0000-0000-0000-000000000000'; GO EXEC msdb.dbo.sp_add_notification @alert_name=N'Severity 016', @operator_name=N'The DBA Team', @notification_method = 7; GO EXEC msdb.dbo.sp_add_alert @name=N'Severity 017', @message_id=0, @severity=17, @enabled=1, @delay_between_responses=60, @include_event_description_in=1, @job_id=N'00000000-0000-0000-0000-000000000000'; GO EXEC msdb.dbo.sp_add_notification @alert_name=N'Severity 017', @operator_name=N'The DBA Team', @notification_method = 7; GO EXEC msdb.dbo.sp_add_alert @name=N'Severity 018', @message_id=0, @severity=18, @enabled=1, @delay_between_responses=60, @include_event_description_in=1, @job_id=N'00000000-0000-0000-0000-000000000000'; GO EXEC msdb.dbo.sp_add_notification @alert_name=N'Severity 018', @operator_name=N'The DBA Team', @notification_method = 7; GO EXEC msdb.dbo.sp_add_alert @name=N'Severity 019', @message_id=0, @severity=19, @enabled=1, @delay_between_responses=60, @include_event_description_in=1, @job_id=N'00000000-0000-0000-0000-000000000000'; GO EXEC msdb.dbo.sp_add_notification @alert_name=N'Severity 019', @operator_name=N'The DBA Team', @notification_method = 7; GO EXEC msdb.dbo.sp_add_alert @name=N'Severity 020', @message_id=0, @severity=20, @enabled=1, @delay_between_responses=60, @include_event_description_in=1, @job_id=N'00000000-0000-0000-0000-000000000000'; GO EXEC msdb.dbo.sp_add_notification @alert_name=N'Severity 020', @operator_name=N'The DBA Team', @notification_method = 7; GO EXEC msdb.dbo.sp_add_alert @name=N'Severity 021', @message_id=0, @severity=21, @enabled=1, @delay_between_responses=60, @include_event_description_in=1, @job_id=N'00000000-0000-0000-0000-000000000000'; GO EXEC msdb.dbo.sp_add_notification @alert_name=N'Severity 021', @operator_name=N'The DBA Team', @notification_method = 7; GO EXEC msdb.dbo.sp_add_alert @name=N'Severity 022', @message_id=0, @severity=22, @enabled=1, @delay_between_responses=60, @include_event_description_in=1, @job_id=N'00000000-0000-0000-0000-000000000000'; GO EXEC msdb.dbo.sp_add_notification @alert_name=N'Severity 022', @operator_name=N'The DBA Team', @notification_method = 7; GO EXEC msdb.dbo.sp_add_alert @name=N'Severity 023', @message_id=0, @severity=23, @enabled=1, @delay_between_responses=60, @include_event_description_in=1, @job_id=N'00000000-0000-0000-0000-000000000000'; GO EXEC msdb.dbo.sp_add_notification @alert_name=N'Severity 023', @operator_name=N'The DBA Team', @notification_method = 7; GO EXEC msdb.dbo.sp_add_alert @name=N'Severity 024', @message_id=0, @severity=24, @enabled=1, @delay_between_responses=60, @include_event_description_in=1, @job_id=N'00000000-0000-0000-0000-000000000000'; GO EXEC msdb.dbo.sp_add_notification @alert_name=N'Severity 024', @operator_name=N'The DBA Team', @notification_method = 7; GO EXEC msdb.dbo.sp_add_alert @name=N'Severity 025', @message_id=0, @severity=25, @enabled=1, @delay_between_responses=60, @include_event_description_in=1, @job_id=N'00000000-0000-0000-0000-000000000000'; GO EXEC msdb.dbo.sp_add_notification @alert_name=N'Severity 025', @operator_name=N'The DBA Team', @notification_method = 7; GO EXEC msdb.dbo.sp_add_alert @name=N'Error Number 823', @message_id=823, @severity=0, @enabled=1, @delay_between_responses=60, @include_event_description_in=1, @job_id=N'00000000-0000-0000-0000-000000000000' GO EXEC msdb.dbo.sp_add_notification @alert_name=N'Error Number 823', @operator_name=N'The DBA Team', @notification_method = 7; GO EXEC msdb.dbo.sp_add_alert @name=N'Error Number 824', @message_id=824, @severity=0, @enabled=1, @delay_between_responses=60, @include_event_description_in=1, @job_id=N'00000000-0000-0000-0000-000000000000' GO EXEC msdb.dbo.sp_add_notification @alert_name=N'Error Number 824', @operator_name=N'The DBA Team', @notification_method = 7; GO EXEC msdb.dbo.sp_add_alert @name=N'Error Number 825', @message_id=825, @severity=0, @enabled=1, @delay_between_responses=60, @include_event_description_in=1, @job_id=N'00000000-0000-0000-0000-000000000000' GO EXEC msdb.dbo.sp_add_notification @alert_name=N'Error Number 825', @operator_name=N'The DBA Team', @notification_method = 7; GO

Setting Up Operators

SQL Agent operators are used to define the recipients for alerts and notifications generated by SQL Server Agent jobs. Here are the steps to create SQL Agent operators:

1. Open SQL Server Management Studio and connect to the database.

2. Expand the "SQL Server Agent" node in the Object Explorer and right-click on the "Operators" folder.

3. Select "New Operator" to open the "New Operator" dialog box.

4. In the "General" section, enter the name of the operator in the "Name" field.

5. Enter the email address of the operator in the "E-mail name" field. You can add multiple email addresses by separating them with semicolons.

6. In the "Pager" section, enter the pager email address of the operator (if applicable).

7. In the "Net send" section, enter the computer name of the operator (if applicable).

8. Click "OK" to create the operator.

Once the operator is created, you can assign them to SQL Server Agent jobs to receive alerts and notifications. Here are the steps to assign operators to SQL Server Agent jobs:

1. Right-click on the SQL Server Agent job in the Object Explorer and select "Properties".

2. Select the "Notifications" tab.

3. Select the "E-mail" checkbox and select the operator from the drop-down list.

4. If applicable, select the "Page" checkbox and select the operator from the drop-down list.

5. If applicable, select the "Net send" checkbox and select the operator from the drop-down list.

6. Click "OK" to save the changes.

Now, the SQL Server Agent job will send notifications to the assigned operators when it completes or when an error occurs.

Setup Database Mail

Here's an example T-SQL code to setup Database Mail, create a new mail profile, and set it as the default profile for public use:

-- Enable Database Mail
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Database Mail XPs', 1;
GO
RECONFIGURE;
GO

-- Create a new mail profileEXECUTE msdb.dbo.sysmail_add_profile_sp
    @profile_name = 'MyMailProfile',
    @description = 'My Mail Profile Description',
    @profile_secure = 0;

-- Add an account to the mail profileEXECUTE msdb.dbo.sysmail_add_account_sp
    @account_name = 'MyMailAccount',
    @email_address = 'myemail@example.com',
    @mailserver_name = 'smtp.example.com',
    @port = 25,
    @username = 'myusername',
    @password = 'mypassword',
    @use_default_credentials = 0;

-- Add the account to the mail profileEXECUTE msdb.dbo.sysmail_add_profileaccount_sp
    @profile_name = 'MyMailProfile',
    @account_name = 'MyMailAccount',
    @sequence_number = 1;

-- Set the mail profile as default for public useEXECUTE msdb.dbo.sysmail_default_profile_sp
    @default_profile_name = 'MyMailProfile',
    @default_profile_security_option = 0;

In this code, we first enable the Database Mail feature, then create a new mail profile named "MyMailProfile" and add an account to it. We then add the account to the profile and set it as the default profile for public use with no security option. You can modify this code as per your requirements, such as adding multiple accounts to the profile or setting a security option for the default profile.

Setting Up A SMTP Server

The choice of SMTP servers for use with SQL Server Database Mail depends on various factors such as the organization's infrastructure, email service provider, and specific requirements. However, here are some general recommendations for SMTP servers that are commonly used with SQL Server Database Mail:

I have an account with MailGun and think it works well.

The choice of SMTP servers for use with SQL Server Database Mail depends on various factors such as the organization's infrastructure, email service provider, and specific requirements. However, here are some general recommendations for SMTP servers that are commonly used with SQL Server Database Mail:

1. Microsoft Exchange Server: If your organization uses Microsoft Exchange Server, it can be a good option for setting up SMTP for Database Mail. Exchange Server is a widely used mail server that supports sending emails from SQL Server.

2. Gmail: Gmail is a popular email service provider that offers SMTP relay service. You can configure SQL Server Database Mail to use Gmail SMTP server for sending emails.

3. Amazon SES: Amazon Simple Email Service (SES) is a cloud-based email service that provides a reliable and cost-effective option for sending emails from SQL Server.

4. SendGrid: SendGrid is a cloud-based email service provider that offers a reliable SMTP relay service for sending emails from SQL Server.

5. Mailgun: Mailgun is another cloud-based email service provider that offers a powerful SMTP relay service for sending transactional emails from SQL Server.

These SMTP servers have different pricing plans and feature sets, so it's important to evaluate them based on your specific needs and requirements. Additionally, some SMTP servers may require additional configuration or authentication settings to work with SQL Server Database Mail.

• Microsoft Exchange Server: If your organization uses Microsoft Exchange Server, it can be a good option for setting up SMTP for Database Mail. Exchange Server is a widely used mail server that supports sending emails from SQL Server.
  

• Gmail: Gmail is a popular email service provider that offers SMTP relay service. You can configure SQL Server Database Mail to use Gmail SMTP server for sending emails.
  

• Amazon SES: Amazon Simple Email Service (SES) is a cloud-based email service that provides a reliable and cost-effective option for sending emails from SQL Server.
  

• SendGrid: SendGrid is a cloud-based email service provider that offers a reliable SMTP relay service for sending emails from SQL Server.
  

• Mailgun: Mailgun is another cloud-based email service provider that offers a powerful SMTP relay service for sending transactional emails from SQL Server.

These SMTP servers have different pricing plans and feature sets, so it's important to evaluate them based on your specific needs and requirements. Additionally, some SMTP servers may require additional configuration or authentication settings to work with SQL Server Database Mail.