Post reply

Transaction log space - getting big

  • March 21, 2006 at 4:17 pm

    #152846

    Hi,

    The transaction log space is getting very big - data is only about 1GB and transaction log space is about 3 GB ---- what is the reason for this.

    Due to this the disk space fills up very often - and when it happens i do the transaction log backup and shrink the database.But again the next day - the disk space fills up .........

    I have a maintenance backup plan to - for both complete backup and transaction log..... still this happens causing the disk space to fill up very quickly.

    Why does this happen and how to fix this.

    I appreciate the reply to this problem.

    Thanks,

    Natalie.

  • March 21, 2006 at 8:41 pm

    #628008

    Hi,

    Ur database is in full recovery mode.As ur databae size is very small so chage the recovery mode to simple.

    Then the logfile with not grow so much.

    As in full recovery mode what ever u do in the particular database it is written in the transaction log file.

    HTH

    from

    Killer

  • March 22, 2006 at 10:06 am

    #628159

    Hi,

    Thanks for the reply.

    But i am still a bit confused on this -

    (1) why does this happen if the recovery is set to full.

    (2) What if we need to have the recovery model to full  then how to manage the transaction log space.

    (3) If data is very large too (about 4GB), and transaction log is larger than the data (about 5GB)- what is the solution here to manage the transaction log.

    Thanks again for the help.

    Natalie.

     

  • March 22, 2006 at 11:10 am

    #628173

    You are using a maintenance plan - I'll bet you have it set to reindex your database. BAD THING. Reindexing will grow your transaction log at a minimum 1.5 times the size of the data.

    Turn reindexing off. You don't really need to run it every day.

    If you really feel you need it, schedule it to run once every other week.

    -SQLBill

  • March 23, 2006 at 8:51 am

    #628374

    If you wish to manage the TN log, you can issue on a regular basis the dbcc_shrinkfile (see BOL for syntax) command to shrink the actual data file on the disk. A transaction log backup, part of the maintenance plan, will truncate the log (but not shrink the log file itself).

    If you still do want to re-index, use this method.

  • March 23, 2006 at 10:30 am

    #628409

    If you can do reindexing or large data loads during off-periods and can guarantee no other update activity during that period, you can switch to simple recovery mode before, do the maintenance, switch back to full recovery mode. BOL discusses the proper full/differential and log backup procedures in the section on switching recovery modes--YOU MUST DO THIS. You will lose the sequence of log events for the period of maintenance, so you can only recover to the points in time before maintenance starts or to the full backup after its done, but not in between.

    If you can't guarantee no other activity, bulk-logged mode will help with bulk operations including reindexing (see BOL for details of what qualifies as "bulk operations"). Again, you want to do at least a log backup aftwards--this will be larger than normal, but smaller than if you reindexed in full recovery mode.

    David Lathrop
    DBA
    WA Dept of Health

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

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