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 ««12345»»»

Default trace - A Beginner's Guide Expand / Collapse
Author
Message
Posted Tuesday, November 11, 2008 10:22 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: Saturday, August 23, 2014 10:43 AM
Points: 942, Visits: 1,064
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 :D. 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.


How to ask for help .. Read Best Practices here.
Post #600805
Posted Tuesday, November 11, 2008 2:03 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, February 2, 2009 10:07 AM
Points: 81, Visits: 23
Good one
Post #600924
Posted Tuesday, November 11, 2008 2:10 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:41 AM
Points: 40,187, Visits: 36,593
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 2008, MVP
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

Post #600931
Posted Tuesday, November 11, 2008 2:43 PM


SSC Eights!

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

Group: General Forum Members
Last Login: Saturday, August 23, 2014 10:43 AM
Points: 942, Visits: 1,064
Thanks Gail.

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

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


How to ask for help .. Read Best Practices here.
Post #600950
Posted Tuesday, November 11, 2008 2:48 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, October 17, 2014 10:07 AM
Points: 7, Visits: 258
Adam,
GREAT WORK!! Very concise and informative. Thanks!
Post #600953
Posted Tuesday, November 11, 2008 2:49 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, October 17, 2014 10:07 AM
Points: 7, Visits: 258
Adam,
GREAT WORK!! Very concise and informative. Thanks!

David Dye
Post #600955
Posted Tuesday, November 11, 2008 4:03 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 3:14 PM
Points: 365, Visits: 698
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...




Post #600991
Posted Tuesday, November 11, 2008 4:37 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, June 7, 2009 7:42 PM
Points: 2, 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?
Post #601004
Posted Tuesday, November 11, 2008 4:44 PM


SSC Eights!

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

Group: General Forum Members
Last Login: Saturday, August 23, 2014 10:43 AM
Points: 942, Visits: 1,064
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.


How to ask for help .. Read Best Practices here.
Post #601007
Posted Tuesday, November 11, 2008 4:47 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 12:27 AM
Points: 2,278, Visits: 3,061
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
Post #601008
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse