Post reply

Extend size of default trace to be more than 100 MB, Is it possible?

  • February 10, 2014 at 9:25 am

    #288005

    Hi All,

    My SQL 2008 R2 default trace can trace back to only 2-3 months, I really need it to capture for say 6-8 months.

    I read some forum that it's size can extend to only 100 MB.

    Anyone know how to extended it to say 300-500 MB?

    So I can run report for that amount of time I need. For Schema change history really help me to solve the issue for who drop the table and so on ....

    Thank you in advance.

    JJ

  • February 10, 2014 at 9:41 am

    #1687598

    you cannot change the default trace, but you can create your own trace, with the exact same settings, and have it use more rollover files, larger files, etc.

    another issue to consider is traces get dropped on server restarts, so you need to add a procedure, with the proc option to execute on server startup, that re-creates your trace for you;

    things like reindex can bloat your default trace, remember; you could easily bloat up a couple of terabytes or more by logging EVERYTHING for six months, even though you are only tracking DDL stuff.

    here's a link to a stored procedure i made that script out any trace, makes it more readable and understandable, so you can modify it to fit your needs.

    sp_ScriptAnyTrace.txt

    so you could run exec sp_scriptAnyTrace 1 --The default trace, and then modify the paramters to match your needs.

    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!

  • February 10, 2014 at 5:14 pm

    #1687731

    If you do want the equivalent of the default trace, but you want to control it more directly, I'd suggest taking a look at extended events. You can have them start with the server just by setting a property on the session. You don't have to create startup triggers and all the rest. Also, check out the system_health session that runs automatically. That's the replacement for the default trace.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

Viewing 3 posts - 1 through 3 (of 3 total)

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