Using the SQL Server Profiler

In SQL Server 2000/2005, the Profiler tool includes 10 user-definable events that you can track. In other words, you can create events from within your applications that can be traced using Profiler. This could come in handy for troubleshooting. You can return any data you want in a user-definable event, so this option permits a lot of possibilities.

The first step when creating a user-definable event is to create the event in your application, script, or stored procedure using the sp_trace_generateevent system stored procedure. Here’s the syntax for implementing this stored procedure:

sp_trace_generateevent [ @eventid = ] event_id
[ , [ @userinfo = ] ‘user_info’ ]
[ , [ @userdata = ] user_data ]


[ @eventid = ] event_id: Is the ID of the event to use. The event_id is INT, with no default. The ID must be an event number from 82 through 91, which represents the user-defined events as set with sp_trace_setevent system stored procedure. Here is how these event numbers correlate to the user-definable events in Profiler:

82 = user-definable Profiler Trace Event 0
83 = user-definable Profiler Trace Event 1
84 = user-definable Profiler Trace Event 2
85 = user-definable Profiler Trace Event 3
86 = user-definable Profiler Trace Event 4
87 = user-definable Profiler Trace Event 5
88 = user-definable Profiler Trace Event 6
89 = user-definable Profiler Trace Event 7
90 = user-definable Profiler Trace Event 8
91 = user-definable Profiler Trace Event 9

So if you want to raise user-definable Profiler Trace Event 0 in Profiler, then you would use event_id 82 within the sp_trace_generateevent stored procedure.

[ @userinfo = ] ‘user_info’: Is used for an optional user-defined string for identifying the reason for the event. user_info is NVARCHAR(128), with a default of NULL.

[ @userdata = ] user_data: Is used for an optional user-specified data for the event. user_data is VARBINARY(8000), with a default of NULL.

Besides incorporating the sp_trace_generateevent system stored procedure in your application, script, or stored procedure, it can also be made part of a trigger. This way, when the trigger fires, the event files, and Profiler can trace the event.

Once you have set up sp_trace_generateevent, you are now ready to begin to use Profiler to trace the events. Here’s how.

First, start a Profiler trace as you normally would. To trace user-definable events, you must first go to the Trace Properties window’s Events Tab. From here, under Available Event Classes, open up the User Configurable Event Class. Here, you will see the 10 available user-configurable events, starting with 0 and ending with 9. Select the one or more user-configurable events from the Available Event Class and move them to the Selected Event Classes.

If your user-configurable event included any text data or binary data, this can be captured by selecting the Text Data or Binary Data Data Columns from the Data Columns Tab in the Trace Properties screen.

And then you use Profiler as usual. If any user-definable events occur, and they have text or binary data, this will be captured by Profiler. [2000, 2005] Updated 5-1-2006


Leave a comment

Your email address will not be published.