default trace configurations

Question

default trace configurations

balasaukri

Default port

Points: 1443
More actions
July 5, 2013 at 6:10 am

#296065

Hi,
Just curiosity to know... there will be 5 trace files with each 20 MB size for default trace. Can it be increased to more number of files to preserve, as well as is it possible to increase the size from 20 MB to more?
Please let me know.
Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Not possible. You cannot in any way configure any of the attributes of the default trace.

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

Lowell SSC Guru Points: 323518 More actions · Answer 2

what you can do, however, is create the same trace as the default trace, and modify it from there.

an additional caveat: As far as I know,

a user created trace gets stopped whenever the SQL service is stopped, so to get it to restart, you need to create a procedure that creates the trace, and make that procedure a startup procedure via sp_procoption.

here's a scripted out copy of my default trace, for example:

default_trace_script.txt

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

balasaukri Default port Points: 1443 More actions · Answer 3

balasaukri

Default port

Points: 1443

July 5, 2013 at 8:35 am

#1630099

Thank you Gail!

balasaukri Default port Points: 1443 More actions · Answer 4

Lowell - Thank you so much for providing more light.

kevaburg SSCoach Points: 18131 More actions · Answer 5

Just a little something from my own experience:

The default trace file as has been mentioned, cannot be amended but that said, it shouldn't really be a trace that is used because of the enormous amount of data that it collects. The trace file generated should contain enough information to pinpoint an issue and not soooo much data that working through the file requires an inordinate amount of work. And it should run over the period of time that a problem occurs and not over an extended period.

To that end, I am assuming you are using the Profiler to generate the trace and not using server-side tracing?

It might be worth having a look at the two side-by-side and measuring the performance gains of a server-side trace over the profiler, especially if it is planned to use the utility over a longer period of time. Although the Profiler is without a doubt easier to use, it can also bring a server to a standstill if you are not careful!