Post reply

Need to change the log file to grow un-restricted

  • August 26, 2010 at 9:18 am

    #140394

    Hello,

    I am in SQL 2008, I have adatbase in Simple Mode, and I am dbowner of the database, trying to change the log file to grow un-restricted,

    When I go through properties it is at 10% growth, and restricted to say to 2 GB, when I change to unresticted and go back to properties it goes back to same old 10%

    How I can get that done, help appreciated,

    Thanks,

    Dev

  • August 26, 2010 at 9:26 am

    #1212379

    There's nothing wrong with that. The 10% refers to the amount it will grow by each time it grows, not the amount it is allowed grow by.

    FYI, 10% (or any %) is not a good value for the growth. Each time the database grows, it will grow by a larger amount each time. Set it to a set number of Megs instead.


    Alvin Ramard
    Memphis PASS Chapter[/url]

    All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

    For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • August 26, 2010 at 9:28 am

    #1212380

    Actually I changed that to MB, but the maximum it grow is 2064 MB, I want to make it as unrestricted

  • August 26, 2010 at 11:22 am

    #1212450

    It won't be 2046 MB, it's 2046GB i.e. 2 TB and that's the maximum allowable size for a log file. Hence it's the same as unrestricted.

    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

  • August 26, 2010 at 12:23 pm

    #1212486

    Thanks actually, it was my fault, it is 2 TB,

    I actually ran the below statement to convert into un-restricted,

    ALTER DATABASE ShavlikScans

    MODIFY FILE

    (NAME = ShavlikScans_Log,

    MAXSIZE = UNLIMITED);

    GO

  • August 26, 2010 at 12:30 pm

    #1212492

    So, when you look at the file properties, what does it say now for the maximum file size?


    Alvin Ramard
    Memphis PASS Chapter[/url]

    All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

    For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • August 26, 2010 at 12:32 pm

    #1212493

    It still says restricted to 10 MB growth till 2 TB, which is I guess SQL 2005 is,

  • August 26, 2010 at 3:30 pm

    #1212577

    The maximum size that a log file can be in SQL (all current versions) is 2TB. You can look that up in Books online. Hence a log that's got unrestricted growth is actually limited to 2 TB because that's the maximum size of the log file. Hence a log with a max size of 2 TB is exactly the same as a log file that's got an unrestricted max size.

    Expecting a 2TB log any time soon?

    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

  • August 26, 2010 at 5:48 pm

    #1212653

    The Max LogSize allowed in 2TB. it is pretty big a size for a log file and i will be surprised if you are reaching that limit!

    You can specify multiple log files, similar to multiple database files.

Viewing 9 posts - 1 through 8 (of 8 total)

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