Appendix: Common DBA Tasks for Microsoft SQL Server

To run a client-side trace on a SQL Server DB Instance

Writing scripts to create a server-side trace can be complex and is beyond the scope of this document. This section contains sample scripts that you can use as examples. As with a client-side trace, the goal is to create a workload file or trace table that you can open using the Database Engine Tuning Advisor.

The following is an abridged example script that starts a server-side trace and captures details to a workload file. The trace initially saves to the file RDSTrace.trc in the D:\RDSDBDATA\Log directory and rolls-over every 100 MB so subsequent trace files are named RDSTrace_1.trc, RDSTrace_2.trc, etc.

DECLARE @file_name NVARCHAR(245) = 'D:\RDSDBDATA\Log\RDSTrace';
DECLARE @max_file_size BIGINT = 100;
DECLARE @on BIT = 1
DECLARE @rc INT
DECLARE @traceid INT

EXEC @rc = sp_trace_create @traceid OUTPUT, 2, @file_name, @max_file_size
IF (@rc != 0) BEGIN
   EXEC sp_trace_setevent @traceid, 10, 1, @on
   EXEC sp_trace_setevent @traceid, 10, 2, @on
   EXEC sp_trace_setevent @traceid, 10, 3, @on
  …
   EXEC sp_trace_setfilter @traceid, 10, 0, 7, N'SQL Profiler'
   EXEC sp_trace_setstatus @traceid, 1
   END

The following example is a script that stops a trace. Note that a trace created by the previous script continues to run until you explicitly stop the trace or the process runs out of disk space.

DECLARE @traceid INT
SELECT @traceid = traceid FROM ::fn_trace_getinfo(default) 
WHERE property = 5 AND value = 1 AND traceid <> 1 

IF @traceid IS NOT NULL BEGIN
   EXEC sp_trace_setstatus @traceid, 0
   EXEC sp_trace_setstatus @traceid, 2
END
            

You can save server-side trace results to a database table and use the database table as the workload for the Tuning Advisor by using the fn_trace_gettable function. The following commands 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 RDSTrace in the current database:

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

To save a specific rollover file to a table, for example the RDSTrace_1.trc file, specify the name of the rollover file and substitute 1 instead of default as the last parameter to fn_trace_gettable.

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

Once you create a trace, either as a local file or as a database table, you can then run Tuning Advisor against your RDS instance. Microsoft includes documentation on using the Database Engine Tuning Advisor in MSDN. Using Tuning Advisor with RDS is the same process as when working with a standalone, remote SQL Server instance. You can either use the Tuning Advisor UI on your client machine or use the dta.exe utility from the command line. In both cases, you must connect to the RDS DB Instance using the endpoint for the DB Instance and provide your master user name and master user password when using Tuning Advisor.

The following code example demonstrates using the dta.exe command line utility against an RDS DB Instance with an endpoint of dta.cnazcmklsdei.us-east-1.rds.amazonaws.com. The example includes the master user name admin and the master user password test, the example database to tune is named RDSDTA and the input workload is a trace file on the local machine named C:\RDSTrace.trc. The example command line code also specifies a trace session named RDSTrace1 and specifies output files to the local machine named RDSTrace.sql for the SQL output script, RDSTrace.txt for a result file, and RDSTrace.xml for an XML file of the analysis. There is also an error table specified on the RDSDTA database named RDSTraceErrors.

                dta -S dta.cnazcmklsdei.us-east-1.rds.amazonaws.com -U admin -P test -D RDSDTA -if C:\RDSTrace.trc -s RDSTrace1 -of C:\ RDSTrace.sql -or C:\ RDSTrace.txt -ox C:\ RDSTrace.xml -e RDSDTA.dbo.RDSTraceErrors
            

Here is the same example command line code except the input workload is a table on the remote RDS instance named RDSTrace which is on the RDSDTA database.

dta -S dta.cnazcmklsdei.us-east-1.rds.amazonaws.com -U admin -P test -D RDSDTA -it RDSDTA.dbo.RDSTrace -s RDSTrace1 -of C:\ RDSTrace.sql -or C:\ RDSTrace.txt -ox C:\ RDSTrace.xml -e RDSDTA.dbo.RDSTraceErrors

A full list of dta utility command-line parameters can be found in MSDN.

To view the SQL Server Agent log, you can use the RDS console. For information on viewing log files, see Viewing and Listing Database Log Files. You can also use the stored procedure rdsadmin.dbo.rds_read_error_log to view the agent log as described below.

CREATE PROCEDURE [dbo].[rds_read_error_log] @index INT = 0, @type INT = 1,
@search_str1 VARCHAR(255) = NULL, @search_str2 VARCHAR(255) = NULL,
@start_time DATETIME = NULL, @end_time DATETIME = NULL,
@sort_order NVARCHAR(4) = N'asc'                
            

Two parameters are important when you call the rdsadmin.dbo.rds_read_error_log stored procedure:

For example, to read the current SQL Server Agent Log, you execute the following statement, where 0 indicates the current log and 2 indicates the SQL Server Agent Log.

EXEC rdsadmin..rds_read_error_log 0, 2;                        
                    

You can specify the dbo schema name and list the parameters, but it is not necessary. The following three statements are equivalent to the statement in the previous example.

EXEC rdsadmin.dbo.rds_read_error_log 0, 2;
EXEC rdsadmin..rds_read_error_log @index = 0, @type = 2;
EXEC rdsadmin.dbo.rds_read_error_log @index = 0, @type = 2;                
            

To read the last rotated log relative to the current SQL Server Agent Log, you execute the following statement, where 1 indicates the previous log and 2 indidates the SQL Server Agent Log.

EXEC rdsadmin..rds_read_error_log 1, 2;                        
                    

If a rotated log does not exist, for example if the SQL Server Agent Log has never been rotated, then the statement returns the following error message.

Msg 22004, Level 16, State 1, Line 0
xp_readerrorlog() returned error 2, 'The system cannot find the file specified.'