xp_trace_eventclassrequired (T-SQL)

Returns the class name for the event classes being captured for the given queue handle when event class information is provided.

Syntax

xp_trace_eventclassrequired {queue_handle} [, event_class]

Arguments

queue_handle

Is an integer that represents the handle for the trace queue. queue_handle is used to reference the trace queue and is placed in an output parameter by xp_trace_addnewqueue.

event_class

Is an integer that represents the class of events to trace. If event_class is not specified, all event classes are returned, indicating whether they are being traced for queue_handle. Execute xp_trace_geteventnames to see this list. event_class can be one of these values.

 

Value	Event class	Description
0	TraceStart	Given trace has been put into started state.
1	TracePause	Given trace has been put into a paused state.
2	TraceRestart	Given trace has been put into a restarted state.
3	TraceAutoPause	Given trace has been put into an automatic pause state.
4	TraceAutoRestart	Given trace has been put into an automatic restart state.
5	TraceStop	Given trace has been put into a stopped state.
6	EventRequired	Given trace has been put into an events to trace changed state.
7	FilterChanged	Given trace has been put into a filter changed state.
8	8	Reserved.
9	9	Reserved.
10	RPC:Completed	Occurs when a remote procedure call (RPC) has been completed.
11	RPC:Starting	Occurs when an RPC has been started.
12	SQL:BatchCompleted	Occurs when a Transact-SQL batch has been completed.
13	SQL:BatchStarting	Occurs when a Transact-SQL batch is starting.
14	Connect	Collects all connection events, such as when a client requests a connection to a server running Microsoft® SQL Server™.
15	Disconnect	Collects all disconnect events, such as when a client issues a disconnect command.
16	Attention	Collects all attention events, such as client-interrupt requests or broken client connections.
17	ExistingConnection	Detects all users that were logged on before the trace started.
18	ServiceControl	Tracks all server control events, such as server paused or restart events.
19	DTCTransaction	Tracks MS DTC coordinated transactions between two or more databases. For more information, see How to use Microsoft Distributed Transaction Coordinator (ODBC).
20	LoginFailed	Indicates that a login attempt to SQL Server from a client failed.
21	EventLog	Indicates events logged in the  Microsoft Windows NT® application log.
22	ErrorLog	Indicates error events logged in the SQL Server error log.
23	Lock:Released	Indicates that a lock on a resource, such as a page, has been released.
24	Lock:Acquired	Indicates acquisition of a lock on a resource, such as a data page.
25	Lock:Deadlock	Indicates that two concurrent transactions have deadlocked each other by trying to obtain incompatible locks on resources the other transaction owns.
26	Lock:Cancel	Indicates that the acquisition of a lock on a resource has been canceled (for example, due to a deadlock).
27	Lock:Timeout	Indicates that a request for a lock on a resource, such as a page, has timed out due to another transaction holding a blocking lock on the required resource. Time-out is determined by the @@LOCK_TIMEOUT function, and can be set with the SET LOCK_TIMEOUT statement.
28	Insert	Occurs before an INSERT statement is executed.
29	Update	Occurs before an UPDATE statement is executed.
30	Delete	Occurs before DELETE statement is executed.
31	Select	Occurs before SELECT statement is executed.
32	ConnectionBeingKilled	Indicates that the connection is being killed.
33	Exception	Indicates that an exception has occurred in SQL Server.
34	SP:CacheMiss	Indicates when a stored procedure is not found in the procedure cache.
35	SP:CacheInsert	Indicates when an item is inserted into the procedure cache.
36	SP:CacheRemove	Indicates when an item is removed from the procedure cache.
37	SP:Recompile	Indicates that a stored procedure is recompiling.
38	SP:CacheHit	Indicates when a stored procedure is found in the procedure cache.
39	SP:ExecContextHit	Indicates when the execution version of a stored procedure has been found in the procedure cache.
40	SQL:StmtStarting	Occurs when the Transact-SQL statement is starting.
41	SQL:StmtCompleted	Occurs when the Transact-SQL statement has completed.
42	SP:Starting	Indicates when the stored procedure has started.
43	SP:Completed	Indicates when the stored procedure has completed.
44	SP:StmtStarting	Indicates when a statement within a stored procedure is starting.
45	SP:StmtCompleted	Indicates when a statement within a stored procedure has completed.
46	Object:Created	Indicates when an object has been created, such as for CREATE INDEX, CREATE TABLE, and CREATE DATABASE statements.
47	Object:Deleted	Indicates when an object has been deleted, such as in DROP INDEX and DROP TABLE statements.
48	Object:Opened	Indicates when an object has been accessed, such as for SELECT, INSERT, or DELETE statements.
49	Object:Closed	Indicates when an object has been closed, such as the end of a SELECT, INSERT, or DELETE statement.
50	SQLTransaction	Tracks Transact-SQL BEGIN, COMMIT, SAVE, and ROLLBACK TRANSACTION statements.
51	Scan:Started	Indicates when a table or index scan has started.
52	Scan:Stopped	Indicates when a  table or index scan has stopped.
53	CursorOpen	Indicates when a cursor has been opened on a Microsoft SQL Server table by ODBC or DB-Library.
54	TransactionLog	Tracks when transactions are written to the transaction log.
55	HashWarning	Indicates that a hashing operation (for example, hash join, hash aggregate, hash union, and hash distinct) that is not processing on a buffer partition has reverted to an alternate plan. This can occur because of recursion depth, data skew, trace flags, or bit counting.
56	56	Reserved.
57	57	Reserved.
58	Auto-UpdateStats	Collects the events associated with the automatic updating of index statistics.
59	Lock:Deadlock Chain	Is produced for each of the events leading up to the deadlock.
60	Lock:Escalation	Indicates that a finer-grained lock has been converted to a coarser-grained lock (for example, a row lock that is escalated or converted to a page lock).
61	OLEDB Errors	Indicates that an OLE DB error has occurred.
62	Replay Error	Error returned by ODBC.
63	Replay Internal Error	Internal replay error.
64	Replay Result Set	Indicates the beginning of a result set returned by a query.
65	Replay Result Row	Produced for each row returned from a replay  result set.
66	66	Reserved.
67	Execution Warnings	Indicates any warnings that occurred during the execution of a SQL Server statement or stored procedure.
68	Execution Plan	Displays the plan tree of the Transact-SQL statement being executed.
69	Sort Warnings	Indicates sort operations that do not fit into memory. Does not include sort operations involving the creating of indexes; only sort operations within a query (such as an ORDER BY clause used in a SELECT statement).
70	CursorPrepare	Indicates when a cursor has been prepared on a Transact-SQL statement by ODBC or DB-Library.
71	Prepare SQL	ODBC, OLE DB, or DB-Library has prepared a Transact-SQL statement for use.
72	Exec Prepared SQL	ODBC, OLE DB, or DB-Library has executed a prepared Transact-SQL statement.
73	Unprepare SQL	ODBC, OLE DB, or DB-Library has unprepared (deleted) a prepared Transact-SQL statement.
74	CursorExecute	A cursor previously prepared on a Transact-SQL statement by ODBC, OLE DB, or DB-Library is executed.
75	CursorRecompile	A cursor that is opened on a Transact-SQL statement by ODBC or DB-Library has been recompiled either directly or due to a schema change.
76	CursorImplicitConversion	A cursor on a Transact-SQL statement is converted by SQL Server from one type to another.
77	CursorUnprepare	A prepared cursor on a Transact-SQL statement is unprepared (deleted) by ODBC, OLE DB, or DB-Library.
78	CursorClose	A cursor previously opened on a Transact-SQL statement by ODBC, OLE DB, or DB-Library is closed.
79	Missing Column Statistics	Column statistics that could have been useful for the query optimizer are not available.
80	Missing Join Predicate	A query is being executed that has no join predicate. This could result in a long-running query.
81	Server Memory Change	Microsoft SQL Server memory usage has increased or decreased by either 1 MB or 5 percent of the maximum server memory, whichever is greater.
82	UserConfigurable:1	User configurable event. Collects event data defined by the user. For more information about user configurable events, see xp_trace_generate_event.
83	UserConfigurable:2	User configurable event. Collects event data defined by the user.
84	UserConfigurable:3	User configurable event. Collects event data defined by the user.
85	UserConfigurable:4	User configurable event. Collects event data defined by the user.
86	UserConfigurable:5	User configurable event. Collects event data defined by the user.

Return Code Values

0 (success) or >1 (failure)

Result Sets

xp_trace_eventclassrequired returns this result set when both required parameters are supplied.

Permissions

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

Examples

A. Specify only a trace queue handle

This example retrieves event information for all event classes for the trace queue by using an event class of 29 (update events).

USE master

DECLARE @queue_handle int, @column_value int

SET @column_value = 16|32|8192|128|512

EXEC xp_trace_addnewqueue 1000,

    5,

    95,

    90,

    @column_value,

    @queue_handle OUTPUT

EXEC xp_trace_eventclassrequired @queue_handle,

    29

B. Specify a trace queue handle and an event class

This example retrieves connect event information for the trace queue.

USE master

DECLARE @queue_handle int, @column_value int

SET @column_value = 16|32|8192|128|512

EXEC xp_trace_addnewqueue 1000,

    5,

    95,

    90,

    @column_value,

    @queue_handle OUTPUT

EXEC xp_trace_eventclassrequired @queue_handle,

    14

See Also