Post reply

Restrict log file size (SQL server 2005)

  • June 8, 2007 at 9:08 pm

    #117118

    hi all,

    I am using SQL server 2005.The Log file size increased to 40GB,so I detach the DB and delete the log & created new log file.Can I restrict the growth.I mean if the file size become 1000MB,I need to clear the log file.Previously it was Enabled the autogrowth 10% & unrestricted File size,Actually I modifed to restricted file size 1000MB.is it work? can I know restricted file size 1000MB what will happen?I mean , is it clear the log after reaching 1000MB?please advice.

    Thanks in advance

    with regards,

    leo

  • June 8, 2007 at 9:18 pm

    #711825

    The log file will not grow that large if you do regular backups.  Setting a 1 gig log file may cause your server to come to a sudden halt if you restrict the size and a large critical operation can't get the log space to run.  If you don't care about backups, set the database recovery mode to "Simple"... recommend you do the backups, though.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • September 26, 2007 at 4:00 am

    #736287

    In response to Jeff's msg, our clients do regular backups of their SQL databases, but this doesn't appear to clear the log files down, even if they do a full backup. Is there anything else, setup wise we shoudl tell them to do?

    Regards

    Ken

  • September 26, 2007 at 4:10 am

    #736291

    If you database is in FULL recovery mode, a database backup is not enough to keep the transaction log small. You also need to take regular log backups.

    If point in time restore is not required you can choose to go with a SIMPLE recovery model. The inactive parts of the transaction log will be recycled and the logfile should stay (relatively) small.

    Markus

    [font="Verdana"]Markus Bohse[/font]

  • September 26, 2007 at 9:10 am

    #736417

    Before you go and run to implement SIMPLE recovery mode make sure that your managers understand the "data loss" risks they are exposed to and only if it is acceptable do it, otherwise regular transaction log backups must be taken in accordance to your disaster recovery plan. You have tested you DRP, right? 😉


    * Noel

  • November 2, 2007 at 5:49 pm

    #747948

    hi i was wondering about log size ....if my db size is 12 gigs what would be a good size for the log file ???

    is there a best practice percentage size for the logfile regarding DB size ???

  • November 2, 2007 at 8:04 pm

    #747956

    My rule of thumb is to open it up... see how big it get's between backups... and then make it's initial size twice that big to handle "contingencies". Other folks use a 10% rule. Regardless of the rule, ya gotta watch it and figure out what it's going to do before making any "hard" settings. There's a reason why it grows...

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • February 8, 2008 at 3:10 pm

    #776632

    Can you restrict the size of the log file after it has been created?

  • February 8, 2008 at 3:19 pm

    #776638

    I wouldn't restrict it. It's where SQL server stores its activity. If you limit it and for some strange reason your log hits that size before your log backup, everything stops. Not sure what happens, but no further processing can then occur.

    I'd make it big enough that it should never grow, but I wouldn't cap it. Never say never: Murphy's listening, and "never" just pisses him off.

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • June 12, 2009 at 12:34 pm

    #1008852

  • June 12, 2009 at 12:57 pm

    #1008859

    Log backups do not shrink the log. If the physical size of the log file is dropping then there's a shrink database or shrink file operation somewhere. Check maint plans, check that autoshrink is not on.

    Please in future post a new thread for a new problem. Thanks

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • June 12, 2009 at 1:56 pm

    #1008884

    removed

  • June 12, 2009 at 3:50 pm

    #1008925

    ebinroy (6/8/2007)

    hi all,

    I am using SQL server 2005.The Log file size increased to 40GB,so I detach the DB and delete the log & created new log file.Can I restrict the growth.I mean if the file size become 1000MB,I need to clear the log file.Previously it was Enabled the autogrowth 10% & unrestricted File size,Actually I modifed to restricted file size 1000MB.is it work? can I know restricted file size 1000MB what will happen?I mean , is it clear the log after reaching 1000MB?please advice.

    Thanks in advance

    with regards,

    leo

    Restrict the file, yes! But be sure you're running Tlog backups as well. Frequency will depend of how critical your database is. If you're not willing to loss data or your database is Mission Critical, you can run Tlogs daily, every 15 min or so. For not so critical databases, daily Tlog backups every 4 or 8 hours should be ok.

    In real world, production environment, big companies, you can not leave it open. It leads to server crashes or your drive becoming full. The question here should be, WHY is getting bigger or not enough. Usually is because you have a lot of uncommitted transactions or during insertions or T-SQL scripts, you are not using BEGIN/COMMIT or Batches. User DBCC LOGINFO and DBCC OPENTRAN to check your Tlogs.

    A basic rule that usually works is assign 25% of your database size to the Tlog.

  • June 12, 2009 at 5:53 pm

    #1008951

    dxmer (6/12/2009)

    I guess I thought I was adding to the conversation to the message above mine by responding to the statment "I'd make it big enough that it should never grow, but I wouldn't cap it".

    I won't post here any more.

    Thanks,

    Scott

    Why not? :blink: It sounded like you were asking a question and Gail provided the correct answer.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • June 12, 2009 at 9:35 pm

    #1008987

    removed

Viewing 15 posts - 1 through 15 (of 23 total)

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