I want some Moore

Blog about stuff and things and stuff. Mostly about SQL server and .Net
posts - 218, comments - 2281, trackbacks - 33

My Links

Advertisement

News

Hi! My name is 
Mladen Prajdić  I'm from Slovenia and I'm currently working as a .Net (C#) and SQL Server developer.

I also speak at local user group meetings and conferences like SQLBits and NT Conference
Welcome to my blog.
SQL Server MVP

My Books

SQL Server MVP Deep Dives 2
The Red Gate Guide to SQL Server Team based Development Free e-book

My Blog Feed via Email
Follow MladenPrajdic on Twitter


Users Online: who's online

Article Categories

Archives

Post Categories

Cool software

Other Blogs

Other stuff

SQL stuff

Custom user configurable SQL Server Profiler events

SQL Server Profiler is a well known tool for tracing different activity that happens between your server and the clients connected to it.

But very few people know that you can have custom events and trace them in the SQL Server Profiler. They can be found under User configurable event group:

ProfilerConfigurableEvents

We can have up to 10 custom events.

Firing these custom events is done by executing sp_trace_generateevent stored procedure which takes event id as the first input parameter. These event ID's span from 82 (UserConfigurable:0) to 91 (UserConfigurable:9)

A simple code example to demonstrate this:

USE AdventureWorks GO CREATE PROCEDURE spDoStuff @AddressID int AS UPDATE Person.Address SET ModifiedDate = GETDATE() WHERE AddressID = @AddressID IF @@ROWCOUNT = 0 BEGIN DECLARE @msg NVARCHAR(128) SELECT @msg = N'No data updated. Rowcount 0. AddressId = ' + CAST(@AddressID AS NVARCHAR(10)) EXEC sp_trace_generateevent 82, @msg END SELECT * FROM Person.Address WHERE AddressID = @AddressID GO -- UPDATES OK EXEC spDoStuff 1 GO -- this id doesn't exist. trace event is executed as shown in the below picture EXEC spDoStuff -22 GO DROP PROCEDURE spDoStuff

ProfilerCutomEvents

The UserConfigurable:0 event was successfully fired as is shown in the profiler trace.

 

These custom events are highly underused and there isn't even much talk about them at all. That is a shame because they can be a very helpful addition to your debugging or general execution tracing. By using them you can easily narrow the profiler trace and with that the load on the server over long periods of time making them ideal for finding seldom occurring problems in production environment.

 

There is one downside to them though. Running sp_trace_generateevent requires ALTER TRACE permissions to run. Most likely the user running the stored procedure doesn't have that permission. Unless you're running under SA of course :)

A workaround for this is to putting it simply create a wrapper stored procedure around the sp_trace_generateevent stored procedure, sign the wrapper with a certificate and allow public access to it.

 

kick it on DotNetKicks.com
 

Print | posted on Thursday, October 16, 2008 10:59 AM | Filed Under [ SQL Server ]

Feedback

Gravatar

# re: Custom user configurable SQL Server Profiler events

"A workaround for this is to putting it simply create a wrapper stored procedure around the sp_trace_generateevent stored procedure, sign the wrapper with a certificate and allow public access to it."

Indeed--and I put an example for that in "Inside SQL Server 2005: Query Tuning and Optimization". The downloads can be found here:

http://insidesqlserver.com/companion/#Optimization

The example stored procedure is in Chapter 2...

Enjoy!
10/16/2008 3:17 PM | Adam Machanic
Gravatar

# re: Custom user configurable SQL Server Profiler events

nice adam! thanx.
i was going to leave that for another blog post but hey...if it's already out there... :))
10/16/2008 3:18 PM | Mladen
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET