locked
Transaction Log does not truncate and shrink using a SQL Server 2005 Maintenance Plan

    Question

  • We are using SQL Server 2005 (SP1). I have created a maintenance plan that backs up up the datebase every night. The problem is that the transaction log is continuing to grow. I have been told that a full backup will automatically truncate and shrink the transaction log. However, this is not happening. How can I truncate and shrink the transaction log after a full backup as part of our maintenance plan. Thank you.
    Monday, May 15, 2006 5:06 PM

Answers

  • Perform as follows,
    1. Take full backup of your database
    2. Take t-log backup of your database frequently say every 30 or 15 minutes so that log file will not grow drastically
    3. Shrink if you do not have any free space. You can perform this operation manually if required.
    Generally avoid shrinking the database and keep it as the last option.

    - Deepak
    Saturday, February 02, 2008 5:23 AM
    Moderator

All replies

  • I was under the impression that the transaction log can only be shrunk after a transaction log backup.  Currently for Maintenance plans I have transaction log backups running durring the week and then a full backup on weekends that first shrinks the database and then backs it up.  It seems to be shrinking the transaction logs correctly.  This may depend upon the recovery model used by the database as well.  We use full.
    Monday, May 15, 2006 7:24 PM
  • We are doing a full backup every night. Do you still need to do a transaction log backup?
    Monday, May 15, 2006 11:09 PM
  • I experience the same thing as davidg12. We also do a full backup every night (including of the transaction logs!!) - using the Full recovery model. However, no truncation is being done.
    Please post the solution if you find one...!

    Tuesday, May 16, 2006 12:24 PM
  • Hello All,

    I am new to this forum. I have faced a similar situation.

    During this we used to perform a manual truncation of the transaction log.

    It is a technique published in the ExpertsExchange.com.

    http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_20651271.html

    In simple steps, i will list down the points

    1.) Stop the Database Server and Start it before it detects all the databases,  

     Detach the database which you need to truncate the transaction log by running the below statement in the query window

          sp_detach_db [DatabaseName]

    2.) Once you detach the database, now Open the folder where you find the transaction log file of the database server.

    In this case , it is "\Microsoft SQL Server1\MSSQL\Data\"

    Rename the transaction log file to a different name.

    3.) Then attach the database again with the below statement  ( attach DB using the data file)

    sp_attach_db @dbname = N'DatabaseName',

    @filename1 = N'D:\Microsoft SQL Server1\MSSQL\Data\DatabaseName.MDF'

    Note : sp_attach_db requires two parameters namely @filename1=datafile and @filename2=logfile.

    but here do not mention the filename2 i.e dont mention log file name.

    This statement creates a new transaction log file to the database. The Renamed Transaction can be moved to different folder or your can delete it.

    NOTE :

    NORMALLY THIS IS NOT ADVICEABLE TO DEVELOPER OR NEW DBA's as it requires MORE CARE WHEN DOING THIS PROCESS EXECUTION. PLEASE DO THIS CAREFULLY.

    BUT THIS PROCESS SURELY HELP THE MAINTENANCE PEOPLE TO GET RID OF THE BACKUP PROBLEMS, STORAGE ISSUES.

    Hope this helps a little to you all.

    REGARDS,

    DHINESH KUMAR

     

     

     

    Tuesday, May 16, 2006 2:45 PM
  • Based on your advice, I modified the maintenance plan to include a shrink before backup, a complete database backup, a transaction log backup and then another shrink after backup. This seems to have worked. The transaction logs have shrunk considerably. Thank you.

    David

     

    Tuesday, May 16, 2006 4:14 PM
  • This seemed to be a common occurance.  However, my solution was a little different and a throw back.  I ran the transaction backup then run a dbcc shrinkfile task.  That worked.  However, I will try the shrinkdatabase task and see how that works.

    Akinja

    Thursday, May 18, 2006 3:14 PM
  • Doing a full backup every night might not be enough. Our database (full-recovery) constantly (24*7*266) writes about 100K per hour. So we do a full backup weekly, a differential backup nightly and a transaction backup every 15min in a rotating biweekly backup device scheme.

    This scheme keeps our transaction logs to a minimum. The transaction log backups are typical small as are the differential backups.

    The only problem are cleanups (indexes, historic data etc.) So this is done during scheduled maintenance windows.

    Thursday, February 08, 2007 5:26 PM
  • As a rough guideline your database's transaction log size should be about 25% of the size of the combined total of your database's data files. If the log is significantly larger, and is causing you problems, then you can always backup the log more regularly to keep its size down if you wish.

    The log is not truncated when you perform either a full or differential backup. To see this for yourself run PerfMon and add the following counter, choosing one of your databases to monitor:

    SQL Server - Log -> Log Space Used (%)

    Watch the counter and perform a full database backup - you should see no drop in the counter's value. Perform a log backup and the counter's value should drop.

    Confusingly in SQL Server 2005 if you create a new database then perform a full backup then the log appears to be truncated for the first backup only, however if you take a log backup before the first full backup then subsequent full backups do not truncate the log.

    Shrinking the log file every day is not necessarily a good thing as the constant growth and shrinkage of the file can cause fragmentation on disk. It is generally better to set the log (or shrink it) to a realistic size and then perform more frequent log backups to manage the space usage within the log file. Backing-up the log more frequently also means that you will lose less data in the event of a disaster.

    The log file deletion process described above (by Dhinesh) should only be carried out as a last resort and should not be incorporated into a regular maintenance schedule.

    Chris

    Thursday, February 08, 2007 7:43 PM
  • There is a database option called autoshrink. This will reduce the log files (I believe) automatically.

    But why the hassle? HD space is cheap these days. What we do is let the database run for a few days having the Log file auto resize it self, add a few Gigs for kicks and than switch off autoresizing. You might want to run a defragmentation after that to have all the database files (mdf, ldf, and ndf) nicely contiguous. Letting the log file "eat up" all free space might run you in serious problems you're only come aware of when it is too late.

    Jo

    • Proposed as answer by Tom Garratt Wednesday, December 23, 2009 7:03 PM
    Thursday, February 08, 2007 8:00 PM
  • The only problem with the autoshrink command is that, as the name suggests, you have no control over when the shrinking will occur, and you can bet that SQL Server will decide to shrink the log file during a busy period - for this reason it is generally recognised as a best practice to set autoshrink off. Again if the log file is regularly shrunk then forced to grow again then fragmentation is likely, which will hurt performance as log files benefit hugely from contiguous disk space.

    Agreed that disk space is cheap these days, although it isn't easy to add extra or higher-capacity drives to existing RAID arrays.

    Another point to consider is that the larger the files then the longer the database will take to restore, which obviously may or may not be a problem.

    Everyone's environments and requirements are different but I'm of the opinion that regular scheduled log backups should be taken and that transactions should be kept as short as possible, both of which will help keep the log file to a manageable size with minimal manual intervention.

    Chris

    Thursday, February 08, 2007 8:59 PM
  • Check your Recovery Model for the Database, if you chose Full the log will not be truncated as part of the maintenance plan. If you choose the Simple Recovery Model than the log can be truncated as part of the maintenance plan. Use the SQL 2005 help and search on Recovery Models for further detail....
    Wednesday, March 28, 2007 7:45 PM
  • I tried to backup the transaction log to see if its size would shrink, but it didn't.  I double checked and the 'Truncate...' option was set.  Is there another setting which affects this?

     

    Also, I'm a little confused about why the transaction log is useful at all after a full backup is taken.  It stands to reason that any restore plan which would incorporate the transaction log would begin with the database restore and a roll forward of the transaction log, if it was available.  As such, it is currently my plan to tuncate the log file each night after the backup is complete.  (or should it be before?)

     

    Any advice would be appreciated, or please corect me if I misunderstand.

     

     

     

     

    Saturday, February 02, 2008 3:55 AM
  • You need to take the T-log backup and then shrink the log file,
    1. Backup log database name to disk='Path\filename.trn'
    2. DBCC shrinkfile('log file name', 500)
    You can get more information about the commands I mentioned from BOL

    Also check this thread where it discussed about t-log growth and how to minimize the same,
    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2728787&SiteID=1
    If you need anything else pls feel free to ask the same !

    - Deepak
    Saturday, February 02, 2008 4:05 AM
    Moderator
  • Thanks Deepak, your response and the postings of the other forum were very useful.

     

    Here are my takeaways...

    1) Shrink the TLOG once

    2) Moving forward, backup the TLOG frequently

    3) TLOG size growth will be limited to transactions processed in between TLOG backups

     

    Make sense?

    Saturday, February 02, 2008 4:45 AM
  • Perform as follows,
    1. Take full backup of your database
    2. Take t-log backup of your database frequently say every 30 or 15 minutes so that log file will not grow drastically
    3. Shrink if you do not have any free space. You can perform this operation manually if required.
    Generally avoid shrinking the database and keep it as the last option.

    - Deepak
    Saturday, February 02, 2008 5:23 AM
    Moderator
  • I too am confused about this, our system has a 64MB db, and a 20GB (!) log file.

     

    Surely (excuse minimal db understanding here), but if you do a full backup of the DB, the log file at that point in time should be 0kb - on my system especially at night, because the db is only used 9-5pm --so no-one is using it.

     

     I dont understand how I get to having a tlog that is this size when all the transactions should be committed, and at the point in time of the full backup there should be nothing in the log because it's consolidated.

     

    I would expect the log to grow *after* the backup as new transactions are added to the db once it's in use again?

     

    Mart.

     

    Saturday, February 09, 2008 8:35 AM
  • I also have this issue with the logfile not shrinking...The database is about 400MB and the logfile is 45GB...I need to shrink this logfile...

    HELP

    Friday, October 10, 2008 2:34 PM
  •  Mart the gadgetman wrote:

    I dont understand how I get to having a tlog that is this size when all the transactions should be committed, and at the point in time of the full backup there should be nothing in the log because it's consolidated.

     

    A full backup does not remove inactive transactions from the log. In Full recovery mode the only way to remove them is to issue a transaction log backup. If you do not need/want to take transaction log backups consider putting your database into SIMPLE recovery. The inactive transactions in the log will be truncated at regular intervals whenever a checkpoint is issued by SQL Server. This will keep the log at a manageable size.

     

    NB: When transactions are removed, the file itself is not truncated- but merely the logical log is truncated. If you have a large file you may need to issue a SHRINKFILE statement once you have resolved the above.

     

    HTH!

    Friday, October 10, 2008 2:42 PM
    Moderator
  • That was helpful, thanks. Since my database is only used for development and QA I don't need full recovery. I was able to shrink the log to 0 by switching to Simple recovery mode (Right click the database --> properties --> options).


    Friday, November 07, 2008 1:49 PM
  • If you want to shrink the transaction log file in your maintenance plan first create a step to backup the transaction log and then create a new T-SQL step with the follwing commands:

    use <DBName>
    DBCC shrinkfile('<logfilename>', 500)


    This will shrink the logfile to 500 MB
    Wednesday, November 12, 2008 5:10 PM
  • I had to do a very large maintenance query that required me to move all large amount of data.  As a result the log file grew to a very large size.

    How I have run the DBCC shrinkfile to go back to the 500MB I had it at before I started (note I am running in Simple Recovery) but it only shrinks to 2.5GB.  I have run several days of normal backups and a couple manual backups to no avail, it will not shrink any more then that?

    ideas?
    Thursday, December 18, 2008 1:59 PM
  • Hi Mart and All friends,

    These are some ways you can do for backup.

    Situation 1: 
         
    1) If your DB is not so critical,
    2) You can take a risk of one day data loss
    3) You dont have space in your setup box
    4) You DB Size not so high like in TB.

    Then keep your DB in simple recovery modal and take full backup every day. Its a beneficial to keep your DB in this modal for min log size.

    Situation 2: 

    1) If your DB is so critical
    2) If you are performing Bulk operation in your DB on regular basis.
    3) You can take a risk of data when you doing bulk operation
    4) You have space crunch in your setup box
    5) You dont want to configure Mirroring in your setup
    6) You DB Size not so high like in TB.

    Then keep your DB in bulk recovery modal and setup Full, Differential backup as per you and transaction backup on every 15 min.

    Situation 3: 

    1) If your DB is so critical
    2) You can take any risk with data.

    Then keep your DB in Full recovery modal and setup Full backup every day and transaction backup on every 15 min.

    How to truncate/shrink your LOG file: In your setup if you are using Full recovery modal for your database and you are not taking transaction backup, doing these steps for Shrinking your log file.
     
    Steps:
    1) Take a full backup of database.
    2) After completing the backup run this command on query analyzer.
        USE <DB Name>;
        GO





        -- Truncate the log by changing the database recovery model to SIMPLE.
        ALTER DATABASE <DB Name> SET RECOVERY SIMPLE;
        GO
        -- Shrink the truncated log file to 1 MB.
        DBCC SHRINKFILE (<DB Log File Name>, 1);
        GO
        -- Reset the database recovery model.
        ALTER DATABASE <DB Name> SET RECOVERY FULL;
        GO







    Deepak Goyal





    • Proposed as answer by kali_subbu Friday, December 19, 2008 6:23 AM
    Friday, December 19, 2008 5:42 AM
  • Hi Steve,

    Can you please send me DB details like recovery modal, file size, Initial file size and growth size. So I can suggest any solution.
    Please run this command and send the result.
        DBCC SQLPERF('LOGSPACE')

    Deepak Goyal
    Friday, December 19, 2008 5:51 AM
  • Hi Steve,

    You can run following queries:

    backup log <dbname> with truncate_only
    dbcc
    shrinkfile (<log file name>,<size of the log file>)

    Ex:

    backup log pubs with truncate_only
    dbcc
    shrinkfile (pubs_log,1)

    - Subbu



    Friday, December 19, 2008 6:22 AM
  • kali_subbu said:

    Hi Steve,

    You can run following queries:

    backup log <dbname> with truncate_only
    dbcc
    shrinkfile (<log file name>,<size of the log file>)

    Ex:

    backup log pubs with truncate_only
    dbcc
    shrinkfile (pubs_log,1)

    - Subbu



    This is not supported in SQL Server 2008, and should not be used on production systems.  Doing so breaks the log chain, and removes the ability to perform a full recovery of the database.  If you are not doing log backups to perform full recovery, then the database recovery model should be changed to simple as Deepak points out above.  If you make a switch from full to simple, shrink and then back to full again, you need to immediately take a full backup of the database and begin your log backups again.  If you shrink the log file and it grows again, you shouldn't shrink the log file.  Constant grow/shrink/grow/shrink will reduce performance and introduce file system fragmentation to the server which further degrades performance. 

    I personally recommend against shrinking the transaction log unless doing so is in the interests of expanding it back out to lower the number of VLF's in the log, or you have transitioned from FULL to SIMPLE recovery and don't plan to return to FULL Recovery.   The larger your database is, the larger the logs need to be to support ongoing database maintenance operations like index rebuilds.  I have a 20GB Log file on a 300GB database  because it takes that much log space to do index maintenance.  There is nothing wrong with this.  If space on disk is of concern, then you need to upgrade your disk space to accomodate your databases transactional needs without shrinking the log as a regular task.

    For Steve's problem, I would look for an open transaction possible in the database that is keeping the log file from being truncated internally marking the space for reuse.  In Simple recovery that would be the one thing that prevents an auto truncation to occur.  You can also force a manual checkpoint on the database and make sure it has released what can be released.

    In the future try doing batched updates/deletes to your database to minimize log growth.  You can find out more how to do this on the following blog post:

    The Database Transaction Log - Part 1: Managing Size



    -- Jonathan Kehayias (MCITP) | Please mark answers that solve your problem | http://www.sqlclr.net
    Friday, December 19, 2008 7:51 PM
    Moderator
  • If you're not backing up transaction logs, change the recovery model on your database from full to simple.  The full recovery model expects transaction log backups which is why the logs won't shrink when you shrink the database.  More information on recovery models can be found here:

    http://databases.about.com/od/sqlserver/a/recoverymodels.htm

     

     

    Thursday, May 20, 2010 4:42 PM
  • It's possible to shrink transaction file for a database with mirror, backup must be performed as there are actives Virtual Log File : 
    http://www.xoowiki.com/Article/SQL-Server/tronquer-journal-de-log-sur-base-en-miroir-499.aspx
    Monday, March 14, 2011 9:54 AM
  • I know that this was added quite a long time ago, but I don't think you need to do a shrink of the transaction logs before backing up the database. If you do a checkpoint, then a transaction log backup this should truncate the logs to only the active portion. Of course, it won't reduce the size of the logs, you'll then need to do a SHRINKFILE on the logs to remove all unused portions.

    If you do a shrinkfile before the checkpoint and backup it won't do much.

    Monday, May 14, 2012 1:01 PM