Troubleshooting SQL Server Profiler

Here are some problems you may encounter when using SQL Server Profiler:

• If you do not see all SQL Server Profiler event classes on the Events tab of the trace definition property sheet, on the Tools menu, click Options. On the General tab, under Events, select All event classes.
• If you do not see all SQL Server Profiler data columns on the Data Columns tab of the trace definition property sheet, on the Tools menu, click Options. On the General tab, under Events, select All data columns.
• SQL Server Profiler can display the object name (instead of the object ID) if the Server Name and Database ID data columns also appear in your trace.
• When setting filters, a blank include filter includes all items in the SQL Server Profiler output. A filter on a data column is not applied to event classes that do not populate that data column.
• Because the SQL Server Profiler Extended Stored Procedures save trace queue definitions on the server rather than on the client, SQL Server Profiler is unable to edit or start a trace created originally with the extended stored procedures.
• For security reasons, batches containing stored procedures with password arguments are not traced. Instead, an event is produced, which replaces the batch text with a comment.
• In Microsoft® Windows® 95/98, SQL Server Profiler does not accept client configuration changes until the SQL Server Profiler is closed and restarted.
• Due to file locking incompatibilities, Microsoft Windows NT® cannot open trace or script files in a Windows 95/98 shared directory.
• SQL Server Profiler can incur problems accessing files on a remote computer if those files become unavailable.

Here are some common problems you may encounter when replaying a SQL Server Profiler trace:

• Replay errors may occur when logins and users captured in the trace do not exist in the target database. If the logins and users exist in the database, they must have the same permissions as they did in the source (traced) database, and they must have the same password as the SQL Server Profiler user replaying the trace. For security reasons, Microsoft Windows NT® authenticated logins cannot be impersonated.
• Replay errors may occur when the database ID (DBID) of the target database is different from the DBID captured in the trace. To correct this problem, restore a backup of the master database of the source (traced) server onto the target server. Then, restore the user database or databases. As an alternative, the DBID data column can be removed from the trace and the default database set to the target database for each user captured in the trace.
• Replay errors may occur when attempting to replay a trace against a database if it is in a different state than the source (traced) database. Updates may fail if data is missing or changed.
• Unexpected results may be returned, or replay errors may occur, if replaying a trace containing Session events (Connect, Disconnect, and Existing Connection, for example) and the Binary Data column has not been captured. For Session event classes, the Binary Data column contains information required to set ANSI and quoted identifiers.
• System performance may degrade if replaying a trace that contains more concurrent connections than the replay computer can manage. In this case, the trace may be filtered by Application Name, SQL User Name, or another filter if one or more of these data columns were captured in the trace.
• Replaying captured events containing the KILL statement may cause unexpected replay results; the SPID that is terminated may not exist or, if it does exist, the SPID may be assigned to a different user or connection than the one traced originally.
• When replaying a trace file as fast as possible, SPIDs may become blocked, halting the progress of the replay. To free the blocked SPID and allow the trace to continue, kill the blocking SPID.

See Also