TechNet
Export (0) Print
Expand All

sp_trace_setevent (Transact-SQL)

 

THIS TOPIC APPLIES TO:yesSQL Server (starting with 2008)noAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

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.

System_CAPS_ICON_important.jpg 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.

Applies to: SQL Server (SQL Server 2008 through current version).

Topic link icon Transact-SQL Syntax Conventions

  
sp_trace_setevent [ @traceid = ] trace_id   
          , [ @eventid = ] event_id  
          , [ @columnid = ] column_id  
          , [ @on = ] on  

[ @traceid= ] trace_id
Is the ID of the trace to be modified. trace_id is int, with no default. The user employs this trace_id value to identify, modify, and control the trace.

[ @eventid= ] event_id
Is the ID of the event to turn on. event_id is int, with no default.

This table lists the events that can be added to or removed from a trace.

Event numberEvent nameDescription
0-9ReservedReserved
10RPC:CompletedOccurs when a remote procedure call (RPC) has completed.
11RPC:StartingOccurs when an RPC has started.
12SQL:BatchCompletedOccurs when a Transact-SQL batch has completed.
13SQL:BatchStartingOccurs when a Transact-SQL batch has started.
14Audit LoginOccurs when a user successfully logs in to SQL Server.
15Audit LogoutOccurs when a user logs out of SQL Server.
16AttentionOccurs when attention events, such as client-interrupt requests or broken client connections, happen.
17ExistingConnectionDetects all activity by users connected to SQL Server before the trace started.
18Audit Server Starts and StopsOccurs when the SQL Server service state is modified.
19DTCTransactionTracks Microsoft Distributed Transaction Coordinator (MS DTC) coordinated transactions between two or more databases.
20Audit Login FailedIndicates that a login attempt to SQL Server from a client failed.
21EventLogIndicates that events have been logged in the Windows application log.
22ErrorLogIndicates that error events have been logged in the SQL Server error log.
23Lock:ReleasedIndicates that a lock on a resource, such as a page, has been released.
24Lock:AcquiredIndicates acquisition of a lock on a resource, such as a data page.
25Lock:DeadlockIndicates that two concurrent transactions have deadlocked each other by trying to obtain incompatible locks on resources the other transaction owns.
26Lock:CancelIndicates that the acquisition of a lock on a resource has been canceled (for example, due to a deadlock).
27Lock:TimeoutIndicates 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.
28Degree of Parallelism Event (7.0 Insert)Occurs before a SELECT, INSERT, or UPDATE statement is executed.
29-31ReservedUse Event 28 instead.
32ReservedReserved
33ExceptionIndicates that an exception has occurred in SQL Server.
34SP:CacheMissIndicates when a stored procedure is not found in the procedure cache.
35SP:CacheInsertIndicates when an item is inserted into the procedure cache.
36SP:CacheRemoveIndicates when an item is removed from the procedure cache.
37SP:RecompileIndicates that a stored procedure was recompiled.
38SP:CacheHitIndicates when a stored procedure is found in the procedure cache.
39DeprecatedDeprecated
40SQL:StmtStartingOccurs when the Transact-SQL statement has started.
41SQL:StmtCompletedOccurs when the Transact-SQL statement has completed.
42SP:StartingIndicates when the stored procedure has started.
43SP:CompletedIndicates when the stored procedure has completed.
44SP:StmtStartingIndicates that a Transact-SQL statement within a stored procedure has started executing.
45SP:StmtCompletedIndicates that a Transact-SQL statement within a stored procedure has finished executing.
46Object:CreatedIndicates that an object has been created, such as for CREATE INDEX, CREATE TABLE, and CREATE DATABASE statements.
47Object:DeletedIndicates that an object has been deleted, such as in DROP INDEX and DROP TABLE statements.
48Reserved
49Reserved
50SQL TransactionTracks Transact-SQL BEGIN, COMMIT, SAVE, and ROLLBACK TRANSACTION statements.
51Scan:StartedIndicates when a table or index scan has started.
52Scan:StoppedIndicates when a table or index scan has stopped.
53CursorOpenIndicates when a cursor is opened on a Transact-SQL statement by ODBC, OLE DB, or DB-Library.
54TransactionLogTracks when transactions are written to the transaction log.
55Hash WarningIndicates 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-57Reserved
58Auto StatsIndicates an automatic updating of index statistics has occurred.
59Lock:Deadlock ChainProduced for each of the events leading up to the deadlock.
60Lock:EscalationIndicates 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).
61OLE DB ErrorsIndicates that an OLE DB error has occurred.
62-66Reserved
67Execution WarningsIndicates any warnings that occurred during the execution of a SQL Server statement or stored procedure.
68Showplan Text (Unencoded)Displays the plan tree of the Transact-SQL statement executed.
69Sort WarningsIndicates 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).
70CursorPrepareIndicates when a cursor on a Transact-SQL statement is prepared for use by ODBC, OLE DB, or DB-Library.
71Prepare SQLODBC, OLE DB, or DB-Library has prepared a Transact-SQL statement or statements for use.
72Exec Prepared SQLODBC, OLE DB, or DB-Library has executed a prepared Transact-SQL statement or statements.
73Unprepare SQLODBC, OLE DB, or DB-Library has unprepared (deleted) a prepared Transact-SQL statement or statements.
74CursorExecuteA cursor previously prepared on a Transact-SQL statement by ODBC, OLE DB, or DB-Library is executed.
75CursorRecompileA 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.
76CursorImplicitConversionA cursor on a Transact-SQL statement is converted by SQL Server from one type to another.

Triggered for ANSI and non-ANSI cursors.
77CursorUnprepareA prepared cursor on a Transact-SQL statement is unprepared (deleted) by ODBC, OLE DB, or DB-Library.
78CursorCloseA cursor previously opened on a Transact-SQL statement by ODBC, OLE DB, or DB-Library is closed.
79Missing Column StatisticsColumn statistics that could have been useful for the optimizer are not available.
80Missing Join PredicateQuery that has no join predicate is being executed. This could result in a long-running query.
81Server Memory ChangeSQL Server memory usage has increased or decreased by either 1 megabyte (MB) or 5 percent of the maximum server memory, whichever is greater.
82-91User Configurable (0-9)Event data defined by the user.
92Data File Auto GrowIndicates that a data file was extended automatically by the server.
93Log File Auto GrowIndicates that a log file was extended automatically by the server.
94Data File Auto ShrinkIndicates that a data file was shrunk automatically by the server.
95Log File Auto ShrinkIndicates that a log file was shrunk automatically by the server.
96Showplan TextDisplays 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.
97Showplan AllDisplays 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.
98Showplan Statistics ProfileDisplays 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.
99Reserved
100RPC Output ParameterProduces output values of the parameters for every RPC.
101Reserved
102Audit Database Scope GDROccurs 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.
103Audit Object GDR EventOccurs every time a GRANT, DENY, REVOKE for an object permission is issued by any user in SQL Server.
104Audit AddLogin EventOccurs when a SQL Server login is added or removed; for sp_addlogin and sp_droplogin.
105Audit Login GDR EventOccurs when a Windows login right is added or removed; for sp_grantlogin, sp_revokelogin, and sp_denylogin.
106Audit Login Change Property EventOccurs when a property of a login, except passwords, is modified; for sp_defaultdb and sp_defaultlanguage.
107Audit Login Change Password EventOccurs when a SQL Server login password is changed.

Passwords are not recorded.
108Audit Add Login to Server Role EventOccurs when a login is added or removed from a fixed server role; for sp_addsrvrolemember, and sp_dropsrvrolemember.
109Audit Add DB User EventOccurs 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.
110Audit Add Member to DB Role EventOccurs 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.
111Audit Add Role EventOccurs when a login is added or removed as a database user to a database; for sp_addrole and sp_droprole.
112Audit App Role Change Password EventOccurs when a password of an application role is changed.
113Audit Statement Permission EventOccurs when a statement permission (such as CREATE TABLE) is used.
114Audit Schema Object Access EventOccurs when an object permission (such as SELECT) is used, both successfully or unsuccessfully.
115Audit Backup/Restore EventOccurs when a BACKUP or RESTORE command is issued.
116Audit DBCC EventOccurs when DBCC commands are issued.
117Audit Change Audit EventOccurs when audit trace modifications are made.
118Audit Object Derived Permission EventOccurs when a CREATE, ALTER, and DROP object commands are issued.
119OLEDB Call EventOccurs when OLE DB provider calls are made for distributed queries and remote stored procedures.
120OLEDB QueryInterface EventOccurs when OLE DB QueryInterface calls are made for distributed queries and remote stored procedures.
121OLEDB DataRead EventOccurs when a data request call is made to the OLE DB provider.
122Showplan XMLOccurs 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.
123SQL:FullTextQueryOccurs when a full text query executes.
124Broker:ConversationReports the progress of a Service Broker conversation.
125Deprecation AnnouncementOccurs when you use a feature that will be removed from a future version of SQL Server.
126Deprecation Final SupportOccurs when you use a feature that will be removed from the next major release of SQL Server.
127Exchange Spill EventOccurs when communication buffers in a parallel query plan have been temporarily written to the tempdb database.
128Audit Database Management EventOccurs when a database is created, altered, or dropped.
129Audit Database Object Management EventOccurs when a CREATE, ALTER, or DROP statement executes on database objects, such as schemas.
130Audit Database Principal Management EventOccurs when principals, such as users, are created, altered, or dropped from a database.
131Audit Schema Object Management EventOccurs when server objects are created, altered, or dropped.
132Audit Server Principal Impersonation EventOccurs when there is an impersonation within server scope, such as EXECUTE AS LOGIN.
133Audit Database Principal Impersonation EventOccurs when an impersonation occurs within the database scope, such as EXECUTE AS USER or SETUSER.
134Audit Server Object Take Ownership EventOccurs when the owner is changed for objects in server scope.
135Audit Database Object Take Ownership EventOccurs when a change of owner for objects within database scope occurs.
136Broker:Conversation GroupOccurs when Service Broker creates a new conversation group or drops an existing conversation group.
137Blocked Process ReportOccurs 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.
138Broker:ConnectionReports the status of a transport connection managed by Service Broker.
139Broker:Forwarded Message SentOccurs when Service Broker forwards a message.
140Broker:Forwarded Message DroppedOccurs when Service Broker drops a message that was intended to be forwarded.
141Broker:Message ClassifyOccurs when Service Broker determines the routing for a message.
142Broker:TransmissionIndicates that errors have occurred in the Service Broker transport layer. The error number and state values indicate the source of the error.
143Broker:Queue DisabledIndicates 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-145Reserved
146Showplan XML Statistics ProfileOccurs 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.
148Deadlock GraphOccurs 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.
149Broker:Remote Message AcknowledgementOccurs when Service Broker sends or receives a message acknowledgement.
150Trace File CloseOccurs when a trace file closes during a trace file rollover.
151Reserved
152Audit Change Database OwnerOccurs when ALTER AUTHORIZATION is used to change the owner of a database and permissions are checked to do that.
153Audit Schema Object Take Ownership EventOccurs when ALTER AUTHORIZATION is used to assign an owner to an object and permissions are checked to do that.
154Reserved
155FT:Crawl StartedOccurs when a full-text crawl (population) starts. Use to check if a crawl request is picked up by worker tasks.
156FT:Crawl StoppedOccurs when a full-text crawl (population) stops. Stops occur when a crawl completes successfully or when a fatal error occurs.
157FT:Crawl AbortedOccurs when an exception is encountered during a full-text crawl. Usually causes the full-text crawl to stop.
158Audit Broker ConversationReports audit messages related to Service Broker dialog security.
159Audit Broker LoginReports audit messages related to Service Broker transport security.
160Broker:Message UndeliverableOccurs when Service Broker is unable to retain a received message that should have been delivered to a service.
161Broker:Corrupted MessageOccurs when Service Broker receives a corrupted message.
162User Error MessageDisplays error messages that users see in the case of an error or exception.
163Broker:ActivationOccurs 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.
164Object:AlteredOccurs when a database object is altered.
165Performance statisticsOccurs when a compiled query plan has been cached for the first time, recompiled, or removed from the plan cache.
166SQL:StmtRecompileOccurs when a statement-level recompilation occurs.
167Database Mirroring State ChangeOccurs when the state of a mirrored database changes.
168Showplan XML For Query CompileOccurs 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.
169Showplan All For Query CompileOccurs when an SQL statement compiles. Displays complete, compile-time data. Use to identify Showplan operators.
170Audit Server Scope GDR EventIndicates that a grant, deny, or revoke event for permissions in server scope occurred, such as creating a login.
171Audit Server Object GDR EventIndicates that a grant, deny, or revoke event for a schema object, such as a table or function, occurred.
172Audit Database Object GDR EventIndicates that a grant, deny, or revoke event for database objects, such as assemblies and schemas, occurred.
173Audit Server Operation EventOccurs when Security Audit operations such as altering settings, resources, external access, or authorization are used.
175Audit Server Alter Trace EventOccurs when a statement checks for the ALTER TRACE permission.
176Audit Server Object Management EventOccurs when server objects are created, altered, or dropped.
177Audit Server Principal Management EventOccurs when server principals are created, altered, or dropped.
178Audit Database Operation EventOccurs when database operations occur, such as checkpoint or subscribe query notification.
180Audit Database Object Access EventOccurs when database objects, such as schemas, are accessed.
181TM: Begin Tran startingOccurs when a BEGIN TRANSACTION request starts.
182TM: Begin Tran completedOccurs when a BEGIN TRANSACTION request completes.
183TM: Promote Tran startingOccurs when a PROMOTE TRANSACTION request starts.
184TM: Promote Tran completedOccurs when a PROMOTE TRANSACTION request completes.
185TM: Commit Tran startingOccurs when a COMMIT TRANSACTION request starts.
186TM: Commit Tran completedOccurs when a COMMIT TRANSACTION request completes.
187TM: Rollback Tran startingOccurs when a ROLLBACK TRANSACTION request starts.
188TM: Rollback Tran completedOccurs when a ROLLBACK TRANSACTION request completes.
189Lock:Timeout (timeout > 0)Occurs when a request for a lock on a resource, such as a page, times out.
190Progress Report: Online Index OperationReports the progress of an online index build operation while the build process is running.
191TM: Save Tran startingOccurs when a SAVE TRANSACTION request starts.
192TM: Save Tran completedOccurs when a SAVE TRANSACTION request completes.
193Background Job ErrorOccurs when a background job terminates abnormally.
194OLEDB Provider InformationOccurs when a distributed query runs and collects information corresponding to the provider connection.
195Mount TapeOccurs when a tape mount request is received.
196Assembly LoadOccurs when a request to load a CLR assembly occurs.
197Reserved
198XQuery Static TypeOccurs when an XQuery expression is executed. This event class provides the static type of the XQuery expression.
199QN: subscriptionOccurs when a query registration cannot be subscribed. The TextData column contains information about the event.
200QN: parameter tableInformation 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.
201QN: templateA 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.
202QN: dynamicsTracks internal activities of query notifications. The TextData column contains information about the event.
212Bitmap WarningIndicates when bitmap filters have been disabled in a query.
213Database Suspect Data PageIndicates when a page is added to the suspect_pages table in msdb.
214CPU threshold exceededIndicates when the Resource Governor detects a query has exceeded the CPU threshold value (REQUEST_MAX_CPU_TIME_SEC).
215Indicates when a LOGON trigger or Resource Governor classifier function starts execution.Indicates when a LOGON trigger or Resource Governor classifier function starts execution.
216PreConnect:CompletedIndicates when a LOGON trigger or Resource Governor classifier function completes execution.
217Plan Guide SuccessfulIndicates that SQL Server successfully produced an execution plan for a query or batch that contained a plan guide.
218Plan Guide UnsuccessfulIndicates 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.
235Audit 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 numberColumn nameDescription
1TextDataText value dependent on the event class that is captured in the trace.
2BinaryDataBinary value dependent on the event class captured in the trace.
3DatabaseIDID 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.
4TransactionIDSystem-assigned ID of the transaction.
5LineNumberContains 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.
6NTUserNameMicrosoft Windows user name.
7NTDomainNameWindows domain to which the user belongs.
8HostNameName of the client computer that originated the request.
9ClientProcessIDID assigned by the client computer to the process in which the client application is running.
10ApplicationNameName 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.
11LoginNameSQL Server login name of the client.
12SPIDServer Process ID assigned by SQL Server to the process associated with the client.
13DurationAmount of elapsed time (in microseconds) taken by the event. This data column is not populated by the Hash Warning event.
14StartTimeTime at which the event started, when available.
15EndTimeTime 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.
16ReadsNumber of logical disk reads performed by the server on behalf of the event. This column is not populated by the Lock:Released event.
17WritesNumber of physical disk writes performed by the server on behalf of the event.
18CPUAmount of CPU time (in milliseconds) used by the event.
19PermissionsRepresents the bitmap of permissions; used by Security Auditing.
20SeveritySeverity level of an exception.
21EventSubClassType of event subclass. This data column is not populated for all event classes.
22ObjectIDSystem-assigned ID of the object.
23SuccessSuccess of the permissions usage attempt; used for auditing.

 1 = success0 = failure
24IndexIDID 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.
25IntegerDataInteger value dependent on the event class captured in the trace.
26ServerNameName of the instance of SQL Server, either servername or servername\instancename, being traced.
27EventClassType of event class being recorded.
28ObjectTypeType of object, such as: table, function, or stored procedure.
29NestLevelThe nesting level at which this stored procedure is executing. See @@NESTLEVEL (Transact-SQL).
30StateServer state, in case of an error.
31ErrorError number.
32ModeLock mode of the lock acquired. This column is not populated by the Lock:Released event.
33HandleHandle of the object referenced in the event.
34ObjectNameName of object accessed.
35DatabaseNameName of the database specified in the USE database statement.
36FileNameLogical name of the file name modified.
37OwnerNameOwner name of the referenced object.
38RoleNameName of the database or server-wide role targeted by a statement.
39TargetUserNameUser name of the target of some action.
40DBUserNameSQL Server database user name of the client.
41LoginSidSecurity identifier (SID) of the logged-in user.
42TargetLoginNameLogin name of the target of some action.
43TargetLoginSidSID of the login that is the target of some action.
44ColumnPermissionsColumn-level permissions status; used by Security Auditing.
45LinkedServerNameName of the linked server.
46ProviderNameName of the OLE DB provider.
47MethodNameName of the OLE DB method.
48RowCountsNumber of rows in the batch.
49RequestIDID of the request containing the statement.
50XactSequenceA token to describe the current transaction.
51EventSequenceSequence number for this event.
52BigintData1bigint value, which is dependent on the event class captured in the trace.
53BigintData2bigint value, which is dependent on the event class captured in the trace.
54GUIDGUID value, which is dependent on the event class captured in the trace.
55IntegerData2Integer value, which is dependent on the event class captured in the trace.
56ObjectID2ID of the related object or entity, if available.
57TypeInteger value, which is dependent on the event class captured in the trace.
58OwnerIDType of the object that owns the lock. For lock events only.
59ParentNameName of the schema the object is within.
60IsSystemIndicates whether the event occurred on a system process or a user process.

 1 = system

 0 = user.
61OffsetStarting offset of the statement within the stored procedure or batch.
62SourceDatabaseIDID of the database in which the source of the object exists.
63SqlHandle64-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.
64SessionLoginNameThe 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.

This table illustrates the interaction between @on and @columnid.

@on@columnidResult
ON (1)NULLEvent is turned ON.

All Columns are cleared.
NOT NULLColumn is turned ON for the specified Event.
OFF (0)NULLEvent is turned OFF.

All Columns are cleared.
NOT NULLColumn is turned OFF for the specified Event.

The following table describes the code values that users may get following completion of the stored procedure.

Return codeDescription
0No error.
1Unknown error.
2The trace is currently running. Changing the trace at this time will result in an error.
3The specified event is not valid. The event may not exist or it is not an appropriate one for the store procedure.
4The specified column is not valid.
9The specified trace handle is not valid.
11The specified column is used internally and cannot be removed.
13Out of memory. Returned when there is not enough memory to perform the specified action.
16The function is not valid for this trace.

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.

For an example of using trace stored procedures, see Create a Trace (Transact-SQL).

User must have ALTER TRACE permission.

sys.fn_trace_geteventinfo (Transact-SQL)
sys.fn_trace_getinfo (Transact-SQL)
sp_trace_generateevent (Transact-SQL)
SQL Server Event Class Reference
SQL Trace

Community Additions

ADD
Show:
© 2016 Microsoft