Post reply

Manual growth of data and log files

  • September 26, 2008 at 12:40 am

    #124818

    Hi,

    I don’t think I am getting this manual growth concept.

    I am suppose to manually grow our data and log file and I am suppose to make the sizes big enough so I only have to manually grow them once a month.

    I have tried 2 methods and none seem to work - my log file still fills up and every night I get the message: The transaction log for database 'abc' is full.

    Method 1: Right click database - properties - Files - I increase the size of the "Initial Size(MB)" column, for example: 5000.

    During the night when processes run - they fail with message: The transaction log for database 'ABC' is full. When I get back the next morning - this "Initial Size" value has gone back to 1. Why is this happening?

    Method 2: Using T-SQL here is an example of what I did:

    ALTER DATABASE ABC

    MODIFY FILE (NAME = 'ABC_log',

    MAXSIZE = 5GB)

    But no matter what I try - I keep getting the error of the log being full.

    Can anyone simplify this for me PLEASE? 🙂

    Thanks

  • September 26, 2008 at 1:09 am

    #877457

    If your initial size has gone back to 1, you probably have autoshrink on. If you don't allow autogrowth (which i don't recommend), don't enable shrink (you'll only have the optimal logfile size until the shrink happens)

    I recommend autoshrink, with a maxfilesize. This prevents you from messages like you get and you'll have the guarantee the file won't blow up your disk.

    What you also can do is create an performance alert for "% logfile full" and as an action increase your logfile, but I realize this is the same behavior as autogrowth 😀

    Wilfred
    The best things in life are the simple things

  • September 26, 2008 at 1:31 am

    #877467

    Hi,

    I have checked and AutoShrink = False.

    Here is some more info (in case)

    SQL 2005

    The Recovery mode = Full for some and Simple for others.

    Backups occur every night

    Transaction log backups every 10 min for the DBs with Full Recovery model.

    I also do not agree with manual growth - but unfortunately have not much say in this 🙁

    Any more advice?

  • September 26, 2008 at 1:38 am

    #877469

    Ok there was a Shrink DB option in the Maintenance plan that executed every night at 12:00

    I have disabled that.

    I will keep an eye on it - see if this sorts out my problem

    THANK YOU!

  • September 26, 2008 at 1:46 am

    #877470

    OOPS, type error:

    I recommend autoshrink, with a maxfilesize. This prevents you from messages like you get and you'll have the guarantee the file won't blow up your disk.

    It should be:

    I recommend autogrowth, with a maxfilesize. This prevents you from messages like you get and you'll have the guarantee the file won't blow up your disk.

    Sorry for the confusion

    Wilfred
    The best things in life are the simple things

  • September 26, 2008 at 2:13 am

    #877480

    MV (9/26/2008)

    Hi,

    I have checked and AutoShrink = False.

    If the log file isn’t set to auto shrink, then someone is shrinking it. Check it you have a job that shrinks the log file. You can also run trace with the event “log file auto shrink” to see more details about the shrink operation (who does it, when he does it, etc’).

    Adi

    --------------------------------------------------------------
    To know how to ask questions and increase the chances of getting asnwers:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

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

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