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 «««23456

Default trace - A Beginner's Guide Expand / Collapse
Author
Message
Posted Thursday, December 2, 2010 1:17 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 6:00 PM
Points: 10,268, Visits: 13,245
Jeff Moden (12/2/2010)
I recently had cause to revisit this fine article... well done, Adam. Great for neophytes and alumni alike.

As a side bar, here's the code I ended up using which could actually be turned into an iTVF...
 SELECT trc.LoginName,
-- trc.LoginSID,
trc.SPID,
trc.IsSystem,
trc.HostName,
trc.ApplicationName,
trc.ServerName,
trc.DatabaseName,
trc.ObjectName,
evt.Category_ID,
CategoryName = cat.name,
trc.TextData,
trc.StartTime,
trc.EventClass,
trc.EventSubClass,
EventName = evt.name
FROM FN_TRACE_GETTABLE( (
SELECT CAST([Value] AS NVARCHAR(4000)) --Drive, path, and filename of trace file
FROM FN_TRACE_GETINFO(0) --0 = Current Server
WHERE [Property] = 2 --2 = Trace File Path
)
,0) trc --0 = Latest default trace file
INNER JOIN sys.Trace_Events evt ON eventclass = trace_event_id
INNER JOIN sys.Trace_Categories cat ON evt.category_id = cat.category_id
WHERE trc.DatabaseName = 'somedatabasenamehere'
AND trc.ObjectName = 'somesqlserverobjectnamehere'
;


Heh... and, no... I don't code for things to work on case sensitive servers.


Jeff,

I prefer to use sys.traces over fn_trace_getinfo and then you can do an cross apply on fn_Trace_gettable. Like this:

sys.traces T CROSS APPLY
sys.fn_trace_gettable(CASE WHEN CHARINDEX('_', T.[path]) <> 0
THEN SUBSTRING(T.PATH, 1, CHARINDEX('_', T.[path]) - 1) + '.trc'
ELSE T.[path]
End, T.max_files) TRC





Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #1029505
Posted Thursday, December 2, 2010 10:11 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:08 AM
Points: 35,366, Visits: 31,901
Thanks for the tip, Jack.

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1029637
Posted Saturday, December 18, 2010 4:56 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 12, 2011 11:56 AM
Points: 1, Visits: 33
Trace ID '3' was stopped because of an error. Cause: Stopping the trace because the current trace file is full and the rollover option is not specified.. Restart the trace after correcting the problem.

i am getting this error on SQL Server, There is default error message enabled on SQL.
How to specifiy the roll over option on default trace.
if i open any default trace files, i am unable to view any roll option on SQL Server profiles studio.

Any idea would be appreciated.

Post #1036932
Posted Sunday, December 19, 2010 3:39 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, October 9, 2014 10:42 AM
Points: 6,731, Visits: 8,480
Default trace typicaly has trace id 1.

Check sys.traces to figure out what kind of trace that is, what file it tries to write to, and check if you can correct that problem


Johan


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

- How to post Performance Problems
- How to post data/code to get the best help


- How to prevent a sore throat after hours of presenting ppt ?


"press F1 for solution", "press shift+F1 for urgent solution"


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me
Post #1036957
Posted Monday, December 20, 2010 8:09 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 6:00 PM
Points: 10,268, Visits: 13,245
gaffar786 (12/18/2010)
Trace ID '3' was stopped because of an error. Cause: Stopping the trace because the current trace file is full and the rollover option is not specified.. Restart the trace after correcting the problem.

i am getting this error on SQL Server, There is default error message enabled on SQL.
How to specifiy the roll over option on default trace.
if i open any default trace files, i am unable to view any roll option on SQL Server profiles studio.

Any idea would be appreciated.



You can't modify the default trace beyond stopping it using sp_configure. As ALZDBA mentioned the default trace is usually trace id 1 in sys.traces, but a better way to tell is to check the is_default column for the trace. I've never seen the Default Trace anything but 1, but if you have common criteria or C2 auditing enabled it might get a different id.




Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #1037216
Posted Tuesday, March 15, 2011 7:34 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, May 20, 2014 2:22 AM
Points: 22, Visits: 292
Here is a more detailed article about the Default trace: http://www.simple-talk.com/sql/performance/the-default-trace-in-sql-server---the-power-of-performance-and-security-auditing/

Make everything as simple as possible, but not simpler.
Albert Einstein
Post #1078334
Posted Monday, May 9, 2011 10:49 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, May 20, 2014 2:22 AM
Points: 22, Visits: 292
Here is a nice set of reports on the Default trace: http://sqlconcept.com/default-trace-audit-documentation-and-faq

Make everything as simple as possible, but not simpler.
Albert Einstein
Post #1105841
Posted Tuesday, May 10, 2011 10:54 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 9:15 AM
Points: 891, Visits: 1,553
sibir1us (5/9/2011)
Here is a nice set of reports on the Default trace: http://sqlconcept.com/default-trace-audit-documentation-and-faq


Too bad you have to sign up for yet one more site in order to review/use the item detailed in the post. Maybe its just me but its relaly annoying to be required to sign up for yet one more sites list just to read/review something teh author wants to actualy share with others.


Kindest Regards,

Just say No to Facebook!
Post #1106353
Posted Tuesday, May 10, 2011 11:28 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, May 20, 2014 2:22 AM
Points: 22, Visits: 292
YSLGuru (5/10/2011)
sibir1us (5/9/2011)
Here is a nice set of reports on the Default trace: http://sqlconcept.com/default-trace-audit-documentation-and-faq


Too bad you have to sign up for yet one more site in order to review/use the item detailed in the post. Maybe its just me but its relaly annoying to be required to sign up for yet one more sites list just to read/review something teh author wants to actualy share with others.


It is worth it, trust me. :)


Make everything as simple as possible, but not simpler.
Albert Einstein
Post #1106398
Posted Tuesday, May 10, 2011 11:43 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 9:15 AM
Points: 891, Visits: 1,553
sibir1us (5/10/2011)
YSLGuru (5/10/2011)
sibir1us (5/9/2011)
Here is a nice set of reports on the Default trace: http://sqlconcept.com/default-trace-audit-documentation-and-faq


Too bad you have to sign up for yet one more site in order to review/use the item detailed in the post. Maybe its just me but its really annoying to be required to sign up for yet one more sites list just to read/review something the author wants to actually share with others.


It is worth it, trust me. :)


It very well maybe but the point is you don't know until you've gone thru the sign up process. Its one thing to sign up at a site you plan to visit regularly like this one but if you have to register just to review/read something advertised on another site I think thats asking too much in this day and age. Then again maybe most foilks like collecting site registrations like some collect stamps and its just me who prefers not to have to register every time.

I did get a post from what I assume is the site owner saying the registration was to discourage those who only wanted to review the default trace file and that they did not have time to spam the emails of those who registered. Not sure where the user read anything about being accused of spamming however its their site so they can put in place what ever restrictions they want.


Kindest Regards,

Just say No to Facebook!
Post #1106413
« Prev Topic | Next Topic »

Add to briefcase «««23456

Permissions Expand / Collapse