How do I read server-side traces in 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. You can also use the profiler GUI to open a trace file. You can run a client-side trace to connect to the Amazon RDS DB instance remotely and then collect 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;

   The sys.traces catalog view contains the current running traces on the system. Note the path of the trace value in the output. You need this path for steps 2 and 3.

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

   select * from ::fn_trace_gettable('enter_trace_output_value', default);

   Note: Replace enter_trace_output_value with the trace value output from step 1.

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('enter_trace_output_value', default);

   Note: Replace enter_trace_output_value with the trace value output from step 1.

These commands load the results of all files named RDSTrace.trc in the D:\rdsdbdata\Log directory. This includes 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