AI-powered incident analysis for Amazon RDS using automated forensic artifacts

When your database has an incident at 3 AM, or performance degrades unexpectedly during peak hours, traditional monitoring tools tell you that something went wrong. But they rarely give you the complete picture of why. Amazon CloudWatch metrics show you the symptoms: elevated CPU, increased queue depth, higher latency. But the root cause is often lost by the time someone investigates: the specific queries contending for locks, the session that’s blocking others, or the replication lag building silently.

In this post, we demonstrate a serverless approach to continuous forensic artifact collection for Amazon Relational Database Service (Amazon RDS) and Amazon Aurora databases. By capturing point-in-time snapshots of database internals on a cadence and storing them in Amazon Simple Storage Service (Amazon S3), you create a time-series record that AI tools can analyze in seconds. This turns what was hours of manual investigation into an instant conversation. This approach complements Amazon CloudWatch Database Insights and other database monitoring frameworks by capturing a historical record with more complete information, such as full database process lists, full engine statistics, and full query lock lists. With this record, you can perform rapid AI-assisted root cause analysis.

This solution achieves two primary objectives:

• Capture telemetry history – Automatically collect point-in-time forensic snapshots from your RDS/Aurora databases (active sessions, lock waits, wait statistics, query performance) on a recurring schedule and store them in S3 as structured JSON artifacts.
• Analyze with AI agents – Use AI coding assistants (Kiro or similar AI tools) with S3 and CloudWatch access to correlate artifacts across time windows and perform root cause analysis in seconds.

Solution overview

If you manage database instances, you’ve encountered this scenario: at 3 AM a team reports that their application was slow between 2:00 and 2:15 AM. You check CloudWatch and confirm elevated read latency. But why?

To answer that, you need data that traditional monitoring doesn’t capture:

• Who was connected? The full list of active sessions, their state, wait types, and the queries they were running.
• What was contending? Lock waits, blocking chains, deadlock victims.
• What changed? Wait statistics shifts, buffer pool hit ratios, query plan regressions.

For example, on Amazon RDS for SQL Server, this means diagnostic information from dynamic management views (DMVs) such as sys.dm_exec_requests, sys.dm_exec_query_stats, sys.dm_os_wait_stats, and sp_who2. These DMVs show the current state of the engine, but if you don’t capture them at that moment of the incident, the evidence is gone.

The concept is straightforward: run diagnostic SQL scripts on your target database instance on a recurring cadence and store the output in S3. We refer to these outputs as artifacts, timestamped snapshots of database internals that paint a complete picture when analyzed together. The key is to focus on lightweight queries that can be tolerated on a once-per-minute or once-every-two-to-five-minutes basis.

This solution demonstrates two key capabilities:

1. Centralized storage in Amazon S3 – all artifacts from your target instances flow to a single bucket, organized by instance ID and timestamp.
2. AI-powered analysis – large language models with access to your S3 artifacts and CloudWatch metrics can correlate, summarize, and diagnose in seconds.

In this post, we demonstrate the implementation using an Amazon RDS for SQL Server instance. The same architecture applies to Aurora PostgreSQL, Aurora MySQL, Amazon RDS for Oracle, and Amazon DocumentDB (with MongoDB compatibility). You can upload engine-appropriate scripts to the S3 scripts bucket and the solution handles the rest.

Architecture

The following diagram illustrates the solution workflow:

The solution uses a fully serverless architecture:

• Amazon EventBridge triggers a scheduler rule on a configurable cadence (for example, every minute).
• Orchestrator AWS Lambda discovers the target RDS instance using DescribeDBInstances API and dispatches work.
• Amazon Simple Queue Service (Amazon SQS) provides fan-out for parallel processing when targeting multiple instances.
• Worker Lambda connects to the RDS SQL Server instance, executes diagnostic scripts, and writes results to S3.
• S3 Scripts Bucket stores your diagnostic SQL scripts organized by engine (for example, sqlserver/).
• S3 Artifacts Bucket receives JSON results organized as {db-instance-id}/{timestamp}/{script-name}.json.
• AWS Secrets Manager securely provides database credentials.
• Amazon CloudWatch captures Lambda execution metrics and database performance data for correlation.

The orchestrator Lambda automatically detects each instance’s engine type from the describe-db-instances API response and routes work accordingly. This means the same deployment supports other engines: you can add scripts for each engine (mysql/, postgres/, sqlserver/, oracle/), and the solution handles routing automatically.

What to collect: SQL Server diagnostic scripts

The following table shows a sample list of DMVs used in the scripts to gather diagnostic information for SQL Server. Each row captures a different dimension of database state:

SQL Server

The complete list of sample scripts is available in the GitHub repository.

This solution can be extended to other engines by uploading engine-appropriate scripts to the corresponding prefix in your S3 scripts bucket (postgres/, mysql/, oracle/). For PostgreSQL, scripts query pg_stat_activity, pg_locks, and pg_stat_statements. For MySQL, scripts query information_schema.innodb_trx, SHOW ENGINE INNODB STATUS, and the performance_schema. The solution detects the engine automatically and routes to the correct scripts.

Prerequisites

To use this solution, you need:

1. An AWS account with AWS Identity and Access Management (IAM) permissions for Lambda, S3, SQS, Secrets Manager, RDS, AWS CloudFormation, ECR, and KMS.
2. AWS Command Line Interface (AWS CLI) installed and configured.
3. Docker installed (for building the Lambda container image).
4. Amazon Virtual Private Cloud (Amazon VPC) with subnets that have network access to your target RDS SQL Server instance. The CloudFormation template in the repository creates the required security groups and configures Lambda VPC access.
5. NAT Gateway or VPC endpoints for Lambda to reach S3, SQS, and Secrets Manager.
6. An RDS for SQL Server instance to target and VIEW SERVER STATE granted to the forensic database user.

Getting started

This walkthrough targets a single RDS for SQL Server instance. After validating the setup, you can expand to additional instances or engines by uploading the appropriate scripts and creating the forensic user on each target.

Clone the project repository

git clone https://github.com/aws-samples/sample-rds-db-forensic-artifacts.git

Set environment variables

export AWS_ACCOUNT_ID=$(aws sts get-caller-identity --query Account --output text)
export AWS_REGION=us-east-1

Create S3 buckets

Create two S3 buckets: one to store the diagnostic SQL scripts, and one to store the collected forensic artifacts.

aws s3 mb s3://forensic-scripts-${AWS_ACCOUNT_ID} --region ${AWS_REGION}
aws s3 mb s3://forensic-artifacts-${AWS_ACCOUNT_ID} --region ${AWS_REGION}

Upload SQL Server diagnostic scripts

Upload the sample DMV query scripts to the scripts S3 bucket so the Lambda worker can retrieve and execute them against your RDS instance.

aws s3 sync example-scripts/sqlserver/ s3://forensic-scripts-${AWS_ACCOUNT_ID}/sqlserver/

Create database credentials in Secrets Manager

Store the forensic monitoring user’s database password securely in Secrets Manager, where the Lambda function retrieves it at runtime.

aws secretsmanager create-secret \
--name rds-forensics-db-password \
--description "Database password for RDS forensic analysis" \
--secret-string '{"password":"YourSecurePassword"}'

export SECRET_ARN=$(aws secretsmanager describe-secret \
--secret-id rds-forensics-db-password \
--query ARN --output text)

Create the forensic user on your SQL Server instance

Connect to your RDS SQL Server instance with master user and create a monitoring forensic user with VIEW SERVER STATE permission.

CREATE LOGIN forensic_user WITH PASSWORD = 'YourSecurePassword';
CREATE USER forensic_user FOR LOGIN forensic_user;
GRANT VIEW SERVER STATE TO forensic_user;
GRANT VIEW ANY DEFINITION TO forensic_user;

Build and push the Lambda container image

The container packages the database drivers needed to connect to your RDS instances, which aren’t available in the standard Lambda runtime.

cd lambda-container
./build-and-push.sh ${AWS_ACCOUNT_ID} ${AWS_REGION}
export IMAGE_URI="${AWS_ACCOUNT_ID}.dkr.ecr.${AWS_REGION}.amazonaws.com/rds-forensics:latest"

Deploy the CloudFormation stack

The CloudFormation template creates the Lambda functions, SQS queue, IAM roles, security groups, and Amazon EventBridge rule.

aws cloudformation deploy \
--template-file cloudformation.yaml \
--stack-name rds-forensics \
--parameter-overrides \
ScriptsBucket=forensic-scripts-${AWS_ACCOUNT_ID} \
ArtifactsBucket=forensic-artifacts-${AWS_ACCOUNT_ID} \
DatabaseUsername=forensic_user \
DatabasePasswordSecretArn=${SECRET_ARN} \
LambdaImageUri=${IMAGE_URI} \
VpcId=vpc-xxxxx \
SubnetIds=subnet-xxxxx,subnet-yyyyy \
--capabilities CAPABILITY_NAMED_IAM

Allow the Lambda security group to reach your RDS SQL Server instance on port 1433.

Test with your target instance

aws lambda invoke \
--function-name rds-forensics-main \
--payload '{"DBInstanceIdentifier": "my-sqlserver-instance"}' \
--cli-binary-format raw-in-base64-out \
response.json

Verify artifacts in S3

aws s3 ls s3://forensic-artifacts-${AWS_ACCOUNT_ID}/my-sqlserver-instance/ --recursive

You should see output organized by timestamp:

my-sqlserver-instance/2026-07-01_03-15-00/get_sessions.sql.json
my-sqlserver-instance/2026-07-01_03-15-00/dm_os_wait_stats.sql.json
my-sqlserver-instance/2026-07-01_03-15-00/check_blocked_sessions.sql.json
my-sqlserver-instance/2026-07-01_03-15-00/get_top_most_costly_execs_cpu.sql.json
my-sqlserver-instance/2026-07-01_03-15-00/metadata.json

Enable scheduled collection

The CloudFormation template creates an Amazon EventBridge rule (disabled by default). Enable it to start recurring collection:

aws events enable-rule --name rds-forensics-daily-scan

Output format

Each script execution produces a JSON artifact:

{
"script": "check_blocked_sessions.sql",
"execution_time": "2026-07-01T03:15:22Z",
"duration_seconds": 0.45,
"rows_returned": 3,
"columns": ["blocked_session", "blocker_session", "wait_type", "wait_time_ms", "blocked_query", "blocker_query"],
"data": [
{"blocked_session": 55, "blocker_session": 42, "wait_type": "LCK_M_S", "wait_time_ms": 15230, "blocked_query": "SELECT * FROM orders WHERE...", "blocker_query": "UPDATE inventory SET qty = qty - 1..."},
{"blocked_session": 61, "blocker_session": 42, "wait_type": "LCK_M_S", "wait_time_ms": 14890, "blocked_query": "SELECT COUNT(*) FROM orders...", "blocker_query": "UPDATE inventory SET qty = qty - 1..."}
]
}

The S3 key structure makes time-based queries natural:

s3://forensic-artifacts-bucket/
└── my-sqlserver-instance/
├── 2026-07-01_03-14-00/
│ ├── get_sessions.sql.json
│ ├── dm_os_wait_stats.sql.json
│ ├── check_blocked_sessions.sql.json
│ └── metadata.json
├── 2026-07-01_03-15-00/
│ ├── get_sessions.sql.json
│ ├── dm_os_wait_stats.sql.json
│ ├── check_blocked_sessions.sql.json
│ └── metadata.json
└── ...

AI analysis: setting up your agentic front end

With artifacts in S3 and metrics in CloudWatch, you use an AI assistant to perform root cause analysis. The AI assistant needs execution access to the AWS CLI with permissions to read from the artifacts S3 bucket and query CloudWatch metrics. In this walkthrough, we use Kiro as the agentic front end. You can use other AI tools such as Claude Code, or any AI assistant with AWS CLI access, in a similar way.

Setting up Kiro

Install Kiro and sign in with your AWS Builder ID. Kiro provides an agentic coding environment that can execute AWS CLI commands, read S3 objects, and query CloudWatch, all the capabilities needed for forensic analysis.

1. Verify Kiro can access your AWS environment. Kiro uses your local AWS CLI configuration (~/.aws/credentials). Use an IAM user that has read access to the artifacts S3 bucket and CloudWatch metrics, this is distinct from the database monitoring forensic user created in the previous setup steps.
2. Make sure your credentials have permissions to:
   • s3:GetObject and s3:ListBucket on the artifacts bucket.
   • cloudwatch:GetMetricData for RDS metrics.

Open Kiro and start a new session,

kiro-cli chat

In the Kiro session, prompt for analysis:

“My database prod-sqlserver-01 was slower between 2:00 and 2:15 AM today. Analyze the forensic artifacts from that time window in s3://amzn-s3-demo- artifacts-bucket/prod-sqlserver-01/. Correlate with CloudWatch metrics for CPU, read latency, and queue depth. Tell me what queries were contending, what was causing the blocking, and what I can do to fix it.”

Within seconds, Kiro can:

• List S3 objects in the relevant time-range prefixes.
• Read the JSON artifacts: sessions, active queries, lock waits, and wait statistics.
• Query CloudWatch for corresponding metric data points.
• Correlate the evidence and identify the root cause.
• Recommend actions such as creating indexes, breaking batch operations, or adjusting application logic.

Example analysis output from Kiro:

Usage examples

The following scenarios demonstrate how customers can use this solution to diagnose real-world database incidents.

Example 1: Correlating tempdb pressure with specific sessions and queries

The application team reports intermittent query timeouts on their SQL Server database during business hours. CloudWatch metrics show disk queue length spikes, but the root cause is unclear, multiple sessions are active and any of them could be consuming tempdb.

With forensic artifacts capturing tempdb usage per transaction every minute, the DBA prompts Kiro:

RDS prod-sqlserver-01 is experiencing intermittent query timeouts during business hours. What is consuming tempdb and which sessions are responsible?

Kiro reads the tempdb and session artifacts and responds:

Finding:
At 10:23, session 1847 (app: ReportingService, host: report-server-03) began
spilling to tempdb. Allocated 12 GB in tempdb over 8 minutes.

Session Details:
SPID Status Wait Type CPU (ms) Reads Tempdb (GB) Host
1847 running - 284,300 18,430,221 12.4 report-server-03
902 suspended PAGEIOLATCH_SH 12,400 84,201 0.0 app-server-01
915 suspended PAGEIOLATCH_SH 8,200 62,100 0.0 app-server-02
1023 suspended PAGEIOLATCH_SH 5,100 41,800 0.0 app-server-01

Query (session 1847):
SELECT o.order_id, c.customer_name, p.product_name
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN products p ON o.product_id = p.product_id
WHERE o.order_date > DATEADD(year, -2, GETDATE())

Root Cause:
The query requested a 4 GB memory grant but the actual data volume required 12 GB, causing a hash join spill to tempdb. The spill saturated disk I/O, blocking other sessions waiting on PAGEIOLATCH_SH (page I/O latch waits on tempdb files).

Impact:
Tempdb I/O: 94% of disk throughput consumed (10:23-10:31)
Affected sessions: 23 sessions timed out waiting for disk I/O
Duration: 8 minutes
Pattern: Repeats daily 10:00-11:00 (reporting batch window)

Recommendations:
1. Create supporting index to convert hash join to nested loop:
CREATE INDEX IX_orders_customer_product
ON orders(customer_id, product_id) INCLUDE (order_date)

2. Limit memory grants for reporting workload:
EXEC msdb.dbo.rds_alter_workload_group
@group_name = 'ReportingGroup', @REQUEST_MAX_MEMORY_GRANT_PERCENT = 15;
EXEC msdb.dbo.rds_alter_resource_governor_configuration;

3. Add additional tempdb data files to distribute I/O:
Current: 4 files. Recommended: 8 files (1 per vCPU up to 8)

4. Schedule report during off-peak hours or run against a read replica

This level of detail, which specific session spilled to tempdb, how much, and the exact query is captured in the forensic snapshots and can be correlated across time windows by the AI assistant.

Example 2: Identifying transaction log growth caused by uncommitted transactions

A DBA notices that transaction log backups for their production RDS SQL Server are growing significantly larger over the past week. The database isn’t growing in data size, but log space usage keeps increasing. The DBA needs to identify which sessions are holding open transactions preventing log truncation.

The DBA prompts Kiro:

Transaction log backups on RDS prod-sqlserver-01 have doubled in size this week. What’s preventing log truncation and which sessions are responsible?

Kiro analyzes the transaction log and session artifacts and responds:

Finding:
The log_truncation_holdup_reason has been ACTIVE_TRANSACTION continuously
since Monday at 14:22.

Root Cause:
Session 445 (app: LegacySync, host: sync-server-01) opened a transaction at
Monday 14:22:03 and has not committed or rolled back since.

BEGIN TRANSACTION
UPDATE sync_status SET last_run = GETDATE() WHERE job_id = 7823
-- Transaction never committed. Connection returned to pool without COMMIT.

Session Details:
SPID Status Duration App Host Last Command
445 sleeping 5 days LegacySync sync-server-01 UPDATE

Session status: "sleeping" in every snapshot for the past 5 days.

Impact:
Active VLF count: 47 → 1,203 (over 5 days)
Log file size: 8 GB → 67 GB (storage billing increase)
Storage consumed: 59 GB additional allocated storage
Recovery risk: Crash recovery would take significantly longer with 1,203 VLFs
Storage limit: Uncontrolled log growth can trigger storage-full state, causing database unavailability until storage is increased

Recommendations:
1. Terminate the orphaned session after verifying rollback impact:
SELECT database_transaction_log_bytes_used FROM sys.dm_tran_database_transactions WHERE database_id = DB_ID()
-- Log_used is minimal (single-row UPDATE), safe to terminate:
KILL 445

2. Fix the LegacySync application to commit all transactions before
releasing connections back to the pool

3. Add connection pool validation that rolls back uncommitted transactions
on connection return

4. Create an alert when log_truncation_holdup_reason = ACTIVE_TRANSACTION
persists for more than 1 hour

Example 3: Trending analysis for capacity planning

Beyond incident response, forensic artifacts enable proactive analysis. A DBA wants to understand if their database is approaching connection limits during business hours.

Are we approaching any connection or resource limits on RDS prod-sqlserver-01? Analyze the last 7 days of peak business hours activity and tell me if there are any trends I should worry about.

Kiro responds:

Finding:
Peak concurrent sessions (business hours) over 7 days:
Weekday range: 412 (Mon) → 523 (Fri), growing ~5% daily
Weekend range: 142-187 (minimal load)

Trend:
Weekday peak sessions growing at ~5% per day (27 additional sessions/day).

Risk:
Current max worker threads setting: 512
At this growth rate, the limit will be reached within 2 weeks.

Recommendations:
1. Increase max worker threads to 1024:
Modify the DB parameter group: set 'max worker threads' = 1024
(requires instance reboot to apply)

2. Implement connection pooling at the application layer to reduce
concurrent session count

3. Set up a CloudWatch alarm when active sessions exceed 450 (85% of limit)

This type of analysis, spanning days of data and identifying trends is made possible by having centralized, structured artifacts readily available for AI-assisted correlation.

Note: AI assistant responses can vary based on the model used, prompt phrasing, and the specific artifacts available. The examples shown are representative of the type of analysis possible, actual output format and detail level can differ.

Security considerations

• Least privilege IAM: Worker Lambdas only have SELECT/VIEW access on databases and write access to the artifacts S3 bucket.
• Secrets Manager: Database credentials are never stored in code or environment variables in plaintext.
• VPC deployment: Lambda functions are deployed inside your VPC to access private RDS instances.
• S3 encryption: Artifacts are encrypted at rest using SSE-S3.
• Script bucket permissions: Restrictive bucket policy. Only the Lambda execution role can read scripts.

Best practices and recommendations

• Start small: Target one database instance first. Expand after validating the setup works.
• Tune cadence to workload: Every minute captures fast-moving contention. Every 5 minutes might suffice for long-running analytics workloads.
• Set retention policies: Use S3 Lifecycle rules to transition artifacts to Glacier after 30 days and delete after 90.
• Monitor for collection failures: Set CloudWatch alarms on worker Lambda errors and Dead-Letter Queue (DLQ).
• Bring your own scripts: Add custom diagnostic queries specific to your workload patterns.
• Enable engine-specific prerequisites (for example, pg_stat_statements extension for PostgreSQL, performance_schema for MySQL).
• Use database-scoped queries for PostgreSQL: The solution supports forensic_query_type=database, forensic_database=all directives to run scripts against specific or all databases in a PostgreSQL cluster.
• Create the forensic user on the target instance with appropriate read permissions.
• Make sure the Lambda security group can reach the database port.

Limitations

• Lambda functions have a maximum execution timeout of 15 minutes per instance. Databases with a large number of diagnostic scripts or long-running queries might need script optimization.
• Concurrent Lambda executions are subject to your AWS account limit (default 1000). For fleets requiring more than 1000 concurrent executions, request a concurrency quota increase through the AWS Service Quotas console.
• SQS batch size is limited to 10 messages per batch. The orchestrator handles this automatically by sending messages in batches.

Clean up

To avoid ongoing charges, delete the resources you created with this solution:

1. Delete the S3 buckets created for this solution.
2. Delete the Amazon EventBridge rule created for this solution.
3. Delete the AWS Lambda functions.
4. Delete the SQS Queue.
5. Delete the CloudFormation stack created for this solution.
6. Delete ECR repository.

Conclusion

The gap between knowing something went wrong and understanding why is often the most expensive part of an incident. By continuously capturing database forensic artifacts into S3 and unlocking AI-powered analysis, you transform post-incident investigation from hours of manual toil into a conversational query that returns answers in seconds.

The solution scales from a single instance to multiple, and the artifacts you collect become more valuable over time as AI tools improve their ability to correlate patterns across time windows.

Stop losing forensic evidence to time. Start collecting artifacts today.

About the authors