Replaying Traces

When you create or edit a trace, you can save the trace to replay it later. SQL Server 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
• Connection ID 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 definition Sample 6-TSQL for Replay as a template for traces to capture 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 a computer running 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 computer running SQL Server against which you want to replay the trace. The server must adhere to the replay requirements previously mentioned.

• Sync. level

  The synchronization level is the degree of accuracy that SQL Server Profiler enforces when starting events based on the start time captured in the trace.

  • Full Synchronization ensures that all events across all connections are started in the order in which they originally started. Even if an event takes longer to execute, for example, because the target server is slower than the source, causing other events on the connection to be delayed, all other events on all other connections that should have already started will not start until the delayed events that should have executed before the other events have started. This is the default synchronization level.
  • Partial Synchronization allows events across all connections to start at their expected start time captured in the trace, even if events that have earlier start times on other connections have not yet started because they have been delayed. However, events will not start if events on other connections with earlier start times are delayed too much.
  • No Synchronization allows all events on each connection to be started as soon as the previous event on the same connection has completed. There is no synchronization between events on different connections.
• Replay rate

  The replay rate specifies the timing SQL Server Profiler uses to replay the events.

  • As fast as possible replays the events in the trace as quickly as the computer running SQL Server can process them. There is no timing maintained between events: as soon as one event completes, the next event is started. This is the default replay rate.
  • Maintain interval between events replays the events in the trace with the same duration of time between events as originally occurred.

    For example, Event 1 occurs three seconds after the trace started, Event 2 five seconds after the trace started, and Event 3 ten seconds after the trace started. SQL Server Profiler will replay Event 1 three seconds after the start of the trace, Event 2 two seconds after Event 1, and Event 3 five seconds after Event 2.

    If you replay the trace against a different computer than the source, then the overall execution time can vary because the length of time of each event can vary.

  • Maintain relationship to start time replays the events in the trace at the same time they originally occurred. If an event takes longer to execute than it did originally, SQL Server Profiler will reduce the time interval between events to ensure the events occur at the same time relative to the start of the trace. Using the previous example, if Event 2 takes three seconds longer to execute than it originally did, then the time interval between Event 2 and Event 3 is reduced by three seconds to ensure that Event 3 starts ten seconds after the start of the trace.
• Output file

  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 Server Profiler displays only the results of replaying the trace to the screen.

• Display

  The display options determine what the user sees in the replay pane of the trace window and, if specified, what is saved to the replay output file:

  • Progress causes the replayed events to be traced and displayed.
  • ODBC errors causes any ODBC errors incurred by replay events to be displayed.
  • Internal errors causes replay errors incurred by replay events to be displayed.
  • Results causes the result set(s) returned from replay events to be displayed.

Replay Considerations

SQL Server Profiler cannot replay traces:

• Captured from connections that connected to SQL Server using Windows NT® Authentication Mode. For information about Windows NT Authentication Mode, see Authentication.
• Containing replication and other transaction log activity.
• Containing operations that involve globally unique identifiers. For information about globally unique identifiers, see Auto-Numbering 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 Server 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 Server 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.

To replay a trace

To set the synchronization level

To set a replay rate

To select an output file

See Also