SQL Clone
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
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: 45221 Visits: 14925
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 Guru
SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)

Group: General Forum Members
Points: 217751 Visits: 41995
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
gaffar786
gaffar786
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 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
SSC-Dedicated
SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)

Group: General Forum Members
Points: 30269 Visits: 8986
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


Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont 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
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: 45221 Visits: 14925
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 Veteran
SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)

Group: General Forum Members
Points: 228 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 Veteran
SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)

Group: General Forum Members
Points: 228 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
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4506 Visits: 1667
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 Veteran
SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)

Group: General Forum Members
Points: 228 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
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4506 Visits: 1667
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