Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Reverse Engineering a Server Side Trace Expand / Collapse
Author
Message
Posted Tuesday, March 23, 2010 12:02 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 7:53 PM
Points: 12,896, Visits: 32,095
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
Posted Tuesday, March 23, 2010 1:08 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 9:11 PM
Points: 17,601, Visits: 15,465
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


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
Posted Tuesday, March 23, 2010 3:04 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 3:42 AM
Points: 284, Visits: 1,072
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
Posted Tuesday, March 23, 2010 6:11 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 7:53 PM
Points: 12,896, Visits: 32,095
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 »

Add to briefcase

Permissions Expand / Collapse