xp_sqltrace (version 6.5)

Allows database administrators and application developers to monitor and record database activity. Multiple instances of xp_sqltrace can be run simultaneously.

Syntax

xp_sqltrace [[@Function = ] function] [, [ @EventFilter = ] eventfilter]
[, [ @LangFilter = ] 'langfilter'] [, [ @RPCFilter = ] 'rpcfilter'] [, [ @UserFilter = ] userfilter] [, [ @AppFilter = ] appfilter] [, [ @HostFilter = ] hostfilter] [, [@BufSize = ] bufsize] [, [ @TimeOut = ] timeout] [, [ @TraceId = ] traceid] [, [@FullText = ] fulltext] [, [ @FullFilePath = ] 'outputfilename'] [, [ @IntegerEvents = ] integerevents]

where

@Function

Specifies a string that describes what action xp_sqltrace performs. These are the possible values for @Function.

@Function value	Description
audit	Logs all database activity requested by the specified filters to a file specified by @FullFilePath. Returns program control immediately. If @TraceId, @BufSize, or @TimeOut parameters are specified, they are ignored. If audit is used without any parameters, generates a results set that displays the current audit settings for xp_sqltrace. Only one instance of audit can be executed at any time. A permanent connection is not necessary to execute an audit. For example, the system administrator can be connected, start the audit, and then disconnect. You can use audit in automatically started procedures.
control	Changes the settings for a running instance from a separate connection. You must run xp_sqltrace without parameters to obtain an @TraceId before starting a trace session. The @TraceId parameter is required.
remove	Unloads all event handlers. Stops all data from being sent t o trace sessions until trace handlers are loaded again. Using remove does not stop currently executing trace sessions. Starting or stopping a trace session also loads or unloads relevant handlers. You can use remove to temporarily pause all trace sessions. No other parameters are required.
stop	Stops either a single trace session or all trace sessions for the current server. It is recommended that you execute xp_sqltrace stop, @traceid = <traceid> to stop a specific trace session. Using xp_sqltrace stop is useful if the server is under heavy load and the system administrator must stop all trace activities.
trace	Captures all database activity requested by the specified filters and generates a log file or a results set. The @TraceId parameter has no effect.

@EventFilter

Specifies an integer (int datatype) that describes what server activity to monitor. To specify more than one @EventFilter value, add together the @EventFilter values. For example, @EventFilter = 31 means that xp_sqltrace should monitor CONNECT, DISCONNECT, POST_LANGUAGE, POST_RPC, and ATTENTION events. These are the possible values for @EventFilter.

@EventFilter value	Description
0	PAUSE. Temporarily pauses the trace session. Must be the only @EventFilter specified.
1	CONNECT. Trace CONNECT events to SQL Server.
2	DISCONNECT. Trace DISCONNECT events from SQL Server.
4	POST_LANGUAGE. Trace completed language events on SQL Server (for example, INSERT, SELECT).
8	POST_RPC. Trace completed RPC events on SQL Server.
16	ATTENTION. Trace ATTENTION events on SQL Server.
32	PRE_LANGUAGE. Trace language batches immediately prior to execution. Not currently used by SQLTRACE.EXE.
64	PRE_RPC. Trace RPC events immediately before execution. Not currently used by SQLTRACE.EXE.

@LangFilter

Specifies a string that specifies a batch pattern of language statements to be monitored. Use the wildcard character (%) to indicate any sequence of characters. Delimit multiple filter strings with a semi-colon (;) for the OR condition (%INSERT%;%SELECT%).

@RPCFilter

Specifies a string pattern of remote stored procedure calls for which to monitor events. Delimit multiple filter strings with a semi-colon (;) for the OR condition. Use the wildcard character (%) to indicate any sequence of characters. Delimit multiple filter strings with a semi-colon (;) for the OR condition (%sp_cursors%;sp_run%).

@UserFilter

Specifies the login name(s) of users for whom to monitor events as a string. Delimit multiple filter strings with a semi-colon (;) for the OR condition. Use the wildcard character (%) to indicate any sequence of characters. For example, specify Chan% as the login name to find login names Chan and Channing.

@AppFilter

Specifies the application name(s) for which to monitor events as a string. Delimit multiple filter strings with a semi-colon (;) for the OR condition. Use the wildcard character (%) to indicate any sequence of characters. For example, specify the application name Acc% to find the Access application.

@HostFilter

Specifies the host(s) for which to monitor events as a string. Delimit multiple filter strings with a semi-colon (;) for the OR condition. Use the wildcard character (%) to indicate any sequence of characters. For example, specify the host name air% to find the airedale host.

@BufSize

Specifies the number of rows of data the server will buffer when the client is busy. The @BufSize parameter is of int datatype and can be a value 1 to 20,000. The default is 1,000.

@TimeOut

Specifies the amount of time in seconds that the server will wait when the row buffer specified in @BufSize is full before terminating the trace session. The @TimeOut parameter is of int datatype and can be 1 to 10 seconds (the default is five seconds).

@TraceId

Specifies the trace identification number used for the trace session. Each @TraceId value is static for a connection as long as you retain your current connection. The @TraceId parameter is of int datatype.

@FullText

Specifies whether the language or RPC strings should be returned as varchar(255) (The default is 0) or as text (1). The @FullText parameter is of int datatype.

@FullFilePath

Specifies the file to send the output to as a string (for example, 'C:\selects.txt'). This parameter uses the standard Microsoft SQL Trace log format for output.

For more information about the SQL Trace log format, see the online Help for SQL Trace.

@IntegerEvents

Specifies whether the event column should be text (The default is 0) or integer (1). The @IntegerEvents parameter is of int datatype.

Remarks

When using xp_sqltrace, an @Function = trace does not return program control until explicitly stopped. No other commands should be issued before the call to xp_sqltrace in an isql or ISQL/w batch. After you choose the stop button in ISQL/w, another output row must be received for the trace to stop. This is a limitation of the ISQL/w interface.

Note The recommended way to stop a trace session is to specify the trace identification number. For example:

xp_sqltrace stop, @traceid = <traceid>
  

Data can be captured to a table by using the INSERT statement's EXECute clause. Using the INSERT statement this way places xp_sqltrace within a transaction. If xp_sqltrace is used in a transaction, the transaction will not complete until the application stops xp_sqltrace. If the trace is not terminated before the server is stopped or if the database fills completely during a transaction, all changes are rolled back.

These are the events that are specific to trace session output.

For more information about monitoring database activity, see the online Help for SQL Trace.

Examples

A.    Trace SELECT Statements for sa and probe

This example begins a trace session that displays the event, the username, the application, the data or language statement, and other columns. All SELECT statements executed by either sa or probe users are included in the trace. Events included in the trace are attention, connect, disconnect, post-language, and post-rpc events.

xp_sqltrace trace, @eventfilter = 31, @userfilter = 'sa;probe', 
@langfilter = '%select%'

These are the columns generated in the results set.

B.    Audit SELECT Statements to a Text File

This example audits batches that contain SELECT statements executed by sa or probe users and places the output in a text file called C:\SELECTS.TXT.

xp_sqltrace audit, @eventfilter = 31, @userfilter = 'sa;probe', 
@langfilter = '%select%', @FullFilePath = 'C:\SELECTS.TXT'

C.    Obtain and Use a Specific @TraceId

This example starts a trace session for the first connection, and it allows the second connection to control the same trace session by using the trace identification number obtained and passed by the first connection.

--pause the trace session 
xp_sqltrace control, 
@TraceId = <TraceId>

--begin trace session
xp_sqltrace control 
@TraceId = <TraceId>, 
@EventFilter = 4, 
@LangFilter = '%select%'

--stop the trace session 
xp_sqltrace stop, 
@traceid = <traceid>

D.    Stop All Current Trace Sessions

The following two xp_sqltrace command examples stop all trace sessions executing on the current server while the third example stops only the specified trace session.

-- stops all trace sessions
xp_sqltrace stop, @traceid = 0
-- OR
xp_sqltrace stop
  
--recommended way to stop a single trace session
xp_sqltrace stop @TraceId = <TraceId>
  

E.    Configure SQL Server to Automatically Audit All Sessions at Disconnect

This example creates a stored procedure to automatically start an audit session that audits disconnections.

print 'Creating auditing stored procedure'
-- create stored procedure to enable xp_sqltrace run at startup
go
USE master
go
IF exists(SELECT * FROM sysobjects WHERE type = 'P' AND name = 'sp_sqlaudit')
    DROP PROCEDURE sp_sqlaudit
go
CREATE PROCEDURE sp_sqlaudit AS
BEGIN
-- @FullFilePath can be any file that SQL Server has write
-- permissions
EXECUTE master.dbo.xp_sqltrace audit, 2, @FullFilePath = 'C:\MSSQL\LOG\AUDIT.LOG'
END
go
sp_makestartup 'sp_sqlaudit'
go
  

F.    Capture Server Activity to Table

This example uses the EXECUTE statement to capture all of the server activity to a table named sqltrace in the pubs database. Note that the transaction is not committed until you execute "xp_sqltrace stop" from another connection.

USE pubs
GO
IF EXISTS(SELECT * FROM sysobjects WHERE name = 'SQLTrace' AND type = 'U')
BEGIN
    DROP TABLE SQLTrace
END
GO
CREATE TABLE SQLTrace
(
    Event         CHAR(12) NOT NULL,
    UserName     CHAR(30) NOT NULL,
    ID int         NOT NULL,
    SPID int     NOT NULL,
    StartTime     DATETIME NULL,
    EndTime         DATETIME NULL,
    Application     CHAR(30) NOT NULL,
    Data         VARCHAR(255) NULL,
    Duration     INT NULL,
    CPU             INT NULL,
    Reads         INT NULL,
    Writes         INT NULL,
    NT_Domain     VARCHAR(30) NULL,
    NT_User       VARCHAR(30) NULL,
    HostName      VARCHAR(30) NULL,
    HostProcess     INT NULL 
)
GO
  
  
EXEC master..xp_sqltrace
GO
  
INSERT INTO SQLTrace EXECUTE master..xp_sqltrace TRACE, @eventfilter = 31
  

From another connection, execute the following statement.

EXEC master..xp_sqltrace STOP, 1358560

/* where 1358560 is the trace session returned from xp_sqltrace */

G.    BCP SQL Trace Activity Log

You can easily load an activity log into SQL Server using BCP. First create a log and capture the server activity to an activity log file. Next, create a table with the following structure:

IF EXISTS(SELECT * FROM sysobjects WHERE name = 'SQLTrace' AND type = 'U')
BEGIN
    DROP TABLE SQLTrace
END
GO
CREATE TABLE SQLTrace
(
    Event         CHAR(12) NOT NULL,
    UserName     CHAR(30) NOT NULL,
    ID             INT NOT NULL,
    SPID         INT NOT NULL,
    StartTime     datetime NULL,
    EndTime         datetime NULL,
    Application     CHAR(30) NOT NULL,
    Data         VARCHAR(255) NULL,
    Duration     INT NULL,
    CPU             INT NULL,
    Reads         INT NULL,
    Writes         INT NULL,
    NT_Domain     VARCHAR(30) NULL,
    NT_User       VARCHAR(30) NULL,
    HostName      VARCHAR(30) NULL,
    HostProcess     INT NULL 
)
GO
  

Finally, load the data using BCP.

bcp pubs..SQLTrace in c:\activity.log -Usa -Psecret -S -c
  

Permission

Execute permission defaults to the system administrator. It is recommended that the system administrator be the only login permitted to execute this procedure.