December 18, 2011 at 9:39 pm
Can we modify SQL Server default trace by ourselves? Such as expand file size.
December 19, 2011 at 3:06 am
No. You can enable it or disable it, nothing else.
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
December 19, 2011 at 8:43 pm
Thanks Gail. I think it is better for us to modify max file size, so it could keep more information. And it shouldn't impact performance.
December 19, 2011 at 8:54 pm
You need to rescript the whole thing and control it yourself. Here's how you can script a trace from the trace definition's tables.
December 20, 2011 at 1:38 am
Two ways you can achieve that.
1) Put a scheduled job in place to copy the older trace files to another location at a regular interval before they can be deleted by SQL. Depending how fast your server turns over the default trace, that could be a daily job or it could be a lot more frequent.
2) Disable the default trace completely and create your own trace that starts when SQL starts (via a startup procedure) or when SQL Agent starts (via a job) and completely reimplement the default trace with a larger file. Note that you'll also have to implement cleanup to remove older files.
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
December 20, 2011 at 2:09 am
Thanks Gail. I already implemented option 1.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply