How can I capture and receive notifications about error events in my RDS for SQL Server instance?
I want to raise and capture error events on my Amazon Relational Database Service (Amazon RDS) for SQL Server DB instance. I also want to be notified whenever an error event occurs. How can I do this?
"I want to raise and capture error events on my Amazon Relational Database Service (Amazon RDS) for SQL Server DB instance. I also want to be notified whenever an error event occurs. How can I do this?Short descriptionSQL 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.ResolutionUse the TRY and CATCH method1.    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 CATCH2.    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 TRYSELECT 1/0END TRYBEGIN CATCHDECLARE @Var VARCHAR(100)SELECT ERROR_MESSAGE()SELECT @Var = ERROR_MESSAGE()RAISERROR(@Var, 16,1) WITH LOGEND CATCHThis 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 notificationsTo 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 storageDECLARE @jb TABLE ([step_id] int, [step_name] NVARCHAR(128), [message] NVARCHAR(4000), [run_status] int);insert into @jbselect 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_iddeclare @error intselect @error = count(run_status) from @jb where run_status != 0if @error > 0RAISERROR('Automatic message from RDS for SQL Server Agent. Job test2 successful', 18,1) WITH LOG  --\will raise the error when job successfulelseRAISERROR('Automatic message from RDS for SQL Server Agent. Job test2 failed', 16,1) WITH LOG  --\will raise the error when job failed2.    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 33Automatic message from RDS for SQL Server Agent. Job test2 failedMsg 50000, Level 18, State 1, Line 29Automatic message from RDS for SQL Server Agent. Job test2 successful3.    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?Follow"