Default trace - A Beginner's Guide

Question

Post reply

Default trace - A Beginner's Guide

Viewing 15 posts - 46 through 59 (of 59 total)

You must be logged in to reply to this topic. Login to reply

lorisj33 SSC Eights! Points: 940 More actions · Answer 1

That did the trick!

Duh! I should have looked closer to the where clause.

Thank you,

Lori

Susan Van Eyck SSChasing Mays Points: 632 More actions · Answer 2

This is incredibly cool! I just queried the trace to find the cause of a mysterious nightly error I've been seeing for months! Turns out its from a SQL Agent Job that isn't even scheduled to run (so of course I wasn't looking at it).

Thanks for this very helpful article. It's great to have another tool in my DBA bag of tricks 🙂

Susan

Tergum Rufus Say Hey Kid Points: 681 More actions · Answer 3

Helpful article Adam - thank you.

How can I use this to trace Queries and Sp activity?

Peter

Melbourne, Australia

Jeff Moden SSC Guru Points: 1004321 More actions · Answer 4

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 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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004321 More actions · Answer 5

peter-970097 (9/21/2010)
Helpful article Adam - thank you.
How can I use this to trace Queries and Sp activity?
Peter
Melbourne, Australia

I don't believe you can. The default trace wasn't setup to detect such things. You'll have to setup your own trace for such things.

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jack Corbett SSC Guru Points: 184394 More actions · Answer 6

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
Consultant - Straight Path Solutions
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

Jeff Moden SSC Guru Points: 1004321 More actions · Answer 7

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

gaffar786 Valued Member Points: 61 More actions · Answer 8

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.

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 9

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

Learn to play, play to learn !

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[/url]

- 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

Jack Corbett SSC Guru Points: 184394 More actions · Answer 10

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
Consultant - Straight Path Solutions
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

sibir1us SSC Eights! Points: 828 More actions · Answer 11

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/[/url]

Make everything as simple as possible, but not simpler.
Albert Einstein

sibir1us SSC Eights! Points: 828 More actions · Answer 12

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 SSC-Insane Points: 21661 More actions · Answer 13

sibir1us (5/9/2011)

Here is a nice set of reports on the Default trace: http://sqlconcept.com/default-trace-audit-documentation-and-faq%5B/quote%5D

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 SSC Eights! Points: 828 More actions · Answer 14

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%5B/quote%5D
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

YSLGuru SSC-Insane Points: 21661 More actions · Answer 15

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%5B/quote%5D
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!