Reverse Engineering a Server Side Trace

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Lowell » Reverse Engineering a Server Side Trace

Reverse Engineering a Server Side Trace

Rate Topic

Display Mode

Topic Options

Author

Message

Lowell

Posted Tuesday, March 23, 2010 12:02 AM

SSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:24 PM
Points: 11,605, Visits: 27,649

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

Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son

Post #887852

SQLRNNR

Posted Tuesday, March 23, 2010 1:08 AM

SSCoach

Group: General Forum Members
Last Login: Yesterday @ 6:30 PM
Points: 18,733, Visits: 12,330

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 2008

SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp

Post #887874

Chris Howarth-536003

Posted Tuesday, March 23, 2010 3:04 AM

SSC Veteran

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 3:01 AM
Points: 266, Visits: 1,023

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

Post #887911

Lowell

Posted Tuesday, March 23, 2010 6:11 AM

SSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:24 PM
Points: 11,605, Visits: 27,649

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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son

Post #888016

« Prev Topic | Next Topic »

Permissions