sp_trace_setevent (Transact-SQL)

SQL Server 2012

Adds or removes an event or event column to a trace. sp_trace_setevent may be executed only on existing traces that are stopped (status is 0). An error is returned if this stored procedure is executed on a trace that does not exist or whose status is not 0.

Important

This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use Extended Events instead.

Indicates that events have been logged in the Windows application log.

22

ErrorLog

Indicates that error events have been 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

Degree of Parallelism Event (7.0 Insert)

Occurs before a SELECT, INSERT, or UPDATE statement is executed.

29-31

Reserved

Use Event 28 instead.

32

Reserved

Reserved

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 was recompiled.

38

SP:CacheHit

Indicates when a stored procedure is found in the procedure cache.

39

Deprecated

Deprecated

40

SQL:StmtStarting

Occurs when the Transact-SQL statement has started.

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 that a Transact-SQL statement within a stored procedure has started executing.

45

SP:StmtCompleted

Indicates that a Transact-SQL statement within a stored procedure has finished executing.

46

Object:Created

Indicates that an object has been created, such as for CREATE INDEX, CREATE TABLE, and CREATE DATABASE statements.

47

Object:Deleted

Indicates that an object has been deleted, such as in DROP INDEX and DROP TABLE statements.

Indicates when a cursor is opened on a Transact-SQL statement by ODBC, OLE DB, or DB-Library.

54

TransactionLog

Tracks when transactions are written to the transaction log.

55

Hash Warning

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-57

Reserved

58

Auto Stats

Indicates an automatic updating of index statistics has occurred.

59

Lock:Deadlock Chain

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 page lock escalated or converted to a TABLE or HoBT lock).

61

OLE DB Errors

Indicates that an OLE DB error has occurred.

62-66

Reserved

67

Execution Warnings

Indicates any warnings that occurred during the execution of a SQL Server statement or stored procedure.

68

Showplan Text (Unencoded)

Displays the plan tree of the Transact-SQL statement 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 on a Transact-SQL statement is prepared for use by ODBC, OLE DB, or DB-Library.

71

Prepare SQL

ODBC, OLE DB, or DB-Library has prepared a Transact-SQL statement or statements for use.

72

Exec Prepared SQL

ODBC, OLE DB, or DB-Library has executed a prepared Transact-SQL statement or statements.

A cursor previously prepared on a Transact-SQL statement by ODBC, OLE DB, or DB-Library is executed.

75

CursorRecompile

A cursor opened on a Transact-SQL statement by ODBC or DB-Library has been recompiled either directly or due to a schema change.

Triggered for ANSI and non-ANSI cursors.

76

CursorImplicitConversion

A cursor on a Transact-SQL statement is converted by SQL Server from one type to another.

Triggered for ANSI and non-ANSI cursors.

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 optimizer are not available.

80

Missing Join Predicate

Query that has no join predicate is being executed. This could result in a long-running query.

81

Server Memory Change

SQL Server memory usage has increased or decreased by either 1 megabyte (MB) or 5 percent of the maximum server memory, whichever is greater.

82-91

User Configurable (0-9)

Event data defined by the user.

92

Data File Auto Grow

Indicates that a data file was extended automatically by the server.

93

Log File Auto Grow

Indicates that a log file was extended automatically by the server.

94

Data File Auto Shrink

Indicates that a data file was shrunk automatically by the server.

95

Log File Auto Shrink

Indicates that a log file was shrunk automatically by the server.

96

Showplan Text

Displays the query plan tree of the SQL statement from the query optimizer. Note that the TextData column does not contain the Showplan for this event.

97

Showplan All

Displays the query plan with full compile-time details of the SQL statement executed. Note that the TextData column does not contain the Showplan for this event.

98

Showplan Statistics Profile

Displays the query plan with full run-time details of the SQL statement executed. Note that the TextData column does not contain the Showplan for this event.

99

Reserved

100

RPC Output Parameter

Produces output values of the parameters for every RPC.

101

Reserved

102

Audit Database Scope GDR

Occurs every time a GRANT, DENY, REVOKE for a statement permission is issued by any user in SQL Server for database-only actions such as granting permissions on a database.

103

Audit Object GDR Event

Occurs every time a GRANT, DENY, REVOKE for an object permission is issued by any user in SQL Server.

104

Audit AddLogin Event

Occurs when a SQL Server login is added or removed; for sp_addlogin and sp_droplogin.

105

Audit Login GDR Event

Occurs when a Windows login right is added or removed; for sp_grantlogin, sp_revokelogin, and sp_denylogin.

106

Audit Login Change Property Event

Occurs when a property of a login, except passwords, is modified; for sp_defaultdb and sp_defaultlanguage.

107

Audit Login Change Password Event

Occurs when a SQL Server login password is changed.

Passwords are not recorded.

108

Audit Add Login to Server Role Event

Occurs when a login is added or removed from a fixed server role; for sp_addsrvrolemember, and sp_dropsrvrolemember.

109

Audit Add DB User Event

Occurs when a login is added or removed as a database user (Windows or SQL Server) to a database; for sp_grantdbaccess, sp_revokedbaccess, sp_adduser, and sp_dropuser.

110

Audit Add Member to DB Role Event

Occurs when a login is added or removed as a database user (fixed or user-defined) to a database; for sp_addrolemember, sp_droprolemember, and sp_changegroup.

111

Audit Add Role Event

Occurs when a login is added or removed as a database user to a database; for sp_addrole and sp_droprole.

112

Audit App Role Change Password Event

Occurs when a password of an application role is changed.

113

Audit Statement Permission Event

Occurs when a statement permission (such as CREATE TABLE) is used.

114

Audit Schema Object Access Event

Occurs when an object permission (such as SELECT) is used, both successfully or unsuccessfully.

115

Audit Backup/Restore Event

Occurs when a BACKUP or RESTORE command is issued.

116

Audit DBCC Event

Occurs when DBCC commands are issued.

117

Audit Change Audit Event

Occurs when audit trace modifications are made.

118

Audit Object Derived Permission Event

Occurs when a CREATE, ALTER, and DROP object commands are issued.

119

OLEDB Call Event

Occurs when OLE DB provider calls are made for distributed queries and remote stored procedures.

120

OLEDB QueryInterface Event

Occurs when OLE DB QueryInterface calls are made for distributed queries and remote stored procedures.

121

OLEDB DataRead Event

Occurs when a data request call is made to the OLE DB provider.

122

Showplan XML

Occurs when an SQL statement executes. Include this event to identify Showplan operators. Each event is stored in a well-formed XML document. Note that the Binary column for this event contains the encoded Showplan. Use SQL Server Profiler to open the trace and view the Showplan.

123

SQL:FullTextQuery

Occurs when a full text query executes.

124

Broker:Conversation

Reports the progress of a Service Broker conversation.

125

Deprecation Announcement

Occurs when you use a feature that will be removed from a future version of SQL Server.

126

Deprecation Final Support

Occurs when you use a feature that will be removed from the next major release of SQL Server.

127

Exchange Spill Event

Occurs when communication buffers in a parallel query plan have been temporarily written to the tempdb database.

128

Audit Database Management Event

Occurs when a database is created, altered, or dropped.

129

Audit Database Object Management Event

Occurs when a CREATE, ALTER, or DROP statement executes on database objects, such as schemas.

130

Audit Database Principal Management Event

Occurs when principals, such as users, are created, altered, or dropped from a database.

131

Audit Schema Object Management Event

Occurs when server objects are created, altered, or dropped.

132

Audit Server Principal Impersonation Event

Occurs when there is an impersonation within server scope, such as EXECUTE AS LOGIN.

133

Audit Database Principal Impersonation Event

Occurs when an impersonation occurs within the database scope, such as EXECUTE AS USER or SETUSER.

134

Audit Server Object Take Ownership Event

Occurs when the owner is changed for objects in server scope.

135

Audit Database Object Take Ownership Event

Occurs when a change of owner for objects within database scope occurs.

136

Broker:Conversation Group

Occurs when Service Broker creates a new conversation group or drops an existing conversation group.

137

Blocked Process Report

Occurs when a process has been blocked for more than a specified amount of time. Does not include system processes or processes that are waiting on non deadlock-detectable resources. Use sp_configure to configure the threshold and frequency at which reports are generated.

138

Broker:Connection

Reports the status of a transport connection managed by Service Broker.

139

Broker:Forwarded Message Sent

Occurs when Service Broker forwards a message.

140

Broker:Forwarded Message Dropped

Occurs when Service Broker drops a message that was intended to be forwarded.

141

Broker:Message Classify

Occurs when Service Broker determines the routing for a message.

142

Broker:Transmission

Indicates that errors have occurred in the Service Broker transport layer. The error number and state values indicate the source of the error.

143

Broker:Queue Disabled

Indicates a poison message was detected because there were five consecutive transaction rollbacks on a Service Broker queue. The event contains the database ID and queue ID of the queue that contains the poison message.

144-145

Reserved

146

Showplan XML Statistics Profile

Occurs when an SQL statement executes. Identifies the Showplan operators and displays complete, compile-time data. Note that the Binary column for this event contains the encoded Showplan. Use SQL Server Profiler to open the trace and view the Showplan.

148

Deadlock Graph

Occurs when an attempt to acquire a lock is canceled because the attempt was part of a deadlock and was chosen as the deadlock victim. Provides an XML description of a deadlock.

149

Broker:Remote Message Acknowledgement

Occurs when Service Broker sends or receives a message acknowledgement.

150

Trace File Close

Occurs when a trace file closes during a trace file rollover.

151

Reserved

152

Audit Change Database Owner

Occurs when ALTER AUTHORIZATION is used to change the owner of a database and permissions are checked to do that.

153

Audit Schema Object Take Ownership Event

Occurs when ALTER AUTHORIZATION is used to assign an owner to an object and permissions are checked to do that.

154

Reserved

155

FT:Crawl Started

Occurs when a full-text crawl (population) starts. Use to check if a crawl request is picked up by worker tasks.

156

FT:Crawl Stopped

Occurs when a full-text crawl (population) stops. Stops occur when a crawl completes successfully or when a fatal error occurs.

157

FT:Crawl Aborted

Occurs when an exception is encountered during a full-text crawl. Usually causes the full-text crawl to stop.

158

Audit Broker Conversation

Reports audit messages related to Service Broker dialog security.

159

Audit Broker Login

Reports audit messages related to Service Broker transport security.

160

Broker:Message Undeliverable

Occurs when Service Broker is unable to retain a received message that should have been delivered to a service.

161

Broker:Corrupted Message

Occurs when Service Broker receives a corrupted message.

162

User Error Message

Displays error messages that users see in the case of an error or exception.

163

Broker:Activation

Occurs when a queue monitor starts an activation stored procedure, sends a QUEUE_ACTIVATION notification, or when an activation stored procedure started by a queue monitor exits.

164

Object:Altered

Occurs when a database object is altered.

165

Performance statistics

Occurs when a compiled query plan has been cached for the first time, recompiled, or removed from the plan cache.

166

SQL:StmtRecompile

Occurs when a statement-level recompilation occurs.

167

Database Mirroring State Change

Occurs when the state of a mirrored database changes.

168

Showplan XML For Query Compile

Occurs when an SQL statement compiles. Displays the complete, compile-time data. Note that the Binary column for this event contains the encoded Showplan. Use SQL Server Profiler to open the trace and view the Showplan.

Indicates that a grant, deny, or revoke event for permissions in server scope occurred, such as creating a login.

171

Audit Server Object GDR Event

Indicates that a grant, deny, or revoke event for a schema object, such as a table or function, occurred.

172

Audit Database Object GDR Event

Indicates that a grant, deny, or revoke event for database objects, such as assemblies and schemas, occurred.

173

Audit Server Operation Event

Occurs when Security Audit operations such as altering settings, resources, external access, or authorization are used.

175

Audit Server Alter Trace Event

Occurs when a statement checks for the ALTER TRACE permission.

176

Audit Server Object Management Event

Occurs when server objects are created, altered, or dropped.

177

Audit Server Principal Management Event

Occurs when server principals are created, altered, or dropped.

178

Audit Database Operation Event

Occurs when database operations occur, such as checkpoint or subscribe query notification.

180

Audit Database Object Access Event

Occurs when database objects, such as schemas, are accessed.

181

TM: Begin Tran starting

Occurs when a BEGIN TRANSACTION request starts.

182

TM: Begin Tran completed

Occurs when a BEGIN TRANSACTION request completes.

183

TM: Promote Tran starting

Occurs when a PROMOTE TRANSACTION request starts.

184

TM: Promote Tran completed

Occurs when a PROMOTE TRANSACTION request completes.

185

TM: Commit Tran starting

Occurs when a COMMIT TRANSACTION request starts.

186

TM: Commit Tran completed

Occurs when a COMMIT TRANSACTION request completes.

187

TM: Rollback Tran starting

Occurs when a ROLLBACK TRANSACTION request starts.

188

TM: Rollback Tran completed

Occurs when a ROLLBACK TRANSACTION request completes.

189

Lock:Timeout (timeout > 0)

Occurs when a request for a lock on a resource, such as a page, times out.

190

Progress Report: Online Index Operation

Reports the progress of an online index build operation while the build process is running.

191

TM: Save Tran starting

Occurs when a SAVE TRANSACTION request starts.

192

TM: Save Tran completed

Occurs when a SAVE TRANSACTION request completes.

193

Background Job Error

Occurs when a background job terminates abnormally.

194

OLEDB Provider Information

Occurs when a distributed query runs and collects information corresponding to the provider connection.

195

Mount Tape

Occurs when a tape mount request is received.

196

Assembly Load

Occurs when a request to load a CLR assembly occurs.

197

Reserved

198

XQuery Static Type

Occurs when an XQuery expression is executed. This event class provides the static type of the XQuery expression.

199

QN: subscription

Occurs when a query registration cannot be subscribed. The TextData column contains information about the event.

200

QN: parameter table

Information about active subscriptions is stored in internal parameter tables. This event class occurs when a parameter table is created or deleted. Typically, these tables are created or deleted when the database is restarted. The TextData column contains information about the event.

201

QN: template

A query template represents a class of subscription queries. Typically, queries in the same class are identical except for their parameter values. This event class occurs when a new subscription request falls into an already existing class of (Match), a new class (Create), or a Drop class, which indicates cleanup of templates for query classes without active subscriptions. The TextData column contains information about the event.

Indicates that SQL Server successfully produced an execution plan for a query or batch that contained a plan guide.

218

Plan Guide Unsuccessful

Indicates that SQL Server could not produce an execution plan for a query or batch that contained a plan guide. SQL Server attempted to generate an execution plan for this query or batch without applying the plan guide. An invalid plan guide may be the cause of this problem. You can validate the plan guide by using the sys.fn_validate_plan_guide system function.

235

Audit Fulltext

[ @columnid= ] column_id

Is the ID of the column to be added for the event. column_id is int, with no default.

The following table lists the columns that can be added for an event.

Column number

Column name

Description

1

TextData

Text value dependent on the event class that is captured in the trace.

2

BinaryData

Binary value dependent on the event class captured in the trace.

3

DatabaseID

ID of the database specified by the USE database statement, or the default database if no USE database statement is issued for a given connection.

The value for a database can be determined by using the DB_ID function.

4

TransactionID

System-assigned ID of the transaction.

5

LineNumber

Contains the number of the line that contains the error. For events that involve Transact-SQL statements, like SP:StmtStarting, the LineNumber contains the line number of the statement in the stored procedure or batch.

6

NTUserName

Microsoft Windows user name.

7

NTDomainName

Windows domain to which the user belongs.

8

HostName

Name of the client computer that originated the request.

9

ClientProcessID

ID assigned by the client computer to the process in which the client application is running.

10

ApplicationName

Name of the client application that created the connection to an instance of SQL Server. This column is populated with the values passed by the application rather than the displayed name of the program.

11

LoginName

SQL Server login name of the client.

12

SPID

Server Process ID assigned by SQL Server to the process associated with the client.

13

Duration

Amount of elapsed time (in microseconds) taken by the event. This data column is not populated by the Hash Warning event.

14

StartTime

Time at which the event started, when available.

15

EndTime

Time at which the event ended. This column is not populated for starting event classes, such as SQL:BatchStarting or SP:Starting. It is also not populated by the Hash Warning event.

16

Reads

Number of logical disk reads performed by the server on behalf of the event. This column is not populated by the Lock:Released event.

17

Writes

Number of physical disk writes performed by the server on behalf of the event.

18

CPU

Amount of CPU time (in milliseconds) used by the event.

19

Permissions

Represents the bitmap of permissions; used by Security Auditing.

20

Severity

Severity level of an exception.

21

EventSubClass

Type of event subclass. This data column is not populated for all event classes.

22

ObjectID

System-assigned ID of the object.

23

Success

Success of the permissions usage attempt; used for auditing.

1 = success0 = failure

24

IndexID

ID for the index on the object affected by the event. To determine the index ID for an object, use the indid column of the sysindexes system table.

25

IntegerData

Integer value dependent on the event class captured in the trace.

26

ServerName

Name of the instance of SQL Server, either servername or servername\instancename, being traced.

Lock mode of the lock acquired. This column is not populated by the Lock:Released event.

33

Handle

Handle of the object referenced in the event.

34

ObjectName

Name of object accessed.

35

DatabaseName

Name of the database specified in the USE database statement.

36

FileName

Logical name of the file name modified.

37

OwnerName

Owner name of the referenced object.

38

RoleName

Name of the database or server-wide role targeted by a statement.

39

TargetUserName

User name of the target of some action.

40

DBUserName

SQL Server database user name of the client.

41

LoginSid

Security identifier (SID) of the logged-in user.

42

TargetLoginName

Login name of the target of some action.

43

TargetLoginSid

SID of the login that is the target of some action.

44

ColumnPermissions

Column-level permissions status; used by Security Auditing.

45

LinkedServerName

Name of the linked server.

46

ProviderName

Name of the OLE DB provider.

47

MethodName

Name of the OLE DB method.

48

RowCounts

Number of rows in the batch.

49

RequestID

ID of the request containing the statement.

50

XactSequence

A token to describe the current transaction.

51

EventSequence

Sequence number for this event.

52

BigintData1

bigint value, which is dependent on the event class captured in the trace.

53

BigintData2

bigint value, which is dependent on the event class captured in the trace.

54

GUID

GUID value, which is dependent on the event class captured in the trace.

55

IntegerData2

Integer value, which is dependent on the event class captured in the trace.

56

ObjectID2

ID of the related object or entity, if available.

57

Type

Integer value, which is dependent on the event class captured in the trace.

58

OwnerID

Type of the object that owns the lock. For lock events only.

59

ParentName

Name of the schema the object is within.

60

IsSystem

Indicates whether the event occurred on a system process or a user process.

1 = system

0 = user.

61

Offset

Starting offset of the statement within the stored procedure or batch.

62

SourceDatabaseID

ID of the database in which the source of the object exists.

63

SqlHandle

64-bit hash based on the text of an ad hoc query or the database and object ID of an SQL object. This value can be passed to sys.dm_exec_sql_text() to retrieve the associated SQL text.

64

SessionLoginName

The login name of the user who originated the session. For example, if you connect to SQL Server using Login1 and execute a statement as Login2, SessionLoginName displays Login1, while LoginName displays Login2. This data column displays both SQL Server and Windows logins.

[ @on=]on

Specifies whether to turn the event ON (1) or OFF (0). on is bit, with no default.

If on is set to 1, and column_id is NULL, then the event is set to ON and all columns are cleared. If column_id is not null, then the column is set to ON for that event.

If on is set to 0, and column_id is NULL, then the event is turned OFF and all columns are cleared. If column_id is not null, then the column is turned OFF.

sp_trace_setevent performs many of the actions previously executed by extended stored procedures available in earlier versions of SQL Server. Use sp_trace_setevent instead of the following:

xp_trace_addnewqueue

xp_trace_eventclassrequired

xp_trace_seteventclassrequired

Users must execute sp_trace_setevent for each column added for each event. During each execution, if @on is set to 1, sp_trace_setevent adds the specified event to the list of events of the trace. If @on is set to 0, sp_trace_setevent removes the specified event from the list.

Parameters of all SQL Trace stored procedures (sp_trace_xx) are strictly typed. If these parameters are not called with the correct input parameter data types, as specified in the argument description, the stored procedure will return an error.