Export (0) Print
Expand All
This topic has not yet been rated - Rate this topic

Limiting Traces

SQL Server 2000

Limiting Traces

If a filter is not set, all events of the selected event classes are returned in the trace output. Filters limit the events collected in the trace. For example, limiting the Microsoft® Windows NT® 4.0 or Windows® 2000 user names in the trace to specific users reduces the output data to only those users in which you are interested.

Trace event criteria are parameters used to restrict (filter) the event data captured within the trace. For example, you can monitor the activity of a specific application or exclude an application from monitoring (the default trace event criteria excludes SQL Profiler from monitoring itself). For example, when monitoring queries to determine the batches that take the longest time to execute, you can set the trace event criteria to monitor (trace) only those batches that take longer than 30 seconds to execute (a CPU minimum value of 30,000 milliseconds). You can specify filters by right-clicking on the appropriate trace event criteria value and entering the information.

To filter events in a trace template

Profiler

Transact-SQL

To modify filters

Profiler

Each trace event criteria parameter has a set of values that determine whether the event data is included in the trace when the event class is monitored by SQL Profiler. The values applicable depend on the event criteria chosen.

Event criteria option Description
Like Specifies that the trace event data must be like the text entered. Allows multiple values.
Not like Specifies that the trace event data must not be like the text entered. Allows multiple values.
Equals Specifies that the trace event data must be equal to the value entered. Allows multiple values.
Not equal to Specifies that the trace event data must not be equal to the value entered. Allows multiple values.
Greater than or equal Specifies that the trace event data must be greater than or equal to the value entered.
Less than or equal Specifies that the trace event data must be less than or equal to the value entered.

The following table lists the trace event criteria (filters), and the options available for each.


Trace event criteria


Description


Like

Not like


Equals
Not equal to Greater than or equal
Less than or equal
Application Name Application that generates the event, for example, Query Analyzer. Yes Yes        
Client ProcessID The process ID of the application calling Microsoft® SQL Server™.     Yes Yes Yes Yes
Column Permissions Indicator of whether a column permission was set.     Yes Yes Yes Yes
CPU Amount of CPU time (in milliseconds).     Yes Yes Yes Yes
Database ID ID assigned to the database. The value for a database can be determined by using the DB_ID function.     Yes Yes Yes Yes
Database Name Name of the database in which the statement of the user is running. Yes Yes        
Duration Amount of elapsed time for remote procedure calls (RPCs) and language statements, locks, sessions, and other events that have a defined elapsed time.     Yes Yes Yes Yes
End Time Time at which the event ended. This column is not populated for starting event classes, such as SQL:BatchStarting or SP:Starting.         Yes Yes
Error Error number of a given event. Often this is the error number stored in sysmessages.     Yes Yes Yes Yes
File Name The logical name of the file being modified. Yes Yes        
Handle Integer used by ODBC, OLEDB, or DB-Library to coordinate execution with the server.     Yes Yes Yes Yes
Host Name Name of the computer that generates the event. To determine the host name, use the HOST_NAME function. Yes Yes        
Index ID Index ID for the object. To determine the index ID for an object, use the indid column of the sysindexes system table.     Yes Yes Yes Yes
Login Name Name of the login of the user (either SQL Server security login or the Windows login credentials in the form of DOMAIN\Username). Yes Yes        
Mode Integer used by various events to describe a state the event has received or is requesting.     Yes Yes Yes Yes
NT Domain Name Windows NT or Windows 2000 domain of the client that generates the event. Yes Yes        
NT User Name Individual responsible for generating the event. Yes Yes        
Object ID Unique ID for the monitored object. Alternatively, by selecting the Exclude system objects check box, all objects are monitored, except system objects such as the sysobjects table.     Yes Yes Yes Yes
Object Name The name of the object being referenced. Yes Yes        
Object Type Value representing the type of the object involved in the event. This value corresponds to the type column in sysobjects.     Yes Yes Yes Yes
Owner Name Database user name of the object owner. Yes Yes        
Permissions Integer value representing 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

    Yes Yes Yes Yes
Reads Number of logical reads performed by the server executing the statement.     Yes Yes Yes Yes
Role Name Name of an application role being enabled. Yes Yes        
Severity Range of error severity levels.     Yes Yes Yes Yes
SPID Each connection has a unique SPID.     Yes Yes Yes Yes
DBUserName User who generates the event. Yes Yes        
Start Time Time at which the event started, when available.         Yes Yes
State Equivalent to an error state code.     Yes Yes Yes Yes
Success 1 = success.
0 = failure (for example, a 1 means success of a permissions check and a 2 means a failure of that check).
    Yes Yes Yes Yes
Target Login Name For actions which target a login (for instance, adding a new login), the name of the targeted login. Yes Yes        
Target User Name For actions which target a database user (for instance, granting permission to a user), the name of that user. Yes Yes        
Text Data Text contained within the event data. Yes Yes        
Writes Number of disk writes performed by the server executing the statement.     Yes Yes Yes Yes

As a security mechanism, SQL Profiler automatically omits from the trace any of the security-related stored procedures affecting passwords. This security mechanism is nonconfigurable and always in effect, preventing users, who otherwise have permissions to trace all activity on SQL Server, from capturing passwords.

The following security-related stored procedures are not monitored:

See Also

sp_addapprole

sp_adddistpublisher

sp_adddistributiondb

sp_adddistributor

sp_addlinkedserver

sp_addlinkedsrvlogin

sp_addlogin

sp_addmergepullsubscription_agent

sp_addpullsubscription_agent

sp_addremotelogin

sp_addsubscriber

sp_approlepassword

sp_changedistpublisher

sp_changesubscriber

sp_dsninfo

sp_helpsubscription_properties

sp_link_publication

sp_password

sp_setapprole

Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft. All rights reserved.