SQL Server Performance

Log file size

Discussion in 'T-SQL Performance Tuning for Developers' started by balance, Nov 19, 2002.

  1. balance New Member

    My log file has been increasing in size unusually high lately to the point of maxing out the total alloted space and making my application "break." I find myself backing up the log file pretty much every other day to prevent this from happening. The total allocated space is set to 1gb and the auto-grow setting is at 5mb.
    Is there anything I can do (other than set the alloted space to a larger size) to prevent this from happening? It used to not take up so much space so quickly, what could've changed?

    Thanks much in advance.
  2. royv New Member

    If you do not need the transactions, then you can truncate the log every so often. BE CAREFUL, because when you truncate your log, then you will not be able to recover anything before the truncation. Make sure that you do not need any of the transactions before you truncate the log.


    "How do you expect to beat me when I am forever?"
  3. joachim.verhagen New Member

    Every other day is not often for log backup.
    I assume you make a full backup every day.

    Do you need log backups?
    If not go to the simple recovery model. At every checkpoint finished transactions will be removed from the log.

    If you do need log backup I suggest you use a maintenance plan to make a backup every hour.

  4. satya Moderator

    ITs always recommended to have Trans.log backup using differential and full backup daily, which saves your data incase of failure.



    Satya SKJ
  5. bradmcgehee New Member

    One of the things I like to do (and it is just one of many options) is to do a full backup once a day and backup the transcation log ever 15 minutes. Backing up the transaction log truncates is automatically and provides a backup in case a recovery is needed. This is especially useful if you make the database and log backups onto another server over the network. Also, by backing up the log often, it will never get very big.


    ------------------
    Brad M. McGehee
    Webmaster
    SQL-Server-Performance.Com

Share This Page