November 11, 2008 at 2:49 pm
Adam,
GREAT WORK!! Very concise and informative. Thanks!
David Dye
November 11, 2008 at 4:03 pm
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
--------------------------------------------------------------------------------
1568default trace enabled1011 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...
November 11, 2008 at 4:37 pm
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?
November 11, 2008 at 4:44 pm
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[/url].
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:[/font]
November 11, 2008 at 4:47 pm
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
November 11, 2008 at 4:53 pm
Forgive me for being unclear, but our servers don't have a log.trc file. There are a number of other trc files with naming conventions like log_xxx.trc. On one of our servers, the latest trace file is from 10/30/2008 and is named log_211.trc.
November 11, 2008 at 4:59 pm
Brian Strickland (11/11/2008)
Forgive me for being unclear, but our servers don't have a log.trc file. There are a number of other trc files with naming conventions like log_xxx.trc. On one of our servers, the latest trace file is from 10/30/2008 and is named log_211.trc.
Create some objects and look for them in the 211 trace file. This way you can verify if the trace is working or not. If not, disable then enable it.
November 11, 2008 at 4:59 pm
Marcia Q (11/11/2008)
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
--------------------------------------------------------------------------------
1568default trace enabled1011 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...
I would try sp_reconfigure to disable it and then use sp_configure to enable it back.
November 11, 2008 at 5:14 pm
Adam,
Oustanding article; gives what users really want! Interesting question though, any thoughts as to why one may have Default trace data going back only a few days even though the option for enabling/disabling the Default trace has never been executed since SQL 2005 first went on line for the user a year ago? I've been with the company for less then a year and am still figuring out whare everything is so many things like the cool stuff have to take a back seat.
I checked and the Default Trace option was not disabled but for some reason the oldest trace info for it is only a few days back. Any thoughts?
Thanks
Kindest Regards,
Just say No to Facebook!November 11, 2008 at 5:38 pm
YSLGuru (11/11/2008)
Adam,Oustanding article; gives what users really want! Interesting question though, any thoughts as to why one may have Default trace data going back only a few days even though the option for enabling/disabling the Default trace has never been executed since SQL 2005 first went on line for the user a year ago? I've been with the company for less then a year and am still figuring out whare everything is so many things like the cool stuff have to take a back seat.
I checked and the Default Trace option was not disabled but for some reason the oldest trace info for it is only a few days back. Any thoughts?
Thanks
Are you using the earliest file in the folder path where the trace logs are created? The function I used in the article to query the trace does a rollup of all files from the initial one to current. For example, say you have log.trace, log1.trc, log2.trc, and log3.trc. If you specify log3.trc you only get the data that is contained in log3.trc, which may or may not have a lot of data. It depends on how active your server is. If you were to choose log2.trc, you will get the contents of log2.trc and log3.trc. Now if you choose log.trc you get the contents of all the trace files up to the current trace file. This is the rollup feature I was speaking of.
November 11, 2008 at 8:19 pm
I have tried listing each trc file in the query and still noth before a few days back.
Thanks
Kindest Regards,
Just say No to Facebook!November 11, 2008 at 10:17 pm
Sounds like you have a very active server. The default trace will only maintain 5 trace files at a time. I believe your issue is that you can only fit a few days worth of events into 5 trace files. It should also be noted that every time SQL restarts a new trace file is created. This behavior can nip you in the butt because introducing a new file will release the eldest file. At which point you will have 3 complete trace files, 1 partially complete trace file and 1 new/empty trace file.
If you wanted to maintain complete history, you can have a sql job that inserts the trace data into a local table daily. At which point you have history of all captured events. Another option would be to setup DDL triggers to handle server/database level DDL events.
November 12, 2008 at 4:49 pm
The sys.traces view has a "max_files" item with a value of 5.
Is it possible for this value to be changed to allow more/less trace files?
S.
November 12, 2008 at 5:02 pm
Simon Villiers (11/12/2008)
The sys.traces view has a "max_files" item with a value of 5.Is it possible for this value to be changed to allow more/less trace files?
S.
Hi Simon,
I was in training with a Microsoft SQL Engineer today; and asked him that exact same question and asked him about altering the default trace events. He said you *might* be able to do it, but if you do it will not be supported by Microsoft. Now he didn't make it clear to me was he referring to only SQL Server or just Default Trace. Suggestion he gave was, some people here have given is to backup the files on regular intervals. As those files total 100MB only, about 20MB each file. So if you server has alot of activity have a script copy the files every x hours, and make sure when you are about to restart SQL Server you stop the SQL Server and copy the files. Because restarting the SQL Server forces it to make a new trace file in default trace.
Thanks.
- Mohit.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
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:[/font]
November 13, 2008 at 1:37 pm
Adam Haines (11/11/2008)
Sounds like you have a very active server. The default trace will only maintain 5 trace files at a time. I believe your issue is that you can only fit a few days worth of events into 5 trace files. It should also be noted that every time SQL restarts a new trace file is created. This behavior can nip you in the butt because introducing a new file will release the eldest file. At which point you will have 3 complete trace files, 1 partially complete trace file and 1 new/empty trace file.If you wanted to maintain complete history, you can have a sql job that inserts the trace data into a local table daily. At which point you have history of all captured events. Another option would be to setup DDL triggers to handle server/database level DDL events.
We do have a very busy box. It services 300-400 users via third party wen based accountig application. Our DB is around 120GB so we are getting up there in over all size.
So to verify/recap, the default action for SQL Server is to start a new trace file each time it's rebooted and it keeps at max 3 trc files. That sound about right?
If yes then I like your idea about capturing the data. We do reboot every server each night simply because our third party software has far less issues with daily re-boots. So we can with fare ease, schedule a dump of the trace data each night.
Thanks
Kindest Regards,
Just say No to Facebook!Viewing 15 posts - 16 through 30 (of 59 total)
You must be logged in to reply to this topic. Login to reply