Transact-SQL Reference
sp_trace_setevent
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). Microsoft® SQL Server™ 2000 will return an error if this stored procedure is executed on a trace that does not exist or whose status is not 0.
Syntax
sp_trace_setevent [ @traceid = ] trace_id
, [ @eventid = ] event_id
, [ @columnid = ] column_id
, [ @on = ] on
Arguments
[ @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 number | Event name |
Description |
|---|---|---|
| 0-9 | Reserved | |
| 10 | RPC:Completed | Occurs when a remote procedure call (RPC) has completed. |
| 11 | RPC:Starting | Occurs when an RPC has started. |
| 12 | SQL:BatchCompleted | Occurs when a Transact-SQL batch has completed. |
| 13 | SQL:BatchStarting | Occurs when a Transact-SQL batch has started. |
| 14 | Login | Occurs when a user successfully logs in to SQL Server. |
| 15 | Logout | Occurs when a user logs out of SQL Server. |
| 16 | Attention | Occurs when attention events, such as client-interrupt requests or broken client connections, happen. |
| 17 | ExistingConnection | Detects all activity by users connected to SQL Server before the trace started. |
| 18 | ServiceControl | Occurs when the SQL Server service state is modified. |
| 19 | DTCTransaction | Tracks Microsoft Distributed Transaction Coordinator (MS DTC) coordinated transactions between two or more databases. |
| 20 | Login Failed | Indicates that a login attempt to SQL Server from a client failed. |
| 21 | EventLog | Indicates that events have been logged in the Microsoft Windows NT® 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 | DOP Event | Occurs before a SELECT, INSERT, or UPDATE statement is executed. |
| 29-31 | Reserved | Use Event 28 instead. |
| 32 | 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 | 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 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 | Reserved | Use Event 40 instead. |
| 45 | Reserved | Use Event 41 instead. |
| 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. |
| 48 | Reserved | |
| 49 | Reserved | |
| 50 | SQL Transaction | 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 is opened on a Transact-SQL statement by ODBC, OLE DB, or DB-Library. |
| 54 | Transaction Log | 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 Update 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 row lock escalated or converted to a page 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 | Execution Plan | 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. |
| 73 | Unprepare SQL | ODBC, OLE DB, or DB-Library has unprepared (deleted) a prepared Transact-SQL statement or statements. |
| 74 | CursorExecute | 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 | Microsoft 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 data 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 | Show Plan Text | Displays the query plan tree of the SQL statement from the query optimizer. |
| 97 | Show Plan ALL | Displays the query plan with full compile-time details of the SQL statement executed. |
| 98 | Show Plan Statistics | Displays the query plan with full run-time details of the SQL statement executed. |
| 99 | Reserved | |
| 100 | RPC Output Parameter | Produces output values of the parameters for every RPC. |
| 101 | Reserved | |
| 102 | Audit Statement GDR | Occurs every time a GRANT, DENY, REVOKE for a statement permission is issued by any user in SQL Server. |
| 103 | Audit Object GDR | Occurs every time a GRANT, DENY, REVOKE for an object permission is issued by any user in SQL Server. |
| 104 | Audit Add/Drop Login | Occurs when a SQL Server login is added or removed; for sp_addlogin and sp_droplogin. |
| 105 | Audit Login GDR | Occurs when a Microsoft Windows® login right is added or removed; for sp_grantlogin, sp_revokelogin, and sp_denylogin. |
| 106 | Audit Login Change Property | Occurs when a property of a login, except passwords, is modified; for sp_defaultdb and sp_defaultlanguage. |
| 107 | Audit Login Change Password | Occurs when a SQL Server login password is changed.
Passwords are not recorded. |
| 108 | Audit Add Login to Server Role | Occurs when a login is added or removed from a fixed server role; for sp_addsrvrolemember, and sp_dropsrvrolemember. |
| 109 | Audit Add DB User | 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 | 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/Drop Role | Occurs when a login is added or removed as a database user to a database; for sp_addrole and sp_droprole. |
| 112 | App Role Pass Change | Occurs when a password of an application role is changed. |
| 113 | Audit Statement Permission | Occurs when a statement permission (such as CREATE TABLE) is used. |
| 114 | Audit Object Permission | Occurs when an object permission (such as SELECT) is used, both successfully or unsuccessfully. |
| 115 | Audit Backup/Restore | Occurs when a BACKUP or RESTORE command is issued. |
| 116 | Audit DBCC | Occurs when DBCC commands are issued. |
| 117 | Audit Change Audit | Occurs when audit trace modifications are made. |
| 118 | Audit Object Derived Permission | Occurs when a CREATE, ALTER, and DROP object commands are issued. |
[ @columnid = ] column_id
Is the ID of the column to be added for the event. column_id is int, with no default.
This 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 | Reserved | |
| 6 | NTUserName | Microsoft Windows NT® user name. |
| 7 | NTDomainName | Windows NT domain to which the user belongs. |
| 8 | ClientHostName | 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 | SQLSecurityLoginName | 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 milliseconds) 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 = success |
| 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. |
| 27 | EventClass | Type of event class being recorded. |
| 28 | ObjectType | Type of object (such as table, function, or stored procedure). |
| 29 | NestLevel | The nesting level at which this stored procedure is executing. See @@NESTLEVEL. |
| 30 | State | Server state, in case of an error. |
| 31 | Error | Error number. |
| 32 | Mode | 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 | ObjectOwner | Owner ID of the object referenced. |
| 38 | TargetRoleName | Name of the database or server-wide role targeted by a statement. |
| 39 | TargetUserName | User name of the target of some action. |
| 40 | DatabaseUserName | SQL Server database username of the client. |
| 41 | LoginSID | Security identification number (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 | ColumnPermissionsSet | Column-level permissions status; used by Security Auditing. |
[ @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 | @columnid | Result |
|---|---|---|
| ON (1) | NULL | Event is turned ON.
All Columns are cleared. |
| NOT NULL | Column is turned ON for the specified Event. | |
| OFF (0) | NULL | Event is turned OFF.
All Columns are cleared. |
| NOT NULL | Column is turned OFF for the specified Event. |
Return Code Values
This table describes the code values that users may get following completion of the stored procedure.
| Return code | Description |
|---|---|
| 0 | No error. |
| 1 | Unknown error. |
| 2 | The trace is currently running. Changing the trace at this time will result in an error. |
| 3 | The specified Event is not valid. The Event may not exist or it is not an appropriate one for the store procedure. |
| 4 | The specified Column is not valid. |
| 9 | The specified Trace Handle is not valid. |
| 11 | The specified Column is used internally and cannot be removed. |
| 13 | Out of memory. Returned when there is not enough memory to perform the specified action. |
| 16 | The function is not valid for this trace. |
Remarks
sp_trace_setevent is a Microsoft SQL Server 2000 stored procedure that performs many of the actions previously executed by extended stored procedures available in earlier versions of SQL Server. Use sp_trace_setevent instead of:
- 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.
Permissions
Only members of the sysadmin fixed server role can execute sp_trace_setevent.