Log File how i prevent form growing

nav_studentnav_student Member Posts: 175
edited 2014-03-02 in SQL General
Helo guyi have SQL 2008 R2.

My database is about 25GB, Recovery Model is Full, and the Log File is set to a Initial Size of 1GB and the Autogrowth By 1000MB to a restricted growth 2097152MB.

In a couple of hours the Log (as the Initial Size) is 4GB. Can you tell me why?

How can i prevent this quick growth?

Thanks for your help :)

Comments

  • Marije_BrummelMarije_Brummel Member, Moderators Design Patterns Posts: 4,262
    It keeps track of all transactions, creating a salesline and deleting one are two lines, not 0.
  • nav_studentnav_student Member Posts: 175
    ???
  • Marije_BrummelMarije_Brummel Member, Moderators Design Patterns Posts: 4,262
    Have you used google or MSDN on how SQL Transaction log works?
  • nav_studentnav_student Member Posts: 175
    Hi Mark,

    check over the Internet and it seems that my problem is not having a Transaction Log Backup.

    Dont confuse truncating the log with shrinking the log.

    To TRUNCATE is to remove the transactions in the log that are before the last checkpoint, (the checkpoint being when transactions are flushed to the database itself). This is done using the BACKUP command.

    To SHRINK the log is to reduce the actual log file size. This is done using DBCC commands.


    I recommend this 2 sources:
    http://www.sqlskills.com/blogs/paul/why-is-log_reuse_wait_desc-saying-log_backup-after-doing-a-log-backup/
    http://technet.microsoft.com/en-us/magazine/2009.02.logging.aspx



    I have search also here on Mibuso and i found this:
    With this command, you can check WHY SQL Server cannot free the transaction log:
    SELECT name,log_reuse_wait_desc
    FROM sys.databases
    WHERE log_reuse_wait_desc <> 'NOTHING'; -- nothing means Nothing is blocking the reusing of transaction log space.

    With this command, you can see how many virtual log files there are in your DB (you need to run it using the db you want to check):
    DBCC LOGINFO;


    http://mibuso.com/forum/viewtopic.php?f=33&t=59880&hilit=logs
  • Marije_BrummelMarije_Brummel Member, Moderators Design Patterns Posts: 4,262
    Google is your best friend. (Or Bing.) 8)
  • nav_studentnav_student Member Posts: 175
    One thing that I notice that my Log file as increase is size from 100MB (beacause I have shrink the file, it has 99% of free space) overnight to 13GB.


    During the day I have create a Task to do a Transaction Log Backup every single hour.

    We have a Maintenance Plan overnight that does a:
    Backup Database Task
    Transaction Log Backup
    Maintenance Database Files (1Day)
    Maintenance Log Files (1Day)
    Check Database Integrity Task
    Reorganize Index Task
    History Cleanup

    I believe it's the task Reorganize Index Task that is increasing the log files.

    This morning I have put the TLog initial size to 15GB (growth: 500MB)

    What are your thoughts, about this?
  • bbrownbbrown Member Posts: 3,268
    I noticed the "Maintenance Database Files (1Day)" and the "Maintenance Log Files (1Day)" in your list. Does this mean you are only retaining backups for 1 day? Might give some thought to a longer retension period.
    There are no bugs - only undocumented features.
  • jordi79jordi79 Member Posts: 272
    Just change the database recovery model from full to simple. But bear in mind this has implications on the database recovery in event of a sql server crash. But in most cases, this will shrink your transaction log significantly.
  • bbrownbbrown Member Posts: 3,268
    jordi79 wrote:
    Just change the database recovery model from full to simple. But bear in mind this has implications on the database recovery in event of a sql server crash. But in most cases, this will shrink your transaction log significantly.

    This with "truncate" the log file. It will not shrink it.
    There are no bugs - only undocumented features.
Sign In or Register to comment.