Microsoft SQL Trace Help Contents

 Microsoft SQL Trace Overview

 How To…

 About The…


Microsoft SQL Trace Help Contents

 Microsoft SQL Trace Overview

 How To…

Connect to or Disconnect From a Server

Create a Filter

Edit a Filter

Edit a Trace Icon

Delete a Filter

Toggle Filter Status Display

Start, Pause, or Stop a Filter

Audit a Server

Launch an External Tool

Choose Window Options

 About The…


Microsoft SQL Trace Help Contents

 Microsoft SQL Trace Overview

 How To…

 About The…

Build Filter On Applications Dialog Box

Build Filter On Host Names Dialog Box

Build Filter On User Logins Dialog Box

Connect Server Dialog Box

Delete Selected Filters Dialog Box

Filter Status Pane

New Filter and Filter Properties Dialog Boxes

Save Activity Log File Dialog Box

Save SQL Script File Dialog Box

Start Selected Filters Dialog Box

SQL Server Auditing Dialog Box

SQL Trace Configuration Options Dialog Box

Pause Selected Filters Dialog Box

Stop Selected Filters Dialog Box

Trace Window

Wildcard Character, Percent Sign (%)


Microsoft SQL Trace Overview

SQL Trace is a graphical utility that database administrators and application developers can use to monitor and record database activity for Microsoft SQL Server 6.5. SQL Trace is installed with the server software and, optionally, with the 32-bit client software. You can start SQL Trace by choosing the SQL Trace icon from the SQL Server 6.5 program group, or by running SQLTRACE.EXE from the C:\MSSQL\BINN directory.

With SQL Trace you can display all server activity in real-time or create filters to monitor the activities of particular users, applications, or hosts. You can display statements or remote procedure calls sent to any SQL Server 6.5 server. You  can save server activity to disk in an ISQL/w-compatible script or as an activity log. System administrators can audit the use of SQL Server.

SQL Trace provides a graphical user interface for the extended stored procedure xp_sqltrace. For more information about xp_sqltrace, see SQL Server Books Online.

For more information about SQL Trace, choose one of the following:

 How To…

 About The…


Connect to or Disconnect From a Server

The MSSQLServer service must be running before you can connect to a server. To start the service, use SQL Service Manager. For more information, see SQL Server Books Online.

You must connect to a server before you can monitor it. Each time you start SQL Trace, you can specify a server to connect to. You can monitor only one server at a time.

To connect to a different server, you must disconnect from the current server before you connect to the new server.

To connect to a server

1.	From the File menu, choose Connect.

2.	Complete the Connect Server dialog box that appears.

To disconnect from a server

From the File menu, choose Disconnect.

To connect to a different server

1.	From the File menu, choose Disconnect.

2.	From the File menu, choose Connect.

3.	Complete the Connect Server dialog box that appears.


Connect Server Dialog Box

Use this dialog box to select a server to monitor. For more information, choose one of the following:

 Server

 Use Trusted Connection

 Use Standard Security

 Login Id

 Password

 Connect

 List Servers

See Also

Connect to or Disconnect From a Server

New Filter and Filter Properties Dialog Boxes


Server

Specifies the server to monitor. You can select a server from the list in the Server box; type a server name in the Server box; or click List Servers and select a server from the list that appears.


Use Trusted Connection

Specifies that the selected server be connected to using Windows NT security. The trusted connection will use the Windows NT integrated network security, eliminating the need to enter a username and a password.

If you are not using Windows NT integrated security features, you cannot apply this option.


Use Standard Security

Specifies that the selected server be connected to using standard SQL Server security. You must provide the login ID and password.

Select this option for connections over networks with protocols that do not use trusted connections.


Login Id

Specifies a valid SQL Server login ID. A valid login ID is a unique name that follows the SQL server rules for identifiers.


Password

Specifies the password for the login ID entered in the Login Id box.


Connect

Connects you to the server specified in the Server box.

When you choose Connect, the server name appears in the title bar and the Start Selected Filters dialog box appears.

When you choose Connect and no filters are defined for the server, a dialog box appears from which you can choose to create a filter.


List Servers

Opens the Select Server dialog box, which presents a list of available servers.

In the Select Server dialog box, double-click the server to monitor. Click Refresh to rebuild the list of servers.


Toggle Filter Status Display

When you connect to a server for which filters have been defined, filter information appears in the Filter Status pane that is displayed in the lower part of the SQL Trace window. You can display or hide filter information.

To display or hide the Filter Status pane

From the toolbar, click the [image] Toggle Filter Status Display button to display or hide the Filter Status pane. Or from the View menu, choose Filter Status to display the Filter Status pane, or clear Filter Status to hide the pane.


Start, Pause, or Stop a Filter

Filters are used to define how SQL Trace monitors server activity. The properties that were defined for a filter when it was created specify which activity to monitor, and how the resulting information will be displayed and/or saved. Each filter requires a connection to the SQL Server.

When you connect to a server for which filters have been defined, filter information appears in the Filter Status pane that is displayed in the lower part of the SQL Trace window.

To start a filter

From the toolbar or from the Filter Status pane, click the [image] green button to start the filter. Or from the File menu, choose Start Filters, select one or more filters from the Start Selected Filters dialog box, then click OK.

You can also start a filter when you connect to a server in SQL Trace. After the connection is made, the Start Selected Filters dialog box appears. In this dialog box, select a filter to run and click OK.

To pause a filter

From the toolbar or from the Filter Status pane, click the [image] blue button to pause the filter. Or from the File menu, choose Pause Filters, select one or more filters from the Pause Selected Filters dialog box, then click OK.

To stop a filter

From the toolbar or from the Filter Status pane, click the [image] red button to stop the filter. Or from the File menu, choose Stop Filters, select one or more filters from the Stop Selected Filters dialog box, then click OK.

See Also

Create a Filter

Filter Status Pane


Start Selected Filters Dialog Box

Use this box to specify a filter to run. A filter determines which activities SQL Trace will monitor and how the resulting information will be displayed and/or saved.

To run filters

1.	Select one or more filters from the list.

2.	Click OK.

See Also

Start, Pause, or Stop a Filter

Create a Filter


Pause Selected Filters Dialog Box

Use this box to specify a filter to pause. A filter determines which activities SQL Trace will monitor and how the resulting information will be displayed and/or saved.

To pause filters

1.	Select one or more filters from the list.

2.	Click OK.

See Also

Start, Pause, or Stop a Filter

Create a Filter


Stop Selected Filters Dialog Box

Use this box to specify a filter to stop. A filter determines which activity SQL Trace will monitor and how the resulting information will be displayed and/or saved.

To stop filters

1.	Select one or more filters from the list.

2.	Click OK.

See Also

Start, Pause, or Stop a Filter

Create a Filter


Launch an External Tool

Three external tools (ISQL/w, SQL Enterprise Manager, and SQL Performance Monitor) can be launched from within SQL Trace. Each tool is represented by one of three buttons on the toolbar:

 Launches ISQL/w. You can use ISQL/w to test a modification to an SQL statement.

 Launches SQL Enterprise Manager. On a server running Windows NT, you can use SQL Enterprise Manager to monitor current activity (for example, locks and blocking processes) on a server.

 Launches SQL Performance Monitor. On a server running Windows NT, you can use SQL Performance Monitor to obtain detailed information on CPU usage, disk activity, locks, and so on.

Note that these external tools are only available when their respective applications are available on your operating system.

To launch an external tool

From the toolbar, click the tool's button. Or, from the Tools menu, choose a tool to launch.


Choose Window Options

Three windowing options are available within SQL Trace from the toolbar:

 Click this button to tile windows within SQL Trace horizontally.

 Click this button to tile windows within SQL Trace vertically.

 Click this button to cascade windows within SQL Trace.

 Click this button to toggle Auto Scroll for windows within SQL Trace.

These and other windowing options are available from the Window menu:

Cascade

Tile

Arrange Icons

Auto-Tile

Auto-Scroll

Start Minimized


Filter Status Pane

Use this pane to stop or start filters, view filter activity, and open filters for editing.

Note that by clicking in the title bar of the Filter Status pane, you can move the pane within the trace window.

To start a filter

Click the [image] green button.

To pause a filter

Click the [image] blue button.

To stop a filter

Click the [image] red button.

To edit a filter

Double-click the filter name to edit and complete the Filter Properties dialog box.

To display or hide the Filter Status pane

Click the [image] Toggle Filter Status Display button from the toolbar, or from the View menu, choose Filter Status.

To move the Filter Status pane

Click the Filter Status pane title bar and while holding down on the right-mouse button, position the Filter Status pane.

See Also

Trace Window


Trace Window

Use this window to view information for an active filter. To display the trace window, in the New Filter or Filter Properties dialog box (displayed when creating or editing a filter), select the View On Screen option for the specified filter. The filter must be running for the window to appear.

You can minimize trace windows. Minimized windows become trace icons, which depict filter configuration and current trace status.

If the filter is only being monitored on-screen in a single window, then closing that window will stop the filter.

To close a trace window

Verify that the trace window to close is active by clicking on its title bar. From the File menu, choose Close. Or from the system menu, choose Close.

To close a trace icon

Click the trace icon, and from the shortcut menu that appears, choose Close.

See Also

Filter Status Pane


Editing a Trace Icon
Edit a Trace Icon

When you minimize a filter window, the window becomes an icon. The appearance of the icon is determined by the filter's properties. If the filter runs on a per-connection basis, the icon's appearance reflects the application name. Otherwise, the icon's appearance is based on the name you assign the filter.

You can modify the icon that is displayed for an application or filter name by editing the \MSSQL\BINN\SQLTRACE.MAP file. Each line in this file controls an icon.

For example, the ISQL/w and the SQL Enterprise Manager icon lines would look like:

Microsoft ISQL/w isqlw.exe

MS SQLEW sqlew.exe

Each line contains the application name or the filter name, the .EXE file that holds the icon definition, and, optionally, the position of an icon within an icon list. If an entry does not follow the .EXE file name, the default icon for that executable will appear. The default icon is the icon that appears first in the list of icons.

To see which icons are already defined, you must edit the SQLTRACE.MAP file. If an entry for your application or filter name does not appear in the SQLTRACE.MAP file, SQL Trace will use a default icon.


Create a Filter

A filter is used to define how SQL Trace monitors server activity. The properties that were defined for a filter when it was created or edited specify which activity to monitor, and how the resulting information will be displayed and/or saved.

To create a new filter

1.	From the toolbar, click the [image] New Filter button, or from the File menu choose New Filter.

The New Filter dialog box appears.

2.	Complete the displayed dialog box to define the filter properties.

3.	Click Add. The filter is added to the Filter Status pane and the filter is started. If the View on Screen option was selected in step 2, a trace window appears and displays information about the running filter.


Edit a Filter

A filter is used to define how SQL Trace monitors server activity. The properties that were defined when a filter was created or edited specify which activity to monitor, and how the resulting information will be displayed and/or saved.

To edit a filter

1.	Open the Filter Properties dialog box in one of the following ways:

From the toolbar, click the [image] Edit Filter Properties button.

From the File menu, choose Edit Filter Properties.

Click a trace icon and from the shortcut menu that appears, choose Filter Properties.

From the Filter Status pane, double-click the filter name or click the screen check box.

2.	To display a list of filters in the Filters Properties dialog box, expand the Filter Name boxand select the filter to edit.

3.	Edit the filter by changing the option settings.

4.	Click OK.


Delete a Filter

A filter must be inactive to be deleted. You can delete multiple filters through the Delete Selected Filters dialog box or individually through the Filter Properties dialog box.

To delete multiple filters

1.	Stop each filter you want to delete.

2.	Open the Delete Selected Filters dialog box in one of the following ways:

From the toolbar, click the [image] Delete Filters button.

From the File menu, choose Delete Filters.

3.	In the Delete Selected Filters dialog box, select each filter you want to delete.

4.	To delete the filter(s), click OK.

To delete a filter

1.	Stop each filter you want to delete.

2.	From the Filter Status pane, double-click the filter name to delete.

The Filter Properties dialog box appears.

3.	Expand the Filter Name box and select the filter to delete.

4.	Click the Delete button, then click Yes.

See Also

Start, Pause, or Stop a Filter


Delete Selected Filters Dialog Box

Specifies a filter(s) to delete. A filter must be inactive before you can delete it.

See Also

Delete a Filter

Start, Pause, or Stop a Filter


New Filter / Filter Properties Dialog Box
New Filter and Filter Properties Dialog Boxes

Use these dialog boxes to create and modify filters. A filter is used to define how SQL Trace monitors server activity. The properties that were defined when a filter was created or edited specify which activity to monitor, and how the resulting information will be displayed and/or saved.

For more information, choose one of the following:

 Filter Name

 Login Name

 Application

 Host Name

 Add/OK

 Delete

Capture Options tab

 View on Screen

 Per Connection

 Save to Script File

 Include Performance Information

 Save as Log File

Events tab

 Connections

 SQL Statements

 SQL Statements Filter

 RPC

 RPC Filter

 Attentions

 Disconnections

See Also

Build Filter On Applications Dialog Box

Build Filter On Host Names Dialog Box

Build Filter On User Logins Dialog Box

Save Activity Log File Dialog Box

Save SQL Script File Dialog Box

Wildcard Character, Percent Sign (%)


Filter Name

Specifies a filter name. To create a filter, type a name in this box. To edit a filter, click the arrow beside this box and select a name from the list that appears.


Login Name

Specifies the login name(s) of users to monitor events for. Separate multiple login IDs with semicolons (;). To select from a list of known login IDs, click the [image] Locate button and complete the Build Filter On User Logins dialog box.

You can use the wildcard character, percent sign (%), to indicate any sequence of characters. For example, specifiying Chan% as the login name would find long names Chan and Channing.

The criteria for Login Name, Application, and Host Name are cumulative. For example, if caseyv; delaneyr; royces are entered as login names, ISQL/w is entered as an application, and airedale is the host name, only events generated by caseyv, delaneyr, or royces that are using ISQL/w on the host airedale will be monitored.


Full File Path

Specifies the full file path name.


Application

Specifies the application(s) to monitor events for. Separate multiple applications with semicolons (;). To select from a list of known applications, click the [image] Locate button and complete the Build Filter On Applications dialog box.

Only applications that are currently connected or that have connected in the past to SQL Server will appear in the list.

You can use the wildcard character, percent sign (%), to indicate any sequence of characters. For example, specifying Acc% as the application name would find the application Access.

The criteria for Login Name, Application, and Host Name are cumulative. For example, if caseyv; delaneyr; royces are entered as login names, ISQL/w is entered as an application, and airedale is the host name, only events generated by caseyv, delaneyr, or royces that are using ISQL/w on the host airedale will be monitored.


Host Name

Specifies the host name(s) to monitor events for. Separate multiple host names with semicolons (;). To select from a list of known host names, click the [image] Locate button and complete the Build Filter On Host Names dialog box.

You can use the wildcard character, percent sign (%), to indicate any sequence of characters. For example, specifying air% as the host name would find the host airedale.

The criteria for Login Name, Application, and Host Name are cumulative. For example, if caseyv; delaneyr; royces are entered as login names, ISQL/w is entered as an application, and airedale is the host name, only events generated by caseyv, delaneyr, or royces that are using ISQL/w on the host airedale will be captured.


Connections

Specifies that connection events will be monitored by the filter.


SQL Statements

Specifies that SQL language batches will be monitored by the filter.


Wildcard Character, Percent Sign (%)

To search for all SQL statement batches containing a string, you must put the wildcard character on either side of the string. For example, to find all batches that contain table, type:

%table%

This example will monitor all batches that contain table, including the following:

drop table authors

Create table temp (job_id smallint, job_name varchar (50))


SQL Statements Filter

Specifies the SQL statement string to monitor. You can use an SQL statements filter, for example, to capture SQL statement batches that explicitly reference a specific table or stored procedure. Separate multiple statements with semicolons (;), or you can use the wildcard character, percent sign (%), to generalize your filter criteria.

Note that all SQL statements containing the string entered in the Filter box will be captured. For example, the filter will not distinguish between a column-named customer and a table-named customer.

Also note that you cannot use percent signs (%) or semicolons (;) within SQL statements, because these symbols will be confused with statement delimiters (;) and wildcard characters (%).


RPC

Specifies that remote procedure calls will be monitored by the filter.


RPC Filter

Specifies that only remote procedure calls that contain the character string entered in the RPC Filter box will be monitored. Separate multiple filter strings with semicolons (;), or you can use the wildcard character, percent sign (%), to generalize your filter criteria.


Attentions

Specifies that attention events will be monitored by the filter.

An attention event is when SQL Server is interrupted by the client and stops executing a command batch.


Disconnections

Specifies that disconnection events will be monitored by the filter.


View on Screen

Specifies that information captured by a running filter will be written to a trace window.


Save as Log File

Specifies that information produced by the filter will be written to the specified log file. If you select this option, you must provide a fully qualified file name for the log file. Type a drive, a path, and a file name in the box, or click the [image] Locate button and complete the Save Activity Log File dialog box.


Save to Script File

Specifies that information produced by the filter will be saved as a SQL script file. If you select this option, you must provide a fully qualified filename for the script file. Type a drive, a path, and a filename in the box, or click the [image] Locate button and complete the Save SQL Script File dialog box.

Note that you should inspect the contents of script files before you run them. Some statements are context-sensitive and can produce unpredictable results if run in the wrong context (for example, current db, user).


Include Performance Information

Specifies that statistics will be captured, including:

The duration of an event

CPU usage for an event

Disk reads for the event

Synchronous writes for the event


Per Connection

Specifies that filter output will be sent to a separate trace window that is established for each client connection.


Add / OK

Accepts a new or modified filter. After you have defined a new filter, click the Add button to create the filter. After you have edited an existing filter, click the OK button to modify the filter.


Delete

Removes the selected filter. Displays a message that asks you to confirm this selection.


Build Filter On Applications Dialog Box

Use this dialog box to select the application(s) to monitor.  Select one or more applications, then click OK.

Only applications that are currently connected or that have connected in the past to SQL Server will appear in this list.

If an application's name is not included in its connection request, then that application's activity can only be captured by using an All filter. In this instance, a filter that is based on the application name will not find the application's activity.


Build Filter On Host Names Dialog Box

Use this dialog box to select the SQL Server host name(s) to monitor. Select one or more host names, then click OK.


Build Filter On User Logins Dialog Box

Use this dialog box to select the SQL Server user login name(s) to monitor. Select one or more login names, then click OK.


Save Activity Log File Dialog Box

Use this dialog box to specify the path and the filename of a log file. The filter will save activity information in this log file. For more information, choose one of the following:

 File Name

 Save File as Type

 Directories

 Drives

 Network


Save SQL Script File Dialog Box

Use this dialog box to specify the path and the filename of a SQL script file. The filter will save SQL statements in this script file. For more information, choose one of the following:

 File Name

 Save File as Type

 Directories

 Drives

 Network


File Name

Specifies a filename in the box (for example, FILTER01). Entering a filename extension (such as .LOG for log files or .SQL for script files) is not necessary. An extension will be added based on the selection in the Save File as Type box.


Save File as Type

Specifies a filename type for the filename to be displayed in the File Name box. Log files use the .LOG filename extension. Script files use the .SQL filename extension. To change the type of file displayed, select a file type from the list.


Directories

Displays the selected directory. To change the selection, double-click in the folder tree.


Drives

Displays the selected drive. To change the drive, select a drive from the list.


Network

Opens the Connect Network Drive dialog box. Use the dialog box to connect to a shared network directory.


Configuration Options Dialog Box
SQL Trace Configuration Options Dialog Box

Specifies the configuration options for SQL Trace. To open the dialog box, from the Tools menu select Configure SQL Trace. To change the characteristics, modify the information displayed in this dialog box.

For more information, choose one of the following:

 Number of Rows to Buffer on Server

 Server Timeout (seconds)

 TDS Packet Size (Kbytes)

 Screen Buffer Size on Client (Kbytes)


Screen Buffer Size on Client

Specifies the amount of memory on the client machine that will be used to store the screen data returned from SQL Server per trace window. The amount of memory controls the size of the scrollable region in the trace window. This value can be set for each filter.


Number of Rows to Buffer on Server

Specifies the number of rows of data the server will buffer when the client is busy. A buffer is an intermediate holder of data. Typically, when a client is busy, the server sends rows immediately back to the client. If the client continues to be busy, the server will buffer a set number of rows until the client can receive them.

When the row buffer reaches its maximum capacity and the client is not ready to receive rows, the server will stop the filter and discard the buffered rows.

The default value is typically adequate. If you increase this value, you increase the chance of the server memory being consumed under adverse conditions. If you decrease this value, you increase the chance that the filter will be terminated by the server.


Server Timeout (seconds)

Specifies the amount of time in seconds that the server will wait, when the row buffer is full, for the client to read back at least one item of data.

To protect the clients that are being traced, the server will stop any filter that has had a full server buffer for longer than the Server Timeout period. This allows the clients being traced to continue their execution without interruption.

Unless you have encountered timeout problems with running a filter and interrupting a client, you should not change the entry specified in the Server Timeout option.


TDS Packet Size (Kbytes)TD
TDS Packet Size (Kbytes)

Specifies the packet size of the tabular data stream (TDS) from the server to SQL Trace. Generally a larger packet size has less overhead, so it provides more data once the packet has been received.  A smaller packet provides quicker response time for any one packet, but less information is received in any set amount of time.


Audit a Server

Server auditing provides a filter that is resident on the server. Once set up, this filter will continue to run even if SQL Trace is not running.

To audit a server

1.	From the Tools menu, choose SQL Server Auditing.

2.	Complete the SQL Server Auditing dialog box.


SQL Server Auditing Dialog Box

You can use this dialog box to set up a filter that resides on the SQL Server and that continues to run whenever SQL Trace is stopped.

For more information, choose one of the following:

 Full File Path

 Login Name

 Application

 Host Name

 Events


Events

Specifies which events will be monitored, including:

 Connections

 SQL Statements

 SQL Statements Filter

 RPC

 RPC Filter

 Attentions

 Disconnections