Capture Queries Executed on SQL Server RDS

In this blog, we will explore how we can set up a server-side trace against AWS DB instance. Extended events are now supported in all version starting from SQL server 2012

Step 1: The script below starts a server-side trace and captures queries executed on RDS 

in the D:\rdsdbdata\Log directory - including all rollover files such as RDSTrace_1.trc, RDSTrace_2.trc, etc.( this path can't be changed). We will be capturing RPC: Completed, SP: StmtCompleted &  SQL: BatchCompleted events

 

/****************************************************/
/* Created by: SQL Server 2019 Profiler          */
/* Date: 06/14/2021  03:40:30 PM         */
/****************************************************/
-- Create a Queue
DECLARE @rc INT
DECLARE @TraceID INT
DECLARE @maxfilesize BIGINT

SET @maxfilesize = 100

EXEC @rc = sp_trace_create @TraceID OUTPUT
	,0
	,N'D:\RDSDBDATA\Log\RDSTrace'
	,@maxfilesize
	,NULL

IF (@rc != 0)
	GOTO error

-- Set the events
DECLARE @on BIT

SET @on = 1

EXEC sp_trace_setevent @TraceID
	,10
	,1
	,@on

EXEC sp_trace_setevent @TraceID
	,10
	,3
	,@on

EXEC sp_trace_setevent @TraceID
	,10
	,11
	,@on

EXEC sp_trace_setevent @TraceID
	,10
	,12
	,@on

EXEC sp_trace_setevent @TraceID
	,10
	,13
	,@on

EXEC sp_trace_setevent @TraceID
	,10
	,35
	,@on

EXEC sp_trace_setevent @TraceID
	,45
	,1
	,@on

EXEC sp_trace_setevent @TraceID
	,45
	,3
	,@on

EXEC sp_trace_setevent @TraceID
	,45
	,11
	,@on

EXEC sp_trace_setevent @TraceID
	,45
	,12
	,@on

EXEC sp_trace_setevent @TraceID
	,45
	,13
	,@on

EXEC sp_trace_setevent @TraceID
	,45
	,28
	,@on

EXEC sp_trace_setevent @TraceID
	,45
	,35
	,@on

EXEC sp_trace_setevent @TraceID
	,12
	,1
	,@on

EXEC sp_trace_setevent @TraceID
	,12
	,3
	,@on

EXEC sp_trace_setevent @TraceID
	,12
	,11
	,@on

EXEC sp_trace_setevent @TraceID
	,12
	,12
	,@on

EXEC sp_trace_setevent @TraceID
	,12
	,13
	,@on

EXEC sp_trace_setevent @TraceID
	,12
	,35
	,@on

-- Set the Filters
DECLARE @intfilter INT
DECLARE @bigintfilter BIGINT

EXEC sp_trace_setfilter @TraceID
	,11
	,0
	,6
	,N'loginName' --pass login name
	-- Set the trace status to start

EXEC sp_trace_setstatus @TraceID
	,1

-- display trace id for future references
SELECT TraceID = @TraceID

GOTO finish

error:

SELECT ErrorCode = @rc

finish:
GO

Step 2: Find the trace ID of the previously created trace by issuing the below command

SELECT id
	,path
FROM sys.traces
WHERE id > 1

Step 3: Specify the trace ID captured in Step 2 to stop the trace

DECLARE @TraceID INT;

SET @TraceID = 2;-- specify value from Step 2 

EXEC sp_trace_setstatus @traceid = @TraceID
	,@status = 0;-- stop trace

Step 4:  Load the results of all files named RDSTrace.trc in the D:\rdsdbdata\Log directory, including all rollover files like RDSTrace_1.trc, into a table named Queries in the current database; which can be queried later

SELECT *
INTO Queries
FROM fn_trace_gettable('D:\rdsdbdata\Log\RDSTrace.trc', DEFAULT);

Step 5: Delete the trace 

DECLARE @TraceID INT;

SET @TraceID = 2;-- specify value from Step 2

EXEC sp_trace_setstatus @traceid = @TraceID
	,@status = 2;-- delete trace

Done!! I hope this helps 😊