xp_trace_addnewqueue (T-SQL)

Adds a new trace queue and sets trace queue configuration values, including the size of the trace queue (the number of items to be held in the trace queue at one time).

Important When using the shared memory network library, Microsoft® SQL Server™ does not support impersonation unless connected by using Windows NT Authentication. To execute a SQL Server Profiler Extended Procedure against a local server through SQL Server Query Analyzer, use Windows NT Authentication.

Syntax

xp_trace_addnewqueue {max_items, timeout, thread_boost, thread_reduce,
    required_columns, queue_handle OUTPUT}

Arguments

max_items

Is an integer representing the maximum number of items buffered or stored in the trace queue. The recommended value is 1000, the minimum is 1, and the maximum is 20,000.

timeout

Is an integer representing the time-out value in seconds for the trace queue. The default value is 5, the minimum is 1, and the maximum is 10. At time-out, the trace queue, for a brief period of time, stops accepting new events.

thread_boost

Is an integer representing the percentage of items in the trace queue causing the trace queue consumer to receive a boost in priority and increases the priority setting for the specified application thread. The default value is 95%, the minimum is 1%, and the maximum is 100%. thread_reduce cannot be greater than thread_boost.

thread_reduce

Is an integer representing the percentage of items in the trace queue causing the trace queue consumer to receive a reduction in priority and decreases the priority setting for the specified application thread. The default value is 90%, the minimum is 0%, and the maximum is 99%. thread_reduce cannot be greater than thread_boost.

required_columns

Is an integer mask representing the required columns to be shown in the trace output. The default is 0, which means all columns listed below are provided in the trace output. Choose from these values and use the bitwise OR (|) operator to combine them.

 

Value	Description
1	Text data
2	Binary data
4	Database ID
8	Transaction ID
16	Connection ID
32	Microsoft Windows NT® username
64	Windows NT domain name
128	Host name
256	Host process ID
512	Application name
1024	SQL username
2048	SQL process ID (SPID)
4096	Duration
8192	Start time
16384	End time
32768	Reads
65536	Writes
131072	CPU usage
524288	Severity
1048576	Event Subclass
2097152	Object ID
8388608	Index ID
16777216	Integer data
33554432	Server name
67108864	Event Class

queue_handle

Is an integer representing the handle for the trace queue returned from this procedure. queue_handle must be declared before running the procedure.

OUTPUT

Specifies that the value returned is placed in the queue_handle parameter.

Return Code Values

0 (success) or >1 (failure)

Result Sets

xp_trace_addnewqueue returns this message:

The command(s) completed successfully.

Remarks

After a trace queue is created, there may come a time when the trace queue must be either stopped, or destroyed. For more information about stopping or destroying a trace queue, see Deleting Traces.

There are some special considerations when using the Replay feature of SQL Server Profiler which pertain to passwords, permissions, and authentication. For more information, see Replaying Traces.

Permissions

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

Examples

This example creates a trace queue that writes events to a file and autostarts whenever the server is restarted.

--Declare the variables.

DECLARE @queue_handle int  --queue handle to refer

                           --to this trace by

DECLARE @column_value int  --data column bitmask

--Set the column mask for the data columns to capture

SET @column_value = 1|16|3|8192|128|512

--Create a queue.

EXEC xp_trace_addnewqueue 1000,

    5,

    95,

    90,

    @column_value,

    @queue_handle OUTPUT

--Specify the event classes to trace.

--To look up the names, execute xp_trace_geteventnames.

EXEC xp_trace_seteventclassrequired @queue_handle,
    10, 1 --RPC:Completed

EXEC xp_trace_seteventclassrequired @queue_handle,
    12, 1 --SQL:BatchCompleted

EXEC xp_trace_seteventclassrequired @queue_handle,
    14, 1 --Connect

EXEC xp_trace_seteventclassrequired @queue_handle,
    16, 1 --Disconnect

EXEC xp_trace_seteventclassrequired @queue_handle,
    17, 1 --ExistingConnection

--Set any filters. (Don't trace the Profiler).

EXEC xp_trace_setappfilter @queue_handle,
    NULL,
    'SQL Server Profiler%'

--Configure the queue to write to a file.

EXEC xp_trace_setqueuedestination @queue_handle,

    2,

    1,

    NULL,

    'c:\temp\demo.trc'

--Start the consumer that actually writes to a file.

EXEC xp_trace_startconsumer @queue_handle

--Display the queue handle. It will be needed

--later to stop the queue.

SELECT @queue_handle

--Save the queue definition as DemoQueue.

EXEC xp_trace_savequeuedefinition @queue_handle,

    'DemoQueue',

    1

--Mark it for autostart

EXEC xp_trace_setqueueautostart 'DemoQueue', 1

See Also