Feb 102012

SQL Profiler is a wonderful tool being wide used for system monitoring, performance monitoring, debugging, etc. ( you can count more than I do ;), I better shut up.) It provides tons of events triggered by SQL Server from different areas for various purposes. You are allowed to create customized event for your own by calling sp_trace_generateevent. There is already an example in BOL that you can trace get notified whenever a record is being inserted into a table. It quite useful when you try to centralize the error messages or some other stuff. Well, if your database is serving a busy website where the user management is application type user permission management, you are asked to keep track of every procedure calls with application user tied with, customized event will come to play. This was the one I have done before. Please feel free to tell me your story with customized events by replying my post.

What I have done was that, in client side, I force each procedure call to send a customized event first then run the SQL by re-wrapping SqlCommand Object. While sending customized event, I send user information (actually you can send anything that you want). In my post process, whenever I see the customized event, I combine the data along with this event to the next row of the same SPID, the save the trace to a table. Aha, idea is simple, but post processing is not. I will write few other posts to tell you how to effectively implement the post processing for such scenario.

Back to the main topic, sp_trace_generateevent. Customized event only supports 10 events where EventID is from 82 to 91. Optionally, you can attach information to the event through paraeter @userinfo and @userdata

exec sp_trace_generateevent @eventid = 82 , @userinfo = N'Your information' , @userdata = 0x1234567

Those 2 parameters are both optional. @userinfo will be presented in TextData column in the trace. data in @userdata will be shown in BinaryData column.

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="">



C# | HTML | Plain Text | SQL | XHTML | XML | XSLT |

This site uses Akismet to reduce spam. Learn how your comment data is processed.