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
Mohit K. Gupta
Mohit K. Gupta
SSCertifiable
SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)

Group: General Forum Members
Points: 5380 Visits: 1089
I found the default trace a long while back while wondering around in SQL Install files. As soon as I noticed it I had the questions like performance, who set it and such. So I went digging around and was quite pleased with this little trick of Microsoft's BigGrin. It has saved me more the once in various environment when someone asked when something was modified. But what I wanted to know was Adam is there a way to add events to Default Trace? As you said and most probably noticed it doesn't contain all the event. And adding all the events probably is a bad idea because it will grow the log files; but selected few events. Also how can I make it so it saves more then 5 roll-over files?

Thanks.

- Mohit.

---

Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN.
Microsoft FTE - SQL Server PFE

* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. Smooooth


How to ask for help .. Read Best Practices here.
dparmender
dparmender
SSC-Enthusiastic
SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)

Group: General Forum Members
Points: 109 Visits: 23
Good one
GilaMonster
GilaMonster
SSC Guru
SSC Guru (414K reputation)SSC Guru (414K reputation)SSC Guru (414K reputation)SSC Guru (414K reputation)SSC Guru (414K reputation)SSC Guru (414K reputation)SSC Guru (414K reputation)SSC Guru (414K reputation)

Group: General Forum Members
Points: 414730 Visits: 47139
Mohit (11/11/2008)
But what I wanted to know was Adam is there a way to add events to Default Trace? Also how can I make it so it saves more then 5 roll-over files?


As far as I know, you can't change the default trace's definition. The events, columns and number of files is hardcoded somewhere. If you want more events or more files, disable the default trace and set up your own one.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Mohit K. Gupta
Mohit K. Gupta
SSCertifiable
SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)

Group: General Forum Members
Points: 5380 Visits: 1089
Thanks Gail.

That what I ended up doing; just wanted to make sure I wasn't missing something Smile.

- Mohit.

---

Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN.
Microsoft FTE - SQL Server PFE

* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. Smooooth


How to ask for help .. Read Best Practices here.
David Dye
David Dye
SSC-Enthusiastic
SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)

Group: General Forum Members
Points: 183 Visits: 308
Adam,
GREAT WORK!! Very concise and informative. Thanks!
David Dye
David Dye
SSC-Enthusiastic
SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)

Group: General Forum Members
Points: 183 Visits: 308
Adam,
GREAT WORK!! Very concise and informative. Thanks!

David Dye
Marcia Q
Marcia Q
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1389 Visits: 700
Thanks for posting this article - I didn't know this trace was out there.

Does anyone have any suggestions as to why my default trace doesn't show up?

I'm a sysadmin...

When I run:
SELECT * FROM sys.configurations WHERE configuration_id = 1568

I get this:
configuration_id name value minimum maximum value_in_use description .
is_dynamic is_advanced
--------------------------------------------------------------------------------
1568 default trace enabled 1 0 1 1 Enable or disable the default trace 1 1

But when I SELECT * FROM ::fn_trace_getinfo(0) OR select * from sys.traces
I get no results.

I see some old trace files named log_625.trc, log-626.trc, log_627.trc, log_628.trc but they're from a two-day time period back in October.

I'm confused...

again...
Brian Strickland-453869
Brian Strickland-453869
SSC Rookie
SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)

Group: General Forum Members
Points: 46 Visits: 13
I think this is a very cool tool. My only problem is that on all our servers, the most recent trc file is not log.trc, it is something like log_211.trc and dates back two weeks. I have verified that the default trace is on, but it doesn't seem to be logging all the time.

Any ideas?
Mohit K. Gupta
Mohit K. Gupta
SSCertifiable
SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)

Group: General Forum Members
Points: 5380 Visits: 1089
Marcia,

Maybe someone turned of the trace, I know when I first discovered it I was like who turned this on and turned it off without realizing its use. Try turning it off in the optins and turning it on to see if brings it back. Mind you mine came on the next day without me doing anything.

Brian,

The trace doesn't long all the events if you open one of the trace files you can see the events it logs what events are being logged.

Now with Log Growth, DDL changes you would think something got logged. Maybe yours default trace is turned off also?

- Mohit.

---

Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN.
Microsoft FTE - SQL Server PFE

* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. Smooooth


How to ask for help .. Read Best Practices here.
Adam Haines
Adam Haines
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10540 Visits: 3135
Brian Strickland (11/11/2008)
I think this is a very cool tool. My only problem is that on all our servers, the most recent trc file is not log.trc, it is something like log_211.trc and dates back two weeks. I have verified that the default trace is on, but it doesn't seem to be logging all the time.

Any ideas?

Brian,

log_211.trc is indicative of the current log file being 211. You can still use log.trc to rollup every log file from the very first to the current (211). Your other option is to specify log_211.trc and only get the trace data from log 211 and greater.

Edit:
You can check the sys.configurations table to make sure the trace is still enabled.

Thanks,
Adam



My blog: http://jahaines.blogspot.com
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