Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Default trace - A Beginner's Guide


Default trace - A Beginner's Guide

Author
Message
Jack Corbett
  Jack Corbett
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11014 Visits: 14858
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. :-P


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
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45022 Visits: 39887
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
gaffar786
gaffar786
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
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.
ALZDBA
ALZDBA
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 6972 Visits: 8839
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 w00t

- 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" :-D


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me Alien but most of the time this is me Hehe
Jack Corbett
  Jack Corbett
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11014 Visits: 14858
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
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
sibir1us
sibir1us
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 Visits: 321
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
sibir1us
sibir1us
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 Visits: 321
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
YSLGuru
YSLGuru
SSC Eights!
SSC Eights! (959 reputation)SSC Eights! (959 reputation)SSC Eights! (959 reputation)SSC Eights! (959 reputation)SSC Eights! (959 reputation)SSC Eights! (959 reputation)SSC Eights! (959 reputation)SSC Eights! (959 reputation)

Group: General Forum Members
Points: 959 Visits: 1659
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!
sibir1us
sibir1us
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 Visits: 321
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. Smile

Make everything as simple as possible, but not simpler.
Albert Einstein
YSLGuru
YSLGuru
SSC Eights!
SSC Eights! (959 reputation)SSC Eights! (959 reputation)SSC Eights! (959 reputation)SSC Eights! (959 reputation)SSC Eights! (959 reputation)SSC Eights! (959 reputation)SSC Eights! (959 reputation)SSC Eights! (959 reputation)

Group: General Forum Members
Points: 959 Visits: 1659
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. Smile


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!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search