SQL Server is a powerful database management system that offers a wide range of features and functionalities. One of the lesser-known features is the ability to create and use user configurable events. In this blog post, we will explore how these events can be used for debugging purposes and provide some examples of how to implement them.

Introduction to sp_trace_generateevent

One of the simplest ways to create user defined events in SQL Server is by using the sp_trace_generateevent procedure. This procedure allows us to raise an event with an event ID between 82 and 91. The usage is straightforward, as shown in the following code snippet:

SELECT 1
GO
EXEC MASTER..sp_trace_generateevent
@event_class = 82, @userinfo = N'This is post SELECT 1 :)';

In the above example, we execute a SELECT statement and then raise an event with an event ID of 82. This can be useful for debugging purposes, as it allows us to track specific points in our code.

Capturing User Configurable Events

Once we have raised a user configurable event, we need a way to capture it. One option is to use SQL Server Profiler. By selecting the appropriate event ID, we can collect the events under the UserConfigurable category. For example, an event with an ID of 82 would be collected under UserConfigurable:0, an event with an ID of 83 would be collected under UserConfigurable:1, and so on.

Another option for capturing user configurable events is to use Extended Events. By creating an event session and specifying the sqlserver.user_event event, we can capture the events and store them in a file. Here is an example of how to create an event session:

CREATE EVENT SESSION [User-Generated-Event] ON SERVER
ADD EVENT sqlserver.user_event
ADD TARGET package0.event_file(SET filename=N'C:\Temp\User-Defined-Event.xel',max_file_size=(20))
WITH (STARTUP_STATE=ON)
GO

Once the event session is created, we can use the Extended Events node to watch live data and capture the user configurable events.

Conclusion

User configurable events in SQL Server are a powerful tool for debugging and monitoring purposes. By raising and capturing these events, developers can gain valuable insights into the execution of their code. Whether using the sp_trace_generateevent procedure or Extended Events, these hidden gems are worth exploring in your SQL Server environments.

Have you ever used user configurable events in your SQL Server environments? Share your experiences and insights in the comments below!