Reverse Engineering a Server Side Trace

• Lowell

  SSC Guru

  Points: 323527

  More actions

  March 23, 2010 at 12:02 am

  #218081

  Comments posted to this topic are about the item Reverse Engineering a Server Side Trace

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• SQLRNNR

  SSC Guru

  Points: 281344

  More actions

  March 23, 2010 at 1:08 am

  #1138002

  Excellent topic and excellent article.

  Thanks Lowell. This is very useful stuff to have in the tool-belt.

  Jason...AKA CirqueDeSQLeil
  _______________________________________________
  I have given a name to my pain...MCM SQL Server, MVP
  SQL RNNR
  Posting Performance Based Questions - Gail Shaw[/url]
  Learn Extended Events

• Chris Howarth-536003

  SSCommitted

  Points: 1775

  More actions

  March 23, 2010 at 3:04 am

  #1138035

  Great article, this will be very useful.

  I spotted a typo on line 65 of your stored proc:

  from ::fn_trace_geteventinfo(1) AS X

  ...should be:

  from ::fn_trace_geteventinfo(@traceid) AS X

  Cheers

  Chris

• Lowell

  SSC Guru

  Points: 323527

  More actions

  March 23, 2010 at 6:11 am

  #1138125

  Thank you for the salute Jason and Thanks for identifying the fix, Chris;

  Something that might escape some folks is did you know that C2 auditing is just another version of a trace?

  a lot of people think it's one of those black box/magical complex things only super dba's know, and it's all requirement for SOX compliance.

  fortunately, a trace is a trace...it's just 1100 lines of events it is going to capture, compared to ~770 for the default trace.

  As an example, here are a couple of traces I ran off of my SLQ2008 server; one is the trace created by C2 Auditing, and the other is one created from the default trace.

  c2_audit_trace_script.txt

  default_trace_script.txt

  I just love looking at the details i guess.

  To do it yourself, it really easy to enable the C2 Auditing to test this: it's in the SSMS, you simply right click on your server, select properties, and there is a check box on the security tab.

  The C2 Audit requires a start and stop of the SQL Server to get it in place, and it becomes traceid 1, with the default trace = traceid 2; I had really grown accustomed to the default trace being traceid = 1, so it was another thing to learn on this one that it could move around.

  here is a link to download the version whitht he correction that Chris identified:

  sp_ScriptAnyTrace.txt

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!