none
How to decrease the size of the Transaction log daily?

    Question

  •  

     

     

    I have a database that the transaction log grows about 1 GB per day. I would like this size was decreased daily. Does anyone have any suggestions? 
    Some friends told me that after the Full backup that is done daily, I should perform a backup of transanction log with option to truncate and after, make a shrink in the database. That is exactly what should be done?

    Thank you,
     
    Best Regards,
     
    Ralph Haddad
    Wednesday, January 23, 2008 2:14 PM

Answers

  • Hi Ralph,
                 No problem you are most welcome to ask your questions and we are here to help you out Smile
    If you have Full recovery model you can take regular t-log backups so that the log file growth can be minimized. Regarding recovery models I believe Rich and Jonathan have explained in a detailed manner.

    I would advise you not to perform regular shrinking instead take regular t-log backups and use shrinking as the last option.

    The following are the causes for transaction log growth,

    1. Due to Uncommitted transactions. We can find if there are any open transactions using DBCC OPENTRAN.
    2. Running DBCC REINDEX, CREATE INDEX operations with the database in Full Recovery model.
    3. Running extremely large transactions like Bulk Insert, Select Into commands.

    For more information about the cause for log file growth please refer the below link http://support.microsoft.com/kb/317375/

    The following are the proactive measures in order to minimize the unexpected log file growth,

    1. If you do not want point in time recovery of your databases then you can change the recovery model to Simple.
    2. Set the size of the transaction log files to a large value to avoid the automatic expansion of the transaction log files.
    3. Configure the automatic expansion of transaction log in terms of MB instead of %.
    4. Backup the transaction log regularly to delete the inactive transactions in your transaction log if you are using full or bulk logged recovery model.
    5. You can switch the recovery model to Bulk logged from full recovery model if you perform some bulk inserts, select into, bcp, alter index, create index commands because these operations will be minimally logged in bulk logged recovery model and after those operations are completed you can switch over to full recovery model.

    For more information about the proactive measures please refer the below link,
    http://support.microsoft.com/kb/873235

    What to do if log file has grown enormously

    When your log file has grown enormously you have 2 options to curtail the growth,

    1. Take a transaction log backup which will truncate the log file and then perform shrinking of log file using DBCC SHRINKFILE statement it will be successful.
    2. You can execute the command Backup log your database name with truncate_only (or backup log your database name with no_log) and shrink the log file. But running those commands (backup log with truncate_only or no_log) will truncate the log file but will break the log chain and hence it should NEVER BE USED. The ONLY case it can be used is when the disk in which the transaction log resides is completely FULL and there is no way to clear the disk space.
    Hence please try to avoid the 2nd option I mentioned above. Due to the above fact the truncate_only or no_log option is deprecated in SQL 2008.
    Wednesday, January 23, 2008 5:37 PM
  • You essentially have something like this:

     

    Code Snippet

    -- this is an example strategy in FULL recovery

    12am FULL.bak -- take full backup

    4am TLOG1.bak -- take a tlog backup

    8am TLOG2.bak -- take a tlog backup

    12pm TLOG3.bak -- take a tlog backup

    -- a 1pm crash

     

    Restore FULL.bak -- restore full backup

    Restore TLOG1.bak -- restore subsequent tlogs in order

    Restore TLOG2.bak

    Restore TLOG3.bak

    -- 1 hours dataloss (12pm-1pm)

     

    -- in SIMPLE recovery (unable to take transaction log backups)

    12am FULL.bak -- take full backup

    -- a 1pm crash

     

    Restore FULL.bak -- restore full backup

    -- 13 hours dataloss (12am - 1pm)

     

     

    Depending on the criticality of the system and number of transactions you may want to set the frequency of log backups from between 15mins and 1 hour. As Jon has mentioned, a well placed differential backup will reduce the number of transaction logs you have to apply.

     

    Check out Books Online for lots of information on Backup and Restore strategies:

     

    http://msdn2.microsoft.com/en-us/library/ms187048.aspx

     


    HTH!

    Thursday, January 24, 2008 2:33 PM
  • If you begin doing Transaction Log Backups, the file won't grow.  You can shrink it once intially, but then you should never shrink it again.  If it grows, you should leave it where it is, or increase the frequency of your log backups to allow it to truncate and reuse the log space it has.  If it grows to say 200MB between your log backups, it will likely do it again if you shrink it.  You would have to backup the log maybe twice as often to keep it somewhere around 100MB in a system that sees consistent activity that is predicatable.

     

    Thursday, January 24, 2008 4:50 PM

All replies

  • What is the acceptable data loss for your database?  If the business accepts that you can only restore your full backup from the night before, then set your database to simple recovery, and you won't have full logging happening.  Otherwise, you need to actually backup the log and store it with your full backups for recovery purposes at a regular interval such as every 15 minutes, every hour, every 2 hrs.  It is all up to you on the interval.  Backup LOG will truncate the archived portion of the log and free it for reuse.  The more often you do it, the less likely it is to grow.  If you are just going to truncate it anyway, then you have broken the log LSN chain, and it is useless to keep it in full recovery.  Just set your recovery model to simple in the database options, and it won't have the growth problems like this.  Then shrink the file down, and it should stay self managing.

     

    The following code will shrink the log file once you have truncated its LSN's

     

    Code Snippet

    DECLARE @LogFileName varchar(100)

    SELECT @LogFileName = rtrim(name)

    FROM dbo.sysfiles

    WHERE Name like '%_log%'

     

    DBCC SHRINKFILE(@LogFileName, 2)

     

     

    You want to minimize resizing your database files as much as possible because it will create external file framentation in the operating system which will impact performance.  I rarely ever shrink database files.  If SQL grew it to that point, then it will likely just grow back to that point.

    Wednesday, January 23, 2008 2:35 PM
  • If you have the database in SIMPLE recovery mode you won't be able to backup the transaction log but the file should remain small (depending on size of transactions). If you have your database in FULL recovery then consider running regular transaction log backups to keep the size of the active log small and to enable point in time recovery.

     

    Backing up the log will truncate the contents of the file but the physical size of the file will remain the same. This is by design.

     

    You want to minimise the number of times your data/log files have to grow so ideally you should size your transaction log appropriately. I would recommend against regularly performing shrinks of the physical file for performance reasons and if you need to do this, you should probably look at bigger disks! It can be done however, by issuing the following command:

     

    Code Snippet
    DBCC SHRINKFILE(YourLogicalLogName, TRUNCATEONLY)

     

     

    HTH!

    Wednesday, January 23, 2008 2:38 PM
  •  

    When I make the shrink, the log is truncated?
     
    Thank You,
     
    Ralph Haddad
    Wednesday, January 23, 2008 4:43 PM
  • Not necessarily.  If you are in full recovery, then no it won't be truncated.

     

    run the following command:

     

    Code Snippet

    SELECT DATABASEPROPERTYEX(databasename, 'RECOVERY')

     

     

    If it returns FULL or BULK_LOGGED, then you will have to backup the log, either to disk or specifying the truncate only to get it truncate the space off.  If you do that, then you might as well change your recovery model to SIMPLE and be done with this problem.  If it says you are already in SIMPLE, then it should shrink without problem.
    Wednesday, January 23, 2008 4:47 PM
  •  

    And if I want to remain in Full Recovery mode, what would be the best strategy?
    I would have to make several backups of transaction log during the day, and the interval between a backup and the other I would shrink, is it?

     

    Excuse me the questions, I am still learning about sql. I thank also the patience that are in answer them.

     

    Thank You,

     

    Ralph Haddad

    Wednesday, January 23, 2008 5:05 PM
  • You need to schedule a backup of the log periodically through out the day.  I usually discuss the recovery strategy with the owner of the product and get there expectations of service level.  For internet e-commerce application databases, I run a backup to disk every 15 minutes of the logs, that are immediately swept off to tape by Netbackup.  For other databases that are "less critical" as defined by the owners, I backup the logs every two hours, and they are managed directly by Netbackup.  Other databases are in simple recovery mode.  What happens is when you do a full backup or a log backup, the LSN's in the log get marked as safe, and can be then truncated by SQL and the space is reused.  If you aren't doing the Log backups between full backups, you aren't really benefitting from the Full Recovery Model.  At best you can hope to be able to get the tail log file from disk and use it in recovery to a point in time.  However, if your disks are the cause of a crash, you will only have the full backup on tape (hopefully) that you can recover to.  For more information  see:

     

    http://msdn2.microsoft.com/en-us/library/aa173678(SQL.80).aspx

     

    or

    http://msdn2.microsoft.com/en-us/library/ms191253.aspx

     

     

    Wednesday, January 23, 2008 5:15 PM
  • Hi Ralph,
                 No problem you are most welcome to ask your questions and we are here to help you out Smile
    If you have Full recovery model you can take regular t-log backups so that the log file growth can be minimized. Regarding recovery models I believe Rich and Jonathan have explained in a detailed manner.

    I would advise you not to perform regular shrinking instead take regular t-log backups and use shrinking as the last option.

    The following are the causes for transaction log growth,

    1. Due to Uncommitted transactions. We can find if there are any open transactions using DBCC OPENTRAN.
    2. Running DBCC REINDEX, CREATE INDEX operations with the database in Full Recovery model.
    3. Running extremely large transactions like Bulk Insert, Select Into commands.

    For more information about the cause for log file growth please refer the below link http://support.microsoft.com/kb/317375/

    The following are the proactive measures in order to minimize the unexpected log file growth,

    1. If you do not want point in time recovery of your databases then you can change the recovery model to Simple.
    2. Set the size of the transaction log files to a large value to avoid the automatic expansion of the transaction log files.
    3. Configure the automatic expansion of transaction log in terms of MB instead of %.
    4. Backup the transaction log regularly to delete the inactive transactions in your transaction log if you are using full or bulk logged recovery model.
    5. You can switch the recovery model to Bulk logged from full recovery model if you perform some bulk inserts, select into, bcp, alter index, create index commands because these operations will be minimally logged in bulk logged recovery model and after those operations are completed you can switch over to full recovery model.

    For more information about the proactive measures please refer the below link,
    http://support.microsoft.com/kb/873235

    What to do if log file has grown enormously

    When your log file has grown enormously you have 2 options to curtail the growth,

    1. Take a transaction log backup which will truncate the log file and then perform shrinking of log file using DBCC SHRINKFILE statement it will be successful.
    2. You can execute the command Backup log your database name with truncate_only (or backup log your database name with no_log) and shrink the log file. But running those commands (backup log with truncate_only or no_log) will truncate the log file but will break the log chain and hence it should NEVER BE USED. The ONLY case it can be used is when the disk in which the transaction log resides is completely FULL and there is no way to clear the disk space.
    Hence please try to avoid the 2nd option I mentioned above. Due to the above fact the truncate_only or no_log option is deprecated in SQL 2008.
    Wednesday, January 23, 2008 5:37 PM
  •  

     

    What do you advise me then?

    Let's assume that the application is critical and has to perform a backup every 15 minutes. Let's see if I am right.
    At the beginning of the Day, a Full Backup, the first backup of the transaction log backup after the Full I set with option to overwrite to overwrite the backup of the transaction log of the previous day. After the first backup of the transaction log, the other backups were with option to append the same every 15 minutes. If a crash happens in the database, I would restore the full backup, and an archive of the transaction log, which would be the append to all transantion log of the day. Recalling that the first backup of the transaction after the Full Backup option would be to overwrite to overwrite the previous one.
    If using the method of Full Recovery.
    If I were using the Simple way, I would not worry about the transaction, because it would not grow by more that I had a very large number of transactions?
     
    That's right, I am right or wrong?
     
    Thank you,
     
    Ralph Nogueira Haddad
    Thursday, January 24, 2008 1:26 PM
  • You basically have the idea.  Under full recovery, you would restore the full backup, then each log backup in order that they were taken, so it isn't just an archive of the logs.  If you were really concered, you could add a differential backup in the middle of the day, and this just reduces the recovery time to be Full, differential then logs, restored in order taken. 

     

    In Simple, you are correct, that you don't have to worry about the Log growing.  I don't recommend this model to any critical database.

    Thursday, January 24, 2008 1:32 PM
  • You essentially have something like this:

     

    Code Snippet

    -- this is an example strategy in FULL recovery

    12am FULL.bak -- take full backup

    4am TLOG1.bak -- take a tlog backup

    8am TLOG2.bak -- take a tlog backup

    12pm TLOG3.bak -- take a tlog backup

    -- a 1pm crash

     

    Restore FULL.bak -- restore full backup

    Restore TLOG1.bak -- restore subsequent tlogs in order

    Restore TLOG2.bak

    Restore TLOG3.bak

    -- 1 hours dataloss (12pm-1pm)

     

    -- in SIMPLE recovery (unable to take transaction log backups)

    12am FULL.bak -- take full backup

    -- a 1pm crash

     

    Restore FULL.bak -- restore full backup

    -- 13 hours dataloss (12am - 1pm)

     

     

    Depending on the criticality of the system and number of transactions you may want to set the frequency of log backups from between 15mins and 1 hour. As Jon has mentioned, a well placed differential backup will reduce the number of transaction logs you have to apply.

     

    Check out Books Online for lots of information on Backup and Restore strategies:

     

    http://msdn2.microsoft.com/en-us/library/ms187048.aspx

     


    HTH!

    Thursday, January 24, 2008 2:33 PM
  •  

     

    I understand, so instead of using only 1 file to store the backup of the transaction log, I use 1 file of log for each backup, even 15 in 15 minutes, 1 file for each. For example, Tlog1, tlog2 ...
    I understand.
    If I use the differential backup, for example.

    12am FULL.bak - take full backup
    4am TLOG1.bak - take a backup tlog
    8am TLOG2.bak - take a backup tlog
    12pm DIF1.bak - take a backup Differencial
    13pm TLOG3.bak - take a backup tlog
    14pm TLOG4.bak - take a tlog log
    15pm crash to the database

    Let's assume that the 15:00 hrs give problems in the bank, I restore the Full back (Full.bak) from 12:00 hrs, the differential (dif1.bak) and the transaction log (tlog3.bak and tlog4.bak) after the differential? Or should I also use the transaction log (tlog1.bak and tlog2.bak) before the full?
     
    Just to conclude, between a backup of the transaction log and another, I can set the shrink of the database or just the datafiles, and will not cause problems in the restore of the database later. In case if I use the shrink will truncate the database?
     
    Well, these are my last questions.

    Thank you for the help of you! It was very important for me!
     
    Thank You,
     
    Ralph Nogueira Haddad
    Thursday, January 24, 2008 3:24 PM
  • You're right on with your recovery example. You would only need to do FULL, DIFF, TLOGs.....

     

    As for the final point of shrinking the database/files - the backing up of the transaction log file should keep the size of the log fairly small/consistent so there would be no need to do this. Doing so is technically possible but you may see performance problems (due to the files having to regrow).

     

    HTH!

     

    Thursday, January 24, 2008 3:36 PM
  •  

    What would you recommend to keep the file of the transaction log with a small size, since it grows about 1 GB per day?
     
    Thank You,
     
    Ralph Haddad
    Thursday, January 24, 2008 4:46 PM
  • If you begin doing Transaction Log Backups, the file won't grow.  You can shrink it once intially, but then you should never shrink it again.  If it grows, you should leave it where it is, or increase the frequency of your log backups to allow it to truncate and reuse the log space it has.  If it grows to say 200MB between your log backups, it will likely do it again if you shrink it.  You would have to backup the log maybe twice as often to keep it somewhere around 100MB in a system that sees consistent activity that is predicatable.

     

    Thursday, January 24, 2008 4:50 PM
  •  

    I will do the shrink of the transaction log initially, to reduce the size of the transaction log.
    I must stipulate a frequency of the transaction log backups so that the size does not grow much.
    After that backup strategy will no longer need to shwink in the database.
    It is this?

     

    Thank You,

     

     

    Ralph Nogueira Haddad

     

     

    Thursday, January 24, 2008 6:26 PM
  • That is exactly it. 

     

    Thursday, January 24, 2008 6:48 PM
  •  

    I apologize in advance for the apparent ignorance my following question is about to display...

     

    What happens to the TLOG at the time of the FULL backup?  That is, shouldn't it be truncated at this point since any restore would begin from the FULL backup?

     

    If yes, then does this mean the maintenance plan should include first the FULL backup, followed by a TLOG truncation? or vice versa, TLOG Truncate, then FULL backup?

     

    Thanks.

    Saturday, February 02, 2008 5:08 AM
  • Monday, February 04, 2008 9:23 PM
  • Following along this thread.   If in full mode and you do index rebuilds this will increase the log file isn't this like 1 1/2 times of databasesize ? so would you put database mode in simple for the index rebuilds and do you stop doing the log backups when these are running.

     

    12:00 full

    12:15 log

    12:20 index rebuild

    12:30 log

     

    If the 12:20 the index still happening will this be ok to still have log running.

     

    Friday, June 06, 2008 6:09 PM