How do I raise and capture error events in my Amazon RDS for SQL Server instance?

Short description

SQL Server uses error handling to resolve object existence errors and runtime errors in a T-SQL code. To troubleshoot errors like these, use the TRY...CATCH construct. Then use the RAISERROR command to generate customized errors and throw exceptions. To monitor the SQL Server error logs and receive notifications about them, use Amazon CloudWatch Logs.

Resolution

Use the TRY...CATCH construct and RAISERROR statement

Complete the following steps:

1. Log in to SQL Server Management Studio (SSMS).

2. Use the following TRY...CATCH construct to define a code block for error testing:

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

   Note: The statement_block monitors code that you include between BEGIN TRY and END TRY for errors at runtime. When an error occurs in the block, the error transfers to the CATCH session. Then, depending on the code in the CATCH block, the statement performs the action. Depending on the issue, you can fix the error, report the error, or log the error into the SQL Server error logs.

3. Create a custom message that raises a SQL Server error when it occurs. Add the following RAISERROR statement to your store procedures or SQL Server that you want to monitor:

   RAISERROR ( { msg_id | msg_str | @local_variable }  
     
   { , severity, state }  
     
   [ , argument [ , ...n ] ] )  
     
   [ WITH option [ , ...n ] ]

   Example TRY and CATCH construct and RAISERROR command: 

   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

   As a result, it raises the following error message 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

Add a script to the job step to monitor the SQL Server agent job and raise the error in the SQL Server error logs. You can then use the error logs to send notifications.

To edit your SQL Server agent job, complete the following steps:

1. Log in to SSMS.

2. For type, choose T-SQL.

3. Enter a database name, and then add the following 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  
     
   declare @errormsg nvarchar(4000)  
     
   select @error = count(run_status) from @jb where run_status != 0  
     
   if @error > 0  
     
   begin  
     
   set @errormsg='Automatic message from RDS for SQL Server Agent - Job: "' + @name + '" succeed'  
     
   RAISERROR(@errormsg, -1,1) WITH LOG  
     
   end else  
     
   begin  
     
   set @errormsg='Automatic message from RDS for SQL Server Agent - Job: "' + @name + '" failed'  
     
   RAISERROR(@errormsg, 16,1) WITH LOG  
     
   end

4. Configure the preceding job step to go to the next job steps On success and On failure. For more information, see Set job step success or failure flow on the Microsoft website. The On success and On failure job steps run the previous code to check if it's a successful execution or not. The agent job then raises the message in the SQL Server error log that shows whether the job is a success or failure.

5. Run the following procedure to confirm that the SQL Server job ran correctly and updated the details of the failed job in the SQL Server error logs:

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

   For more information, see Viewing error and agent logs.
   The following is an example of the updated job details in the error logs:

   Automatic message from RDS for SQL Server Agent - Job: "jobtest-new" succeed    
   Error: 50000, Severity: 16, State: 1.  
   Automatic message from RDS for SQL Server Agent - Job: "jobtest-new" failed

Configure notifications in CloudWatch Logs

To configure notifications in CloudWatch, see Publishing SQL Server logs to Amazon CloudWatch Logs.

After you publish the SQL Server logs to CloudWatch Logs, you can create metric filters to help you search the logs. The metric filters turn the 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?