locked
Reducing the fast growing log file RRS feed

  • Question

  • I am using SQL Server 2008 R2 Express. The size of the .mdf file is 25 Mb but the size of the .ldf file is 417 Mb.

    What are the factors that are adding to such a big size of log file? I only enabled Change Tracking to monitor changes of two days.

    How to reduce this fast growing log file?

    Thursday, February 2, 2012 12:28 PM

Answers

  • You don't need to take a database offline to back it up using the native SQL backup tools.

    I would recommend you start completing regular full online backups of the database probably daily but this depends on your backup solution and size of your database.As part of this you should also regularly backup your transaction log. I usually try to go for a once per hour transaction log backup at a minimum. This means that I can only lose up to 60 minutes of transactions in the event of a failure as I can restore the backup plus all the transaction log backups up the time of the failure.

    The transaction log stores the information that has not been backed up since the last full backup and a full backup does not clear those entries. When running in full recovery mode the transaction has to keep growing if you don't back it up as it cannot re-use the available space and must grow larger to continue to write transaction information to the log - if there is no space the database will go offline as SQL does write ahead logging meaning that all changes to the database must first be written to the log before being written to the data file. Therefore if the log cannot be written to nothing can operate.

    How to: Back Up a Database (SQL Server Management Studio)
    How to: Back Up a Transaction Log (SQL Server Management Studio)

    If you don't start doing this and you keep taking the database offline and backing up the files manually then you may as well change the database to simple recovery mode as your method is not making use of the recover ability features in SQL using full recovery mode.

     


    Sean Massey | Consultant, iUNITE

    Feel free to contact me through My Blog, Twitter or Hire Me.
    Please click the Mark as Answer button if a post solves your problem!

    Saturday, February 4, 2012 6:10 AM

All replies

  • Hi,

    When was the last time you successfully backed up the log? Run this code and you can see when the last log backup completed for each of the databases on your instance. If you are running in full recovery mode you need to perform regular transaction log backups in order to allow the log to wrap around and not have to grow.

    USE master
    GO
    SELECT   d.name, 
             MAX(b.backup_finish_date) AS backup_finish_date 
    FROM     master.sys.sysdatabases d 
             LEFT OUTER JOIN msdb..backupset b 
             ON       b.database_name = d.name 
             AND      b.type          = 'L' 
    GROUP BY d.name 
    ORDER BY backup_finish_date DESC 
    



    Sean Massey | Consultant, iUNITE

    Feel free to contact me through My Blog, Twitter or Hire Me.
    Please click the Mark as Answer button if a post solves your problem!

    Thursday, February 2, 2012 12:44 PM
  • Check the  log_reuse_wait_desc column in sys.databases

    Also have a look at this article which explains the factors that delay log truncation which inturn cause log growth.

    http://msdn.microsoft.com/en-us/library/ms345414.aspx

    HTH,


    Regards, Ashwin Menon My Blog - http:\\sqllearnings.wordpress.com
    Thursday, February 2, 2012 2:36 PM
  • @Sean:

    I usually take backup by turning OFF SQL Server service and copying the .MDF and .LDF files elsewhere. I never used FULL BACKUP option from management studio.

    My database is running in FULL RECOVERY MODE. How to take full backup? Do I need to take it Offline first?

    Saturday, February 4, 2012 5:47 AM
  • You don't need to take a database offline to back it up using the native SQL backup tools.

    I would recommend you start completing regular full online backups of the database probably daily but this depends on your backup solution and size of your database.As part of this you should also regularly backup your transaction log. I usually try to go for a once per hour transaction log backup at a minimum. This means that I can only lose up to 60 minutes of transactions in the event of a failure as I can restore the backup plus all the transaction log backups up the time of the failure.

    The transaction log stores the information that has not been backed up since the last full backup and a full backup does not clear those entries. When running in full recovery mode the transaction has to keep growing if you don't back it up as it cannot re-use the available space and must grow larger to continue to write transaction information to the log - if there is no space the database will go offline as SQL does write ahead logging meaning that all changes to the database must first be written to the log before being written to the data file. Therefore if the log cannot be written to nothing can operate.

    How to: Back Up a Database (SQL Server Management Studio)
    How to: Back Up a Transaction Log (SQL Server Management Studio)

    If you don't start doing this and you keep taking the database offline and backing up the files manually then you may as well change the database to simple recovery mode as your method is not making use of the recover ability features in SQL using full recovery mode.

     


    Sean Massey | Consultant, iUNITE

    Feel free to contact me through My Blog, Twitter or Hire Me.
    Please click the Mark as Answer button if a post solves your problem!

    Saturday, February 4, 2012 6:10 AM
  • @Sean:

    I have just taken Full Backup and Transaction Log backup using Management Studio. It is still showing Transaction Log file (.ldf) size of 427 MB. When is it going to refresh the size?

    Saturday, February 4, 2012 6:29 AM
  • Taking a backup does not reduce the log's physical size on disk. If you want to do that you need to shrink the log file. What size do you think the log needs to be to handle your daily workload?

    If you run this command it should show you that the log space used is now close to 0.

     

    dbcc sqlperf (logspace)
    

    8 Steps to better Transaction Log throughput

     


    Sean Massey | Consultant, iUNITE

    Feel free to contact me through My Blog, Twitter or Hire Me.
    Please click the Mark as Answer button if a post solves your problem!


    • Edited by Sean Massey Sunday, February 5, 2012 12:22 AM
    Saturday, February 4, 2012 7:14 AM
  • @Sean:

    The link is not working. I want to set the log size to half the size of the MDF.

    Saturday, February 4, 2012 1:02 PM
  • I fixed the link and the post also shows you how to use the shrink operation to decrease the size of the log plus how to grow it to the size you choose.

    Sean Massey | Consultant, iUNITE

    Feel free to contact me through My Blog, Twitter or Hire Me.
    Please click the Mark as Answer button if a post solves your problem!

    Sunday, February 5, 2012 12:24 AM
  • Hi,

    to shirnk the file you have run the below command

    DBCC SHRINKFILE (AdventureWorks_Log, 1);

    remove the "AdventureWorks_Log" and insert your DB log file Name

    to Know the Log file name execute this command Sp_helpDB "<Dbname>"

    Jai

    Please click "Propose As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to you


    Jai Shiva Naidu Verizon

    Tuesday, February 7, 2012 2:08 PM