Learn SQL Server

Active Alerts

Database Mail

Database Mail is a feature in Microsoft SQL Server that allows users to send email messages directly from the SQL Server database engine. With Database Mail, database administrators can configure email messages to be sent in response to specific events or alerts that occur within the database, such as a failure of a database backup, a long-running query, or an error in a job.

To use Database Mail, you need to configure an SMTP server to send email messages, set up a mail profile and an account in the SQL Server Management Studio, and then use T-SQL or the SQL Server Agent to send email messages.

Database Mail is a useful tool for sending notifications and alerts to users or administrators about important events or changes within the database. It can be particularly useful in large organizations with many databases, where it may be difficult to manually monitor all the activities in the databases. By using Database Mail, administrators can automate the process of sending notifications and alerts, and ensure that the appropriate personnel are notified in a timely manner about any issues or problems within the database.

Here are some commonly used SMTP providers with SQL Server to send database mail:

Gmail:

You can use Gmail as your SMTP provider to send emails from SQL Server. However, you will need to enable "Less Secure Apps" on your Google account.

Outlook:

If you are using Microsoft Outlook, you can use it as your SMTP provider to send emails from SQL Server.

SendGrid:

SendGrid is a cloud-based email delivery service that can be used as an SMTP provider with SQL Server. It provides a robust set of APIs that can be used to integrate with various programming languages, including T-SQL.

Amazon Simple Email Service

Amazon Simple Email Service (SES) is a cost-effective email delivery service that can be used as an SMTP provider with SQL Server.

Mailgun:

Mailgun is a popular email service provider that can be used as an SMTP provider with SQL Server. It provides a powerful API that can be used to send and receive emails programmatically.Note that some SMTP providers may require additional configuration or authentication steps, such as using SSL or providing API keys, to be able to send email from SQL Server.

Mikes Take: I like Mailgun it's simple and very low-cost.

To setup Database Mail in SQL Server using T-SQL, follow these steps:

Enable Database Mail:

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

Create a Database Mail account:

EXECUTE msdb.dbo.sysmail_add_account_sp
    @account_name = 'MyDatabaseMailAccount',
    @email_address = 'example@example.com',
    @display_name = 'Example Mail Account',
    @replyto_address = 'example@example.com',
    @smtp_server = 'smtp.example.com',
    @smtp_port = 587,
    @smtp_authenticate = 1,
    @smtp_username = 'example@example.com',
    @smtp_password = 'password',
    @enable_ssl = 1;
GO

Note: Replace the values in the above T-SQL code with your own values.

Create a Database Mail profile:

EXECUTE msdb.dbo.sysmail_add_profile_sp
    @profile_name = 'MyDatabaseMailProfile',
    @description = 'Profile for sending database mail';
GO

Add the Database Mail account to the profile:

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
    @profile_name = 'MyDatabaseMailProfile',
    @account_name = 'MyDatabaseMailAccount',
    @sequence_number = 1;
GO

Grant permission to the Database Mail profile:

EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
    @profile_name = 'MyDatabaseMailProfile',
    @principal_name = 'public',
    @is_default = 1;
GO

That's it! You have now setup Database Mail in SQL Server using T-SQL.

Here is a video outline of the process for SQL 2008 but the process is still the same in other versions

Alerts 19-25 In SQL Server

Alerts 19-25 in SQL Server are predefined system alerts that can be used to monitor SQL Server performance and health. Here's a brief overview of each alert:

Alert 19: "Replication: Agent failure" - This alert is raised when a SQL Server Replication Agent fails to complete a job or task.

Alert 20: "Replication: Agent retry" - This alert is raised when a SQL Server Replication Agent fails to complete a job or task and is automatically retried.

Alert 21: "Replication: Checkup for agent job" - This alert is raised when a SQL Server Replication Agent job fails to run as scheduled.

Alert 22: "Replication: Agent success" - This alert is raised when a SQL Server Replication Agent successfully completes a job or task.

Alert 23: "SQL Server performance condition alert" - This alert is raised when a predefined performance condition is met, such as a high CPU usage or low disk space.

Alert 24: "SQL Server event alert" - This alert is raised when a predefined SQL Server event occurs, such as a deadlock or a failed login attempt.

Alert 25: "OLEDB call failed" - This alert is raised when an OLEDB call fails, indicating a potential issue with database connectivity or data access.

These alerts can be useful for monitoring and troubleshooting SQL Server issues, and can be configured to notify administrators or trigger automated responses, such as running a job or sending an email notification.

Here's an example of T-SQL code to set up alerts 19-25 in SQL Server:

-- Alert 19: Replication: Agent failure
EXEC msdb.dbo.sp_add_alert @name=N'Replication: Agent failure', 
    @message_id=1480, 
    @severity=0, 
    @enabled=1, 
    @delay_between_responses=0, 
    @include_event_description_in=1, 
    @category_name=N'[Uncategorized]', 
    @job_id=N'00000000-0000-0000-0000-000000000000'

-- Alert 20: Replication: Agent retry
EXEC msdb.dbo.sp_add_alert @name=N'Replication: Agent retry', 
    @message_id=14182, 
    @severity=0, 
    @enabled=1, 
    @delay_between_responses=0, 
    @include_event_description_in=1, 
    @category_name=N'[Uncategorized]', 
    @job_id=N'00000000-0000-0000-0000-000000000000'

-- Alert 21: Replication: Checkup for agent job
EXEC msdb.dbo.sp_add_alert @name=N'Replication: Checkup for agent job', 
    @message_id=14183, 
    @severity=0, 
    @enabled=1, 
    @delay_between_responses=0, 
    @include_event_description_in=1, 
    @category_name=N'[Uncategorized]', 
    @job_id=N'00000000-0000-0000-0000-000000000000'

-- Alert 22: Replication: Agent success
EXEC msdb.dbo.sp_add_alert @name=N'Replication: Agent success', 
    @message_id=14184, 
    @severity=0, 
    @enabled=1, 
    @delay_between_responses=0, 
    @include_event_description_in=1, 
    @category_name=N'[Uncategorized]', 
    @job_id=N'00000000-0000-0000-0000-000000000000'

-- Alert 23: SQL Server performance condition alert
EXEC msdb.dbo.sp_add_alert @name=N'SQL Server performance condition alert', 
    @message_id=0, 
    @severity=0, 
    @enabled=1, 
    @delay_between_responses=0, 
    @include_event_description_in=1, 
    @category_name=N'[Uncategorized]', 
    @performance_condition=N'MSSQL$InstanceName:SQL Statistics:Batch Requests/sec:>', 
    @performance_condition_compare=N'>', 
    @performance_value=100, 
    @job_id=N'00000000-0000-0000-0000-000000000000'

-- Alert 24: SQL Server event alert
EXEC msdb.dbo.sp_add_alert @name=N'SQL Server event alert', 
    @message_id=0, 
    @severity=0, 
    @enabled=1, 
    @delay_between_responses=0, 
    @include_event_description_in=1, 
    @category_name=N'[Uncategorized]', 
    @event_description=N'server:deadlock', 
    @job_id=N'00000000-0000-0000-0000-000000000000'

-- Alert 25: OLEDB call failed
EXEC msdb.dbo.sp_add_alert @name=N'OLEDB call failed', 
    @message_id=0, 
    @severity=0, 
    @enabled=1, 
    @delay_between_responses

Here is a video of the process in SQL 2016

Set Job Failure Alerts

To set up job failure alerts in SQL Server using SQL Server Management Studio (SSMS), follow these steps:

• Open SSMS and connect to your SQL Server instance.
  

• Expand the SQL Server Agent node in Object Explorer, right-click on Alerts and select "New Alert..." from the context menu.
  

• In the New Alert dialog, give the alert a name such as "Job Failure Alert".
  

• Under the General tab, select "SQL Server performance condition alert" as the type of alert.
  

• In the "Raise alert when" section, select "SQL Server job event" as the source and "Job Failure" as the event.
  

• Under the "Response" tab, select "Notify operators" and then click on the ellipsis button to choose the operator or operators you want to notify when the alert is raised. You can create a new operator by clicking the "New..." button.
  

• Under the "Options" tab, you can set various options such as the severity level, whether to include the error message in the alert, and how often to re-send the alert if it is not acknowledged.
  

• Click OK to create the alert.
  

• To test the alert, create a new SQL Server Agent job that intentionally fails. To do this, right-click on Jobs in Object Explorer, select "New Job..." and create a simple job with a T-SQL step that contains an error, such as:

SELECT 1/0;

• Execute the job by right-clicking on it in Object Explorer and selecting "Start Job at Step...".
  

• When the job fails, the alert should be raised and the operator you specified should receive an email or other notification, depending on your settings. You can view the details of the alert in the SQL Server Agent Error Logs.

Detecting when the "sa" account logs into a SQL Server instance can be useful for security and auditing purposes. The "sa" account is a powerful account that has unrestricted access to the SQL Server instance, and its use should be limited to only when necessary.

By monitoring when the "sa" account logs in, you can detect unauthorized access attempts and take appropriate action, such as investigating the source of the login attempt and taking steps to prevent further unauthorized access.In addition, detecting "sa" logins can help with auditing and compliance requirements.

Many organizations have security policies or compliance regulations that require tracking and logging of all privileged account activity, and detecting "sa" logins is one way to achieve this. By logging "sa" logins and sending alerts when they occur, you can maintain a record of all privileged account activity and be alerted to any suspicious activity.

To create an alert in SQL Server that triggers when the "sa" account logs into the system, you can use a server trigger and an alert. Here is an example T-SQL code to create the trigger and the alert:

USE master;
GO

-- Create the server trigger
CREATE TRIGGER tr_sa_login
ON ALL SERVER WITH EXECUTE AS 'sa'
FOR LOGON
AS
BEGIN
    DECLARE @loginName NVARCHAR(128);
    SET @loginName = ORIGINAL_LOGIN();
    
    IF @loginName = 'sa'
    BEGIN
        EXEC msdb.dbo.sp_notify_operator 
            @name = 'DBA',
            @subject = 'SA account logged in',
            @message = 'The SA account has logged into the system.';
    END
END
GO

-- Create the operator for the alert
EXEC msdb.dbo.sp_add_operator 
    @name = 'DBA', 
    @enabled = 1, 
    @email_address = 'dba@yourdomain.com'

-- Create the alert
EXEC msdb.dbo.sp_add_alert 
    @name = 'SA Login Alert', 
    @message_id = 0, 
    @severity = 16, 
    @enabled = 1, 
    @category_name = 'Database', 
    @job_id = NULL, 
    @operator_name = 'DBA', 
    @performance_condition = 'sa_login', 
    @wmi_namespace = NULL, 
    @wmi_query = NULL;

In this example, we create a server trigger that runs whenever a login occurs, and checks if the login is the "sa" account. If the login is "sa", the trigger calls the sp_notify_operator stored procedure to send an email to the DBA operator.

We also create the operator for the alert, and then create the alert itself, specifying the DBA operator as the recipient, and the sa_login performance condition to trigger the alert when the "sa" account logs in.

Note that the server trigger runs with the EXECUTE AS 'sa' clause to ensure that the trigger can detect when the "sa" account logs in. This clause requires the CONTROL SERVER permission.

Enable Blocking and Locking Notifications Method #1

To send an alert when there is blocking in the system, you can create a SQL Server Agent alert that is triggered when a blocking event occurs. Here is an example T-SQL code to create the alert:

USE msdb
GO

-- Create the operator for the alert
EXEC dbo.sp_add_operator 
    @name = 'DBA', 
    @enabled = 1, 
    @email_address = 'dba@yourdomain.com';

-- Create the alert
EXEC dbo.sp_add_alert 
    @name = 'Blocking Alert', 
    @message_id = 0, 
    @severity = 16, 
    @enabled = 1, 
    @category_name = 'Database', 
    @job_id = NULL, 
    @operator_name = 'DBA', 
    @performance_condition = 'MSSQL$InstanceName:Locks|Number of Deadlocks/sec|_Total|>|0', 
    @wmi_namespace = NULL, 
    @wmi_query = NULL;

In this example, we create an alert that is triggered when the "Number of Deadlocks/sec" performance counter for the SQL Server instance named "InstanceName" is greater than 0. This indicates that blocking is occurring in the system, as deadlocks can be caused by blocking.

We also create an operator for the alert, named "DBA", and specify this operator as the recipient of the alert. You can customize the email address for the operator as needed.

Once the alert is created, you can configure it to perform any actions you need, such as sending an email notification or running a job. To test the alert, you can deliberately create blocking in the system and monitor whether the alert is triggered. You can also view the alert history in SQL Server Agent to see the details of the alerts that have been raised.

Method #2

If you prefer not to use a SQL Server Agent alert to detect blocking, you can use a SQL Server Agent job to periodically check for blocking and send an email notification when blocking is detected. Here is an example T-SQL code to create the job:

USE msdb
GO

-- Create the operator for the job
EXEC dbo.sp_add_operator 
    @name = 'DBA', 
    @enabled = 1, 
    @email_address = 'dba@yourdomain.com';

-- Create the job
DECLARE @job_id uniqueidentifier;
EXEC dbo.sp_add_job 
    @job_name = 'Blocking Check Job', 
    @enabled = 1,
    @description = 'Checks for blocking in the system',
    @job_id = @job_id OUTPUT;

-- Create a job step to check for blocking
EXEC dbo.sp_add_jobstep 
    @job_id = @job_id, 
    @step_name = 'Check for blocking', 
    @subsystem = 'TSQL', 
    @command = '
        IF EXISTS (
            SELECT *
            FROM sys.dm_exec_requests r1
            WHERE r1.blocking_session_id <> 0
        )
        BEGIN
            EXEC msdb.dbo.sp_notify_operator 
                @name = ''DBA'', 
                @subject = ''Blocking Detected'', 
                @message = ''Blocking has been detected in the system.'';
        END', 
    @retry_attempts = 0, 
    @retry_interval = 0, 
    @on_success_action = 1, 
    @on_failure_action = 2;

-- Add a schedule to run the job every 5 minutes
EXEC dbo.sp_add_schedule 
    @schedule_name = 'Blocking Check Schedule', 
    @enabled = 1, 
    @freq_type = 4, 
    @freq_interval = 1, 
    @freq_subday_type = 4, 
    @freq_subday_interval = 5, 
    @active_start_time = 0, 
    @active_end_time = 235959;

-- Attach the schedule to the job
EXEC dbo.sp_attach_schedule 
    @job_id = @job_id, 
    @schedule_name = 'Blocking Check Schedule';

-- Assign the operator to the job
EXEC dbo.sp_add_jobserver 
    @job_id = @job_id, 
    @server_name = N'(local)', 
    @local_server_name = NULL;

-- Start the job
EXEC dbo.sp_start_job 
    @job_id = @job_id;

In this example, we create a SQL Server Agent job named "Blocking Check Job" that runs every 5 minutes. The job has a single step that checks whether any blocking is currently occurring in the system using the sys.dm_exec_requests dynamic management view.

If blocking is detected, the step calls the sp_notify_operator stored procedure to send an email notification to the "DBA" operator.

We also create the "DBA" operator and assign it to the job as the recipient of the email notification.

Once the job is created, you can monitor the job history to see if it has detected any blocking in the system and sent email notifications as needed. You can also modify the job step to perform any other actions you need when blocking is detected, such as logging the event to a table or running a custom stored procedure.