How do I read server-side traces in Amazon RDS for SQL Server?

2 minute read
0

I want to read server-side traces in Amazon Relational Database Service (Amazon RDS) for SQL Server.

Short description

Amazon RDS is a managed service, so it doesn't give users access to the operating system. If you use a self-hosted installation of SQL Server, then you have access to the host, and you can use the profiler GUI to open a trace file. You can run a client-side trace by connecting to the Amazon RDS DB instance remotely and collecting the trace. The trace file is then created on a remote client. Because the profiler is a client-side tool, the trace stops if the connection is lost. To avoid lost connections, you can run a server-side trace.

Resolution

Read server-side traces using T-SQL statements

1.    Identify the currently running traces in the server by running a command similar to the following T-SQL statement:

select * from sys.traces;

Note: The sys.traces catalog view contains the current running traces on the system.

2.    Read the trace file by running a command similar to the following fn_trace_gettable function:

select * from ::fn_trace_gettable('D:\rdsdbdata\log\rdstest.trc', default);

3.    Optionally, you can save server-side trace results to a database table. Then, you can use the database table as the workload for the Tuning Advisor by running a command similar to the following fn_trace_gettable function:

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

Note: These commands load the results of all files named RDSTrace.trc in the D:\rdsdbdata\Log directory—including all rollover files such as RDSTrace_1.trc—into a table named RDSTrace that is located in the current database.

Trace and dump files accumulate, and they can fill up disk space. By default, Amazon RDS purges trace and dump files that are more than seven days old. You can modify the retention period for trace files by using the rds_set_configuration stored procedure to set the trace file retention. The following stored procedure sets the trace file retention period to 24 hours (1440 minutes):

exec rdsadmin..rds_set_configuration 'tracefile retention', 1440;

Related information

Working with trace and dump files

Microsoft SQL Server database log files

Analyzing your database workload on an Amazon RDS for SQL Server DB instance with Database Engine Tuning Advisor

Amazon RDS for Microsoft SQL Server