Post reply

Log File Advice

  • January 4, 2013 at 3:30 am

    #268453

    Hi,

    I'm looking to get some advice around one of my log files which increases greatly over a one hour period at night.

    I have a database that is used (not heavily) durring the day to collect statistics. I run log file backups every hour on the database from 7am to 8pm (at which point my maintenance kicks in).

    At 12 midnight the application kicks off a process that creates a 65GB log file.

    My question is would it be better to set another schedule up that runs, say every 10 mins, for this hour or so OR expand my disk space and leave the log file as a 65GB working log file that is only needed over night? Or Does it not make much difference either way?

    P.S. I'm not sure if the process is a single transaction or not! (which I guess would have some bearing on it!)

    I hope this makes sence.

    Thanks

  • January 4, 2013 at 3:44 am

    #1573656

    Can you detail what the process is doing at midnight to cause the 65GB log file?

    Managing Transaction Logs - http://www.sqlservercentral.com/articles/Administration/64582/

    Stairway to Transaction Log Management in SQL Server, Level 7: Dealing with Excessive Log Growth - http://www.sqlservercentral.com/articles/Stairway+Series/94579/

  • January 4, 2013 at 3:56 am

    #1573663

    I'm not 100% sure but I'm pretty sure it is creating statistics. I'm sorry I can't be more specific..

    Thanks

  • January 4, 2013 at 4:06 am

    #1573672

    In that case I would stick a server side trace on to run for an hour when the log grows to see what is happening.

    You could have multiple things happening which are contributing to the increased log growth.

    Once you know whats running you can then tune your processes to do things at different times if possible.

    For now I would personally change the schedule for the hour to every X amount of minutes to keep the log file smaller and a bit easier to manage, then once you know what is happening and if things can be staggered can look at changing the frequency back to your default 1 hour backup.

  • January 4, 2013 at 4:10 am

    #1573673

    I wouldn't stop the log backups over night. Maintenance routines will add to the transaction log and you've got a data load running that's absolutely adding to the transaction log. Keep running those backups.

    And yeah, hourly backups seem a little light. How much data is the business prepared to lose? Yeah, I know, zero. But realistically how much? If less than 1 hour's worth, you may want to modify the log backup schedule accordingly.

    "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

  • January 4, 2013 at 4:50 am

    #1573689

    Hi Anthony,

    Thanks for the advice, I will setup another schedule for that time and setup a trace.

    Hi Grant,

    Thank you also for your advice...as it is only a monitor we are not bothered if we lose all the data but I do tend to backup more than I need to 🙂

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

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