Describing Events by Using Data Columns
SQL Trace uses data columns in the trace output to describe events that are returned when the trace runs.
Note
In SQL Server 2005, trace column values greater than 1 GB return an error and are truncated in the trace output.
The following table describes the Microsoft SQL Server Profiler data columns, which are the same data columns as those used by SQL Trace, and indicates the columns that are selected by default.
Data column
Column number
Description
ApplicationName1
10
The 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 and not the name of the program.
BigintData1
52
Value (bigint data type), which depends on the event class specified in the trace.
BigintData2
53
Value (bigint data type), which depends on the event class specified in the trace.
Binary Data
2
The binary value dependent on the event class that is captured in the trace.
ClientProcessID1
9
The ID assigned by the host computer to the process where the client application is running. This data column is populated if the client process ID is provided by the client.
ColumnPermissions
44
Indicates whether a column permission was set. You can parse the statement text to determine which permissions were applied to which columns.
CPU
18
The amount of CPU time (in milliseconds) that is used by the event.
Database ID1
3
The ID of the database specified by the USE database_name statement, or the ID of the default database if no USE database_namestatement has been issued for a given instance. SQL Server Profiler displays the name of the database if the Server Name data column is captured in the trace and the server is available. Determine the value for a database by using the DB_ID function.
DatabaseName
35
The name of the database in which the user statement is running.
DBUserName1
40
The SQL Server user name of the client.
Duration
13
The duration (in microseconds) of the event.
.gif "ms190762.note(en-US,SQL.90).gif")
Note:
In SQL Server 2005, the server reports the duration of an event in microseconds (one millionth, or 10-6, of a second) and the amount of CPU time used by the event in milliseconds (one thousandth, or 10-3, of a second). In SQL Server 2000, the server reported both duration and CPU time in milliseconds. In SQL Server 2005, the SQL Server Profiler graphical user interface displays the Duration column in milliseconds by default, but when a trace is saved to either a file or a database table, the Duration column value is written in microseconds.
EndTime
15
The time at which the event ended. This column is not populated for event classes that refer to an event that is starting, such as SQL:BatchStarting or SP:Starting.
Error
31
The error number of a given event. Often this is the error number stored in sysmessages.
EventClass1
27
The type of event class that is captured.
EventSequence
51
Sequence number for this event.
EventSubClass1
21
The type of event subclass, which provides further information about each event class. For example, event subclass values for the Execution Warning event class represent the type of execution warning:
1 = Query wait. The query must wait for resources before it can execute; for example, memory.
2 = Query time-out. The query timed out while waiting for required resources to execute. This data column is not populated for all event classes.
GUID
54
GUID value which depends on the event class specified in the trace.
FileName
36
The logical name of the file that is modified.
Handle
33
The integer used by ODBC, OLE DB, or DB-Library to coordinate server execution.
HostName1
8
The name of the computer on which the client is running. This data column is populated if the host name is provided by the client. To determine the host name, use the HOST_NAME function.
IndexID
24
The 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.
IntegerData
25
The integer value dependent on the event class captured in the trace.
IntegerData2
55
The integer value dependent on the event class captured in the trace.
IsSystem
60
Indicates whether the event occurred on a system process or a user process:
1 = system
0 = user
LineNumber
5
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.
LinkedServerName
45
Name of the linked server.
LoginName
11
The name of the login of the user (either SQL Server security login or the Windows login credentials in the form of DOMAIN\Username).
LoginSid1
41
The security identifier (SID) of the logged-in user. You can find this information in the sys.server_principals view of the master database. Each login to the server has a unique ID.
MethodName
47
Name of the OLEDB method.
Mode
32
The integer used by various events to describe a state the event is requesting or has received.
NestLevel
29
The integer that represents the data returned by @@NESTLEVEL.
NTDomainName1
7
The Microsoft Windows domain to which the user belongs.
NTUserName1
6
The Windows user name.
ObjectID
22
The system-assigned ID of the object.
ObjectID2
56
The ID of the related object or entity, if available.
ObjectName
34
The name of the object that is referenced.
ObjectType2
28
The value representing the type of the object involved in the event. This value corresponds to the type column in sysobjects.
Offset
61
The starting offset of the statement within the stored procedure or batch.
OwnerID
58
For lock events only. The type of the object that owns a lock.
OwnerName
37
The database user name of the object owner.
ParentName
59
The name of the schema in which the object resides.
Permissions
19
The integer value that represents the type of permissions checked. Values are:
1 = SELECT ALL
2 = UPDATE ALL
4 = REFERENCES ALL
8 = INSERT
16 = DELETE
32 = EXECUTE (procedures only)
4096 = SELECT ANY (at least one column)
8192 = UPDATE ANY
16384 = REFERENCES ANY
ProviderName
46
Name of the OLEDB provider.
Reads
16
The number of read operations on the logical disk that are performed by the server on behalf of the event. These read operations include all reads from tables and buffers during the statement's execution.
RequestID
49
ID of the request that contains the statement.
RoleName
38
The name of the application role that is being enabled.
RowCounts
48
The number of rows in the batch.
ServerName1
26
The name of the instance of SQL Server that is being traced.
SessionLoginName
64
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.
Severity
20
The severity level of the exception event.
SourceDatabaseID
62
The ID of the database in which the source of the object exists.
SPID
12
The server process ID (SPID) that is assigned by SQL Server to the process that is associated with the client.
SqlHandle
63
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.
StartTime1
14
The time at which the event started, when available.
State
30
Error state code.
Success
23
Represents whether the event was successful. Values include:
1 = Success.
0 = Failure
For example, a 1 means a successful permissions check, and a 0 means a failed check.
TargetLoginName
42
For actions that target a login, the name of the targeted login; for example, to add a new login.
TargetLoginSid
43
For actions that target a login, the SID of the targeted login; for example, to add a new login.
TargetUserName
39
For actions that target a database user, the name of that user; for example, to grant permission to a user.
TextData
1
The text value dependent on the event class that is captured in the trace. However, if you trace a parameterized query, the variables are not displayed with data values in the TextData column.
Transaction ID
4
The system-assigned ID of the transaction.
Type
57
The integer value dependent on the event class captured in the trace.
Writes
17
The number of physical disk write operations that are performed by the server on behalf of the event.
XactSequence
50
A token to describe the current transaction.
1 These data columns are populated by default for all events.
2 For more information about the ObjectType data column, see ObjectType Trace Event Column.
See Also
Reference
SQL Server Event Class Reference