How can I capture and receive notifications about error events in my RDS for SQL Server instance?

Short description

SQL Server uses error handling to resolve object existence errors and run time errors in a T-SQL code. To handle errors like these, use the TRY and CATCH method. Then, use the RAISERROR command to generate customer errors and throw exceptions.

Resolution

Use the TRY and CATCH method

1.    Use a TRY and CATCH statement to define a code block for error testing. Any code that you include between BEGIN TRY and END TRY is monitored for errors at the time of run. Whenever an error occurs in the block, it's transferred to the CATCH session. Then, depending on the code in the CATCH block, the action is performed. Depending on the issue, you can fix the error, report the error, or log the error into the SQL Server error logs.

BEGIN TRY
                            --code to try
                  END TRY
                  BEGIN CATCH
                                --code to run if an error occurs
                                --is generated in try
                  END CATCH

2.    Create a custom message that raises a SQL Server error when it occurs. To do this, add RAISERROR to your store procedures or to a SQL Server that you want to monitor.

RAISERROR ( { msg_id | msg_str | @local_variable }

                         { , severity, state }

                         [ , argument [ , ...n ] ] )

                         [ WITH option [ , ...n ] ]

Examples of the TRY CATCH method and RAISERROR When you capture errors using the TRY CATCH method, create a custom message, and then raise the error into the SQL Server error logs. See this example:

BEGIN TRY

SELECT 1/0

END TRY

BEGIN CATCH

DECLARE @Var VARCHAR(100)

SELECT ERROR_MESSAGE()

SELECT @Var = ERROR_MESSAGE()

RAISERROR(@Var, 16,1) WITH LOG

END CATCH

This is an example of an error raised in the SQL Server logs:

Error: 50000, Severity: 16, State: 1.

  Divide by zero error encountered.

Monitor the SQL Server error logs and send notifications

To monitor the SQL Server agentjob, add a script to the step to monitor and raise the error in the SQL Server error logs. You can then use these logs to send notifications.

1.    Edit your SQL Server job, and add the step. For type, choose T-SQL. Enter a database name, and then add this T-SQL in the command section:

DECLARE @name NVARCHAR(128)

select @name =  name from msdb.dbo.sysjobs where job_id = $(ESCAPE_SQUOTE(JOBID));



-- Temporary table to store the data of the datafile with low free storage

DECLARE @jb TABLE ([step_id] int, [step_name] NVARCHAR(128), [message] NVARCHAR(4000), [run_status] int);



insert into @jb

select hist.step_id, hist.step_name, hist.message, hist.run_status

  from msdb.dbo.sysjobhistory hist inner join

       (select a.job_id

               , convert(varchar(50),max(a.run_requested_date),112) as run_date

                     , replace(convert(varchar(50),max(a.run_requested_date),108), ':', '') as run_time

          from msdb.dbo.sysjobs j inner join msdb.dbo.sysjobactivity a

               on j.job_id = a.job_id

           where j.name = @name

               and a.run_requested_date is not null

         group by a.job_id) ja

        on hist.job_id = ja.job_id

       and hist.run_date = ja.run_date

       and hist.run_time >= ja.run_time

 order by hist.step_id

declare @error int

select @error = count(run_status) from @jb where run_status != 0

if @error > 0

RAISERROR('Automatic message from RDS for SQL Server Agent. Job test2 successful', 18,1) WITH LOG  --\will raise the error when job successful

else

RAISERROR('Automatic message from RDS for SQL Server Agent. Job test2 failed', 16,1) WITH LOG  --\will raise the error when job failed

2.    Configure the SQL Server job to go to the step that you created for the On failure action section.

3.    Run this procedure to confirm that the SQL Server job ran correctly and updated the job failed details in the SQL Server error logs. For more information see Viewing error and agent logs.

EXEC rdsadmin.dbo.rds_read_error_log @index = 0, @type = 1;

Example in the error logs:

Msg 50000, Level 18, State 1, Line 33
Automatic message from RDS for SQL Server Agent. Job test2 failed
Msg 50000, Level 18, State 1, Line 29
Automatic message from RDS for SQL Server Agent. Job test2 successful

3.    Configure notifications by publishing the SQL Server logs to Amazon CloudWatch. Modify the SQL Server using the Amazon RDS console. From the Log exports section, choose the logs that you want to publish to the CloudWatch logs. After you publish the SQL Server logs to Amazon CloudWatch, you can create metric filters to help you search the logs. Metric filters define the terms and patterns that Amazon CloudWatch searches the log data for. Then, the metric filters turn this log data into numerical CloudWatch metrics that you can set alarms for.

For more information, see How can I receive SNS notifications about Amazon RDS for SQL Server error and agent log events that match a CloudWatch filter pattern?

---