How can I receive a notification when a deadlock event occurs on my Amazon RDS for Microsoft SQL Server DB instance?

Short description

When the SQL Server deadlock monitor detects a deadlock event, the monitor chooses one of the transactions as a victim. The monitor rolls back the victim transaction and issues error code 1205 in the error logs.

To resolve this issue, first activate deadlock trace flags 1222 and 1204 to capture deadlock events in the SQL Server error logs. Then, publish SQL Server error logs to Amazon CloudWatch Logs. Finally, create CloudWatch alarms and Amazon Simple Notification Service (Amazon SNS) notifications that send an alert when a deadlock event occurs.

Resolution

Activate trace flags through RDS parameter groups

Complete the following steps:

Note: If you have an existing custom parameter group, then proceed to step 5.

1. Open the Amazon RDS console, and then in the navigation pane choose Parameter groups.
2. Choose Create parameter group.
3. For Parameter group family, choose the SQL Server version and edition that you want to use. For example, SQL Server 2016 Enterprise Edition uses sqlserver-ee-13.0.
4. Enter a group name and description, and then choose Create.
5. On the Parameter groups page, select the group.
6. Choose Edit parameters, and then select 1204 and 1222.
7. Change the values for 1204 and 1222 to 1.
8. Choose Preview changes, and then choose Save changes.
9. In the navigation pane, choose Databases.
10. In the DB identifier section, select your DB instance.
11. Choose Modify.
12. In the Database options section, for DB parameter group, choose the parameter group.
13. Reboot the DB instance for the changes to take effect.

Push the SQL Server error log to CloudWatch Logs

Complete the following steps:

1. Open the Amazon RDS console, and then in the navigation page choose Databases.
2. Select the DB instance, and then choose Modify.
3. In Log exports, select Error log, and then choose Continue.
4. In the Scheduling of modifications section, choose Apply immediately, and then choose Modify DB Instance.
   Note: Your DB instance is now in the modifying status. Wait until it returns to the available status.
5. On the Databases page, choose your DB instance, and then choose Actions.
6. If you created a new parameter group, then reboot the DB instance for the changes to take effect.

After the DB instance is online, run DBCC TRACESTATUS (1222, 1204) on your DB instance to verify the deadlock trace status. If the Global trace command returns 1 in the output window, then the deadlock trace is activated.

Simulate a deadlock transaction

To confirm whether the SQL Server monitor logged the deadlock victim queries in the error logs, simulate a deadlock transaction on your DB instance. You can run the following query on your DB instance to check the deadlock events in SQL Server error logs:

sp_readerrorlog 0, 1, 'deadlock'

Create a filter pattern and CloudWatch alarm

Complete the following steps:

1. Open the CloudWatch console.
2. In the Logs section, choose Log groups.
3. Select the SQL Server error logs of your DB instance. The logs are listed in the following format:(/aws/rds/instance/<Your-RDS-Instance-Name>/error).
4. Choose Create metric filter.
5. On the Define Logs Metric Filter page, in the Filter Pattern section, enter deadlock.
6. Choose Assign metric.
7. In the Filter Name and Metric Name fields, enter deadlock.
8. Choose Create Filter, and then choose Create alarm.
9. On the Specify metric and conditions page, under Metric name, enter deadlock.
10. Under Statistic, enter minimum.
11. Under Period, from the dropdown list, choose the time period for the alarm, for example, 1 minute.
12. In the Conditions section, choose the following settings:
    Threshold type: Static
    Whenever Deadlock is: Greater > threshold
    Than: 0
13. Choose Next.
14. In the Notification section, under Alarm state trigger, choose In alarm.
15. Subscribe to an Amazon SNS topic, or create a new topic. Use the email address where you want to receive alerts.
16. Choose Next.
17. In the Name and description section, enter an alarm name and alarm description, and then choose Next.
18. On the Preview and create page, review your alarm configuration, and then choose Create alarm.

Use the preceding process to create CloudWatch alarms for other SQL Server fatal errors and login failed errors.

Related Information

MySQL database log files