Using Alerts and Operators in SQL Server

Introduction

With all the advancements in SQL Server and data, it feels great that such native SQL tools as Alerts and Operators are still available. They are the key SQL Server Agent features that are related to anything in SQL Server automation. 

Their roles are clear:

• Alerts, when configured, let database administrators know when a specified event has occurred. 
• An operator is an object defined within SQL Server that maps anyone or a group that can receive notifications when these events occur.

Types of Events

SQL Server records events to the Windows Application Log – we can access it via Windows Event Viewer at the Operating System level. SQL Server Agent can read the Application Log and fire alerts when there is a match between 2 categories of things: 1) events recorded in the application log 2) an alert defined by an administrator. 

There are three main types of events:

• SQL Server events
• SQL Server performance conditions
• WMI events

Creating an SQL Server Event

We start creating an SQL Server Agent alert by right-clicking on the Alerts node in the object explorer under SQL Server Agent. This opens the window shown in Fig. 2 where you specify the name of the alert, its type (SQL Server Event Alert in this case), database scope, and alert conditions. 

The simplest way to specify a condition is to use the severity level. And to get a complete list of error messages in SQL Server, you need to query sys.messages. Note that this view contains 309408 rows so using a filter is a great idea – that’s why in my query, I have filtered for English via command.

Fig. 1. Alerts Node

-- Listing 1: SQL Server Error Messages
use msdb
go
SELECT * FROM sys.messages where language_id=1033
order by severity;

Fig. 2. Creating an SQL Server Event Alert

Notice, that I could also use a specific error message to trigger an alert. As mentioned earlier, all error messages are listed in the SQL Server catalog view sys.messages. On the Fig. 2 screen, I have merely selected the event conditions – the response page allowed me to choose what happens if this event is captured. As you can see, I can either choose to automate my response by executing a job or simply notify operators. We will explore executing a job later but, for now, let’s observe why an Operator is required to proceed with the alternative option. We will create an operator in the same window, but If we had existing operators, we could’ve simply selected them from the list. 

Fig. 3. Selecting a Response

Fig. 4. Defining an Operator

Fig. 5. Selecting the Alert Channel

In Fig 5, we choose Email as the medium for sending notifications. You should configure Database Mail correctly, otherwise, the generated alerts will remain in the queue. On the third page of the New Alert window, we can choose if the error message is included in the email notification and whether we want to send an additional message (for example, the instructions to a Junior DBA about further actions). We also determine the interval between alerts fired. It’s important because if you leave it at the default (0), the operator’s mailbox will be full of alerts in a short period of time.

Fig .6. Configuring Options

-- Listing 2: Creating a SQL Server Alert Using sp_add_alert
USE [msdb]
GO
EXEC msdb.dbo.sp_add_alert @name=N'Alert_Insufficient_Resources_017',
@message_id=0,
@severity=17,
@enabled=1,
@delay_between_responses=900,
@include_event_description_in=1,
@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Alert_Insufficient_Resources_017', @operator_name=N'DatabaseAdmin', @notification_method = 1
GO

Listing 2 shows the equivalent code for implementing this alert. Once we complete the process by clicking OK, we will be able to view both the alert and the operator in the SQL Server Agent node in Object Explorer.

Fig. 7. Alert Created

Creating a SQL Server Performance Condition Alert

We can also go the alternate route and create a performance condition alert. As an example we choose Databases > Percent Log Used Counter. We tell SQL Server to generate an event when the Log File Usage exceeds 75%. In this case, we choose ‘to execute a job’ as the response. Listing 3 shows the major tasks being done by this job and Listing 4 shows the complete definition of the job.

Fig. 8. Performance Condition Alert

-- Listing 3: SQL Server Error Messages
BACKUP LOG [DB1] TO DISK = N'E:\MSSQL\Backup\DBTransactionLog.bak'
WITH RETAINDAYS = 90, NOFORMAT, NOINIT, NAME = N'DB1-TransactionLog Backup',
SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

USE [DB1]
GO
DBCC SHRINKFILE (N'DB1_log' , 0, TRUNCATEONLY)
GO


-- Listing 4: Complete Definition of SQL Agent Backup Job
USE [msdb]
GO

/****** Object: Job [DB1_BackupTransactionLog] Script Date: 27/11/2019 7:20:22 PM ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0

/****** Object: JobCategory [[Uncategorized (Local)]] Script Date: 27/11/2019 7:20:23 PM ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'DB1_BackupTransactionLog',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=3,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'Backup the Transaction Log for DB1 when Transaction Log usage exceeds 75%',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'DESKTOP-CT8K40N\kenne',
@notify_email_operator_name=N'DatabaseAdmin', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

/****** Object: Step [Backup_Transaction_Log] Script Date: 27/11/2019 7:20:23 PM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Backup_Transaction_Log',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=2,
@retry_interval=15,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'-- Listing 3: SQL Server Error Messages
BACKUP LOG [DB1] TO DISK = N''E:\MSSQL\Backup\DBTransactionLog.bak''
WITH RETAINDAYS = 90, NOFORMAT, NOINIT, NAME = N''DB1-TransactionLog Backup'',
SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO',
@database_name=N'master',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

/****** Object: Step [DB1_Shrink_Transaction_Log_File] Script Date: 27/11/2019 7:20:23 PM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'DB1_Shrink_Transaction_Log_File',
@step_id=2,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=2,
@retry_interval=15,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'USE [DB1]
GO
DBCC SHRINKFILE (N''DB1_log'' , 0, TRUNCATEONLY)
GO',
@database_name=N'master',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO

Fig. 9. Create Job to the Executed

Since our response, in this case, is the job execution, we also include a notification in the job itself. Once we are done with the job creation, we can review the alert properties in Object Explorer. We can check in the History Page how many times the event has occurred.

Fig. 10. Options for Alert

Fig. 11. Alert History Page

Conclusion

In this article, we have explored the creation of alerts and operators. You can take advantage of this powerful capability in SQL Server and set up your environment for monitoring basic events without buying a third-party tool. The possibility of using jobs to correct minor problems helps us achieve proactive management of our SQL Server environment.