Replaying Traces

Replaying Traces

When you create or edit a trace, you can save the trace to replay it later. SQL Profiler features a multithreaded playback engine that can simulate user connections and SQL Server Authentication, allowing the user to reproduce the activity captured in the trace. Therefore, replay is useful when troubleshooting an application or process problem. When you have identified the problem and implemented corrections, run the trace that found the potential problem against the corrected application or process, then replay the original trace to compare results.

Trace replay supports debugging using break points and run-to-cursor, which especially improves the analysis of long scripts. For more information, see Single-Stepping Traces.

Replay Requirements

In addition to any other event classes you want to monitor, the following event classes must be captured in a trace to allow the trace to be replayed:

• Connect
  
• CursorExecute (only required when replaying server-side cursors)
  
  
• CursorOpen (only required when replaying server-side cursors)
  
  
• CursorPrepare (only required when replaying server-side cursors)
  
  
• Disconnect
  
  
• Exec Prepared SQL (only required when replaying server-side prepared SQL statements)
  
  
• ExistingConnection
  
  
• Prepare SQL (only required when replaying server-side prepared SQL statements)
  
  
• RPC:Starting
  
  
• SQL:BatchStarting

In addition to any other data columns you want to capture, the following data columns must be captured in a trace to allow the trace to be replayed:

• Application Name
  
  
• Binary Data
  
  
• ClientProcessID or SPID
  
  
• Database ID
  
  
• Event Class
  
  
• Event Sub Class
  
  
• Host Name
  
  
• Integer Data
  
  
• Server Name
  
  
• SQL User Name
  
  
• Start Time
  
  
• Text

  Note  Use the sample trace template SQLProfilerTSQL_Replay for traces capturing data for replay.

In order to replay a trace against a computer running Microsoft® SQL Server™ (the target), other than the computer originally traced (the source):

• All logins and users contained in the trace must already be created on the target and in the same database as the source.
  
  
• All logins and users in the target must have the same permissions they had in the source.
  
  
• All login passwords must be the same as the user executing the replay.

Replaying events associated with missing or incorrect logins will result in replay errors, but the replay operation will continue.

In order to replay a trace against an instance of SQL Server (the target), other than the computer originally traced (the source), either:

• Database IDs on the target must be the same as those on the source. This can be accomplished by creating from the source a backup of the master database, and any user databases referenced in the trace, and restoring them on the target.
  
  
• The default database for each login contained in the trace must be set (on the target) to the respective target database of the login. For example, the trace to be replayed contains activity for the login, Fred, in the database Fred_Db on the source. Therefore, on the target, the default database for the login, Fred, must be set to the database that matches Fred_Db (even if the database name is different). To set the default database of the login, use sp_defaultdb system stored procedure.

Replay Options

Before replaying a captured trace, you can specify:

• Server

  The server is the name of the instance of SQL Server against which you want to replay the trace. The server must adhere to the replay requirements previously mentioned.

• Output file name

  The output file contains the result of replaying the trace for later viewing. If Progress is selected, then the output file can be also replayed at a later time. By default, SQL Profiler displays only the results of replaying the trace to the screen.

• Replay Options
  • Replay events in the order they were traced. This option enables debugging.

    Specify to replay events in the order they were traced. This allows you to use debugging methods such as stepping through each trace.

  • Replay events using multiple threads. This option optimizes performance and disables debugging.

    Specify to replay events using multiple threads. This optimizes performance, but debugging is disabled.

  • Display replay results

    Specify to display the results of the replay. This is the default option. If the trace you are replaying is very large, you may want to disable this to save disk space.

Note  For best replay performance, it is recommended that you select to replay events using multiple threads, and do not select to display the replay results.

Replay Considerations

SQL Profiler cannot replay traces:

• Captured from connections that connected to an instance of SQL Server using Windows Authentication Mode. For information about Windows Authentication Mode, see Authentication Modes.
  
  
• Containing replication and other transaction log activity.
  
  
• Containing operations that involve globally unique identifiers (GUID). For information about GUIDs, see Autonumbering and Identifier Columns.
  
  
• Containing operations on text, ntext, and image columns involving the bcp utility, BULK INSERT, READTEXT, WRITETEXT, and UPDATETEXT statements, and full-text operations.
  
  
• Containing session binding: sp_getbindtoken and sp_bindsession system stored procedures.

Additionally, SQL Profiler cannot replay SQL Trace .log files that contain SQL Server 6.5 server-side cursor statements (sp_cursor).

Unexpected results or replay errors can occur when replaying a trace containing the Sessions event classes (Connect, Disconnect, and Existing Connection) if the Binary Data data column is not also captured in the trace. The Binary Data data column, for the Session event classes, contains information required to set ANSI nulls, ANSI padding, cursor close on commit, concat null yields null, and quoted identifier session settings. For more information, see SET.

When replaying a trace containing concurrent connections, SQL Profiler creates a thread for each connection. Therefore, system performance of the computer replaying the trace can be affected if the trace contains many concurrent connections. To reduce the effect on system performance, filter the trace by specifying a value(s) for the Application Name, SQL User Name, or another data column captured in the trace, to focus the trace on only those events you need to monitor.

Note  If you do not use the provided replay template (SQLProfilerTSQL-Replay), you may encounter difficulties capturing the current database context. For more information, see Troubleshooting SQL Profiler.

To replay a trace table