AWS Database Blog

Monitor deadlocks in SQL Server on Amazon EC2 and set notifications using Amazon CloudWatch

July 2023: This post was reviewed for accuracy.

Many of our customers running SQL Server on Amazon Elastic Compute Cloud (Amazon EC2) instances want to capture specific events (systems and user-defined) for monitoring and troubleshooting issues with SQL Server. SQL Server logs these events in its error logs and agent logs.

A deadlock is an event that can be captured in SQL Server error logs, and you may often want to be notified of these events. A deadlock occurs when two or more processes are waiting on the same resource and each process is waiting on the other process to complete before moving forward. When this situation occurs, there is no way for these processes to resolve the conflict, so SQL Server automatically chooses one of the processes as the victim of the deadlock and rolls back the process, and the other process succeeds.

Although the client application may see and handle deadlocks, these are not captured in SQL Server error logs by default.

The error message that SQL Server sends back to the client is similar to the following:

Msg 1205, Level 13, State 51, Line 3
Transaction (Process ID xx) was deadlocked on {xxx} resources with another process 
and has been chosen as the deadlock victim. Rerun the transaction

With SQL Server on Amazon EC2, you can now monitor deadlocks and send Amazon Simple Notification Service (Amazon SNS) notifications as soon as a deadlock event occurs for a SQL Server instance. This can help you automate deadlock reporting and take appropriate actions to resolve deadlock conflicts.

This solution isn’t limited to capturing deadlock events; you can scale this solution to monitor other system and user-defined events captured in the error logs and SQL Server agent logs.

In this post, we show you how to publish error and agent log events directly to Amazon CloudWatch Logs and set up CloudWatch alarms and SNS notifications for the deadlock events that match the filter pattern that you create.

Solution overview

The following diagram illustrates the solution architecture for CloudWatch integration with SQL Server.

To implement the solution, we walk through the following high-level steps:

  1. Enable deadlock detection for SQL Server.
  2. Publish SQL Server error logs to CloudWatch.
  3. Simulate a deadlock event.
  4. Create a filter pattern and CloudWatch alarm.

Prerequisites

Before you get started, you must have the following prerequisites:

Enable deadlock detection for SQL Server

To enable deadlock detection in SQL Server, you must enable global trace flags 1222 and 1204. There are two ways to enable global trace flags:

  • Method 1 – Enable deadlock detection by setting –T1222 and –T1204 startup options in SQL Server properties as shown in the following screenshot. We recommend this option because it helps persist deadlock detection across SQL Server or Windows restarts.

You need to restart SQL Server for these startup options to take effect. Alternately, you can follow Method 2 to avoid a restart.

  • Method 2 – You can enable using the DBCC Traceon(1222,-1) and DBCC Traceon(1204,-1) command. The -1 parameter in the DBCC TRACEON command indicates to SQL Server that this trace flag should be set globally.

You can check the status of the trace flag using the DBCC TRACESTATUS (1222, -1) and DBCC TRACESTATUS(1204,-1) command.

Publish the SQL Server error logs to CloudWatch

To publish your SQL Server error logs to CloudWatch, complete the following steps:

  1. Install the CloudWatch agent on your EC2 Windows instance.
  2. Create the CloudWatch agent configuration file and specify the SQL Server error log file as a customer log to monitor during the configuration. Configure the CloudWatch agent file at C:\Program Files\Amazon\AmazonCloudWatchAgent.
{
"logs": {
"logs_collected": {
"files": {
"collect_list": [
{"file_path": "C:\\Program Files\\Microsoft SQL Server\\MSSQL15.MSSQLSERVER\\MSSQL\\Log\\ERRORLOG",
"log_group_name": "ERRORLOG",
"log_stream_name": "{instance_id}",
 "timestamp_format": "%Y-%m-%d %H:%M:%S",
  "multi_line_start_pattern": "^\\d{4}-\\d{2}-\\d{2} ",
  "encoding": "utf-16"
}
				]			
			}
				}	
		}
},
  1. To start the CloudWatch agent on the EC2 Windows instance, run Windows PowerShell with administrator rights and run the following command: 
    & "C:\Program Files\Amazon\AmazonCloudWatchAgent\amazon-cloudwatch-agent-ctl.ps1" -a fetch-config -m ec2 -s -c file:configuration-file-p

PS C:\Program Files\Amazon\AmazonCloudWatchAgent> & "C:\Program Files\Amazon\AmazonCloudWatchAgent\amazon-cloudwatch-agent-ctl.ps1" -a fetch-config -m ec2 -s  -c file:Config.JSON
****** processing amazon-cloudwatch-agent ******
Successfully fetched the config and saved in C:\ProgramData\Amazon\AmazonCloudWatchAgent\Configs\file_Config.JSON.tmp
Start configuration validation...
2021/06/22 19:40:26 Reading json config file path: C:\ProgramData\Amazon\AmazonCloudWatchAgent\Configs\file_Config.JSON.tmp ...
Valid Json input schema.
No csm configuration found.
No windows event log configuration found.
Configuration validation first phase succeeded
Configuration validation second phase succeeded
Configuration validation succeeded
AmazonCloudWatchAgent has been stopped
AmazonCloudWatchAgent has been started
  2. Verify the status of the CloudWatch agent on your EC2 Windows instance: 
    PS C:\Program Files\Amazon\AmazonCloudWatchAgent> & $Env:ProgramFiles\Amazon\AmazonCloudWatchAgent\amazon-cloudwatch-agent-ctl.ps1 -m ec2 -a status
{
  "status": "running",
  "starttime": "2021-06-22T19:40:27",
  "configstatus": "configured",
  "cwoc_status": "stopped",
  "cwoc_starttime": "",
  "cwoc_configstatus": "not configured",
  "version": "1.247347.6b250880"
}
PS C:\Program Files\Amazon\AmazonCloudWatchAgent>

Now that the service has started, it starts sending logs to CloudWatch Logs. It takes a few minutes before the first data appears.

You can open the CloudWatch Console in the Region specified to find the new log groups created.

Simulate a deadlock event

Simulate a deadlock transaction on your SQL Server instance by running the following T-SQL code in SSMS:

--Two global temp tables with sample data for demo purposes.
CREATE TABLE ##Employees (
    EmpId INT IDENTITY,
    EmpName VARCHAR(16),
    Phone VARCHAR(16)
)
GO
 
INSERT INTO ##Employees (EmpName, Phone)
VALUES ('Amy', '900-999-1332'), ('Jay', '742-234-2222')
GO
 
CREATE TABLE ##Suppliers(
    SupplierId INT IDENTITY,
    SupplierName VARCHAR(64),
    Location VARCHAR(16)
)
GO
 
INSERT INTO ##Suppliers (SupplierName, Location)
VALUES ('ABC', 'New York'), ('Honest Sourcing', 'Boston')
GO

Next, open two query windows in SSMS and run the following code in each of the sessions. Run the commands on row by row fashion, each session on its own window.

Session 1 Session 2 
Begin Tran;
 
Begin Tran;
 
UPDATE ##Employees
SET EmpName = 'Gani'
WHERE EmpId = 1;
 
UPDATE ##Suppliers
SET Location = N'Toronto'
WHERE SupplierId = 1;
 
UPDATE ##Suppliers
SET Location = N'Columbus'
WHERE SupplierId = 1
 
Blocked
 
UPDATE ##Employees
SET Phone = N'123-456-7890'
WHERE EmpId = 1;
 
Blocked

After you run the code, one of the transactions is processed.

The following screenshot shows that the second transaction is blocked with a deadlock error.

Create a filter pattern and CloudWatch alarm

You can create a filter for specific errors you want to monitor.

  1. On the CloudWatch console, under Logs, choose Log groups.
  2. Choose the SQL Server error logs of your SQL Server DB instance.

The logs are listed in ERRORLOG.

  1. On the Metric filters tab, choose Create metric filter.
  2. In the Filter Pattern section, enter deadlock.
  3. Select any errors to monitor and use that as the filter word.
  4. Choose Assign metric.
  5. Enter deadlock in both the Filter Name and Metric Name fields.
  6. Set the metric value field to 1.
  7. Choose Create Filter.

The following screenshot shows your filter details.

  1. After the deadlock filter is created, choose Create alarm.
  2. On the Specify metric and conditions page, for Metric name, enter deadlock.
  3. For Statistic, choose Minimum.
  4. For Period, choose the time period for the alarm, for example, 1 minute.
  5. In the Conditions section, for Threshold type, choose Static.
  6. For Whenever Deadlock is, choose Greater > threshold.
  7. For Than, enter 0.
  8. Choose Next.
  9. In the Notification section, for Alarm state trigger, choose In alarm.
  10. Select an SNS topic, or choose Create new topic to create an SNS topic using the email address you want to receive alerts.
  11. Choose Next.
  12. In the Name and description section, enter a name and description for your alarm.
  13. Choose Next.
  14. On the Preview and create page, review your alarm configuration, then choose Create alarm.
  15. Confirm the notification email.

You can check the alarm’s status. When the alarm has enough data, the status shows as OK.

After you follow these steps, simulate a deadlock again. The CloudWatch alarm sends an SNS notification to the email that you specified.

Clean up

When you’re finished using the resources in this post, clean up the AWS resources to avoid incurring unwanted charges. Specifically, delete the following resources.

Conclusion

In this post, we showed you how to monitor and get notified when deadlocks occur in SQL Server running on EC2 instances. We used CloudWatch and SNS notifications to instrument the solution. Although we focused on deadlocks, you can extend this solution for monitoring and getting notified on any SQL Server log events or fatal errors.

To learn more about log monitoring on Amazon EC2 using CloudWatch, see Automate IIS and HttpErr Logs to Amazon CloudWatch Using EC2 Systems Manager. You can also monitor SQL Server using CloudWatch and AWS Systems Manager.

To learn about Amazon RDS for SQL Server monitoring for deadlocks, see Monitor deadlocks in Amazon RDS for SQL Server and set notifications using Amazon CloudWatch.

About the Author

Yogi Barot is Microsoft Specialist Senior Solution Architect at AWS, she has 22 years of experience working with different Microsoft technologies, her specialty is in SQL Server and different database technologies. Yogi has in depth AWS knowledge and expertise in running Microsoft workload on AWS.