How can I receive a notification when a deadlock event occurs on my Amazon RDS SQL Server DB instance?
Last updated: 2020-06-02
I have an Amazon Relational Database Service (Amazon RDS) DB instance that runs SQL Server. I want to be notified as soon as a deadlock event occurs on my RDS DB instance. How can I do this?
SQL Server uses a lock monitor thread to monitor deadlock events. If a deadlock event is detected, then SQL Server uses a resource/cost-based mechanism to declare one of the transactions a deadlock victim. The victim transaction is rolled back and error code 1205 is posted in the error logs.
To resolve this issue, enable deadlock trace flags (1222, 1204) to capture deadlock events in the SQL error logs. Next, publish SQL Server error logs to Amazon CloudWatch and create CloudWatch alarms and Amazon Simple Notification Service (Amazon SNS) notifications that send an alert whenever a deadlock event occurs.
Enable trace flags through RDS Parameter groups
Note: If you have an existing custom parameter group, then skip to step 5.
- Open the Amazon RDS console, and then choose Parameter groups from the navigation pane.
- Choose Create parameter group.
- For Parameter group family, choose the SQL Server version and edition you are using. For example, SQL Server 2016 Enterprise Edition uses sqlserver-ee-13.0.
- Enter a Group name and Description, and then choose Create.
- On the Parameter groups page, choose the group that you created in the previous step.
- Choose Edit parameters, and check the box beside 1204 and 1222.
- Edit the Values for both 1204 and 1222 to 1.
- Choose Preview changes. On the next page, choose Save changes.
- From the navigation pane, choose Databases.
- In the DB identifier section, choose your RDS DB instance.
- Choose Modify.
- From the Database options section, for DB parameter group, choose the parameter group that you created.
Note: Creating a new parameter group and attaching it to an RDS DB instance requires that you reboot the RDS DB instance for the changes to take effect.
Push the SQL Server error log to Amazon CloudWatch
- From the Error logs section, choose the box for Error log. This makes sure that the SQL Server error logs are published to CloudWatch Logs.
- Choose Continue.
- From the Scheduling of modifications section, choose Apply immediately, and then choose Modify DB Instance.
Note: Your RDS DB instance is now in the modifying status. Wait until it has returned to the available status. If you used an existing parameter group, skip step 4 as you do not need to reboot your RDS DB instance. If you have created a new parameter group, proceed with step 4.
- From the Databases page, choose your RDS DB instance, and then choose Actions. Reboot the RDS DB instance for the changes to take effect.
After the RDS DB instance comes online, you can verify the deadlock trace status by running DBCC TRACESTATUS (1222, 1204) on your RDS DB instance. If the Global trace command returns 1 in the output windows, then the deadlock trace is enabled.
Simulate a deadlock transaction
Simulate a deadlock transaction on your RDS DB instance to validate if the deadlock victim queries are logged in the SQL Server error logs. You can check the deadlock events in SQL Server error logs by running the following query on your RDS DB instance:
sp_readerrorlog 0, 1, 'deadlock'
Create a filter pattern and Amazon CloudWatch alarm
- Open the CloudWatch console.
- From the Logs section, choose Log groups.
- Choose the SQL Server error logs of your RDS DB instance. The logs are listed in the following format:
- Choose Create metric filter.
- From the Define Logs Metric Filter page, in the Filter Pattern section, enter Deadlock.
- Choose Assign metric.
- Enter Deadlock in both the Filter Name and Metric Name fields.
- Choose Create Filter. After the deadlock filter is created, choose Create alarm.
- From the Specify metric and conditions page, under Metric name, enter Deadlock.
- Under Statistic, enter Minimum.
- Under Period, from the dropdown menu, choose the time period for the alarm, for example, 1 minute.
From the Conditions section, choose the following settings:
Threshold type: Static
Whenever Deadlock is: Greater > threshold
Than: Enter 0
- Choose Next.
- From the Notification section, under Alarm state trigger, choose In alarm.
- Select an Amazon SNS topic, or choose Create new topic using the email address you want to receive alerts, and then choose Next.
- From the Name and description section, enter an Alarm name and Alarm description, and choose Next.
- From the Preview and create page, review your alarm configuration, and then choose Create alarm.
Use the same process to create CloudWatch alarms for other SQL Server fatal errors and login failed errors. This lets you identify and receive notifications about many SQL Server errors before the database system has a major issue.