xp_sqltrace (T-SQL)

Allows database administrators and application developers to monitor and record database activity.

Note xp_sqltrace has been replaced by xp_trace_addnewqueue as well as several other supporting extended procedures in the current version of Microsoft® SQL Server™. xp_sqltrace is documented here for backward compatibility only and may not be supported in future versions.

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]

Arguments

[@Function =] function

Is a string that describes what action xp_sqltrace performs. These are the possible values.

 

Value	Description
audit	Logs all database activity requested by the specified filters to a file specified by outputfilename. Returns program control immediately. If traceid, bufsize, or timeout are specified, they are ignored. If audit is used without any parameters, a result set is generated displaying 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 a trace ID before starting a trace session. traceid is required.
remove	Unloads all event handlers. Stops all data from being sent to 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 result set. traceid has no effect.

[@EventFilter =] eventfilter

Is the server activity to monitor. eventfilter is int, and can be any of these values.

 

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 remote procedure call (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.

To specify more than one event, add the eventfilter values. For example, @EventFilter = 31 means that xp_sqltrace should monitor CONNECT, DISCONNECT, POST_LANGUAGE, POST_RPC, and ATTENTION events.

[@LangFilter =] 'langfilter'

Is a string specifying 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 semicolon (;) for the OR condition (%INSERT%;%SELECT%).

[@RPCFilter =] 'rpcfilter'

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

[@UserFilter =] 'userfilter'

Is the login name(s) of users for whom to monitor events as a string. Delimit multiple filter strings with a semicolon (;) 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 =] appfilter

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

[@HostFilter =] hostfilter

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

[@BufSize =] bufsize

Is the number of rows of data the server buffers when the client is busy. bufsize is int, with a default of 1,000. bufsize can be a value from 1 to 20000.

[@TimeOut =] timeout

Is the amount of time in seconds the server waits, when bufsize is full, before terminating the trace session. timeout is int, with a default of 5 seconds. timeout can be 1 to 10 seconds.

[@TraceId =] traceid

Each traceid value is static for a connection as long as you retain your current connection. traceid is int.

[@FullText =] fulltext

Is whether the language or RPC strings should be returned as varchar(255) (0) or as text (1). fulltext is int, with a default of 0.

[@FullFilePath =] 'outputfilename'

Is the file to send the output to as a string (for example, C:\Selects.log). This parameter uses the standard SQL Server Profiler log format for output. For more information about the SQL Server Profiler log format, see SQL Server Profiler Help.

[@IntegerEvents =] integerevents

Is whether the event column should be text (0) or integer (1). integerevents is int, with a default of 0.

Return Code Values

0 (success) or > 1 (failure)

Result Sets

xp_sqltrace returns this result set (values may vary).

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 utility or SQL Server Query Analyzer batch. After you choose the stop button in SQL Server Query Analyzer, another output row must be received for the trace to stop. This is a limitation of the SQL Server Query Analyzer interface.

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 specific to trace session output.

Permissions

Execute permissions for xp_sqltrace default to the members of the sysadmin fixed server role but can be granted to other users.

Examples

A. Trace SELECT statements for user

This example begins a trace session displaying the event, the username, the application, the data or language statement, and other columns. All SELECT statements executed by the user sa are included in the trace. Events included in the trace are ATTENTION, CONNECT, DISCONNECT, POST-LANGUAGE, and POST-RPC events.

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

These are the columns generated in the result set.

B. Audit SELECT statements to a text file

This example audits batches that contain SELECT statements executed by the user sa and places the output in a file called C:\SELECTS.LOG.

EXEC xp_sqltrace audit, @eventfilter = 31, @userfilter = 'sa',
    @langfilter = '%select%', @FullFilePath = 'C:\SELECTS.LOG'

C. Obtain and use a specific trace identification number

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.

D. Stop all current trace sessions

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

-- Stops all trace sessions.

EXEC xp_sqltrace stop, @traceid = 0

-- OR

EXEC xp_sqltrace stop

-- Recommended way to stop a single trace session.

EXEC 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.

EXEC master.dbo.xp_sqltrace audit, 2, @FullFilePath =

    'C:\MSSQL7\LOG\AUDIT.LOG'

END

GO

EXEC sp_procoption 'sp_sqlaudit', 'startup', true

GO

F. Capture server activity to table

This example uses EXECUTE (or EXEC) to capture all of the server activity to a table named sqltrace in the pubs database. Note the transaction is not committed until xp_sqltrace STOP is executed 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 EXEC master..xp_sqltrace TRACE, @eventfilter = 31

From another connection, execute:

EXEC master..xp_sqltrace STOP, 1358560

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

G. Load a trace activity log with the bcp utility

This example loads an activity log into SQL Server using the bcp utility. First, create a log and capture the server activity to an activity log file. Next, create a table with this structure:

IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='SQLTrace')

    DROP TABLE SQLTrace

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

See Also