locked
Unable to make unrestricted file growth of LDF in sql 2008 RRS feed

  • Question

  • Hello Everyone,

    I am unable to make unrestricted file growth of LDF in sql 2008. When I goto Database Properties > Files and open AutoGrowth window, I set Maximum file size to "Unrestricted File Growth" and click OK  for both windows (AutoGrowth and Database Properties). But upon re checking file growth setting, I found that file growth agains set to Restrickted File Growth....

    Any clues how to resolve this issue ?

     

    Thanks


    Muhammad Tariq khan
    Friday, January 27, 2012 11:51 AM

Answers

  • I suspect that you are seeing a Restricted File Growth (MB) of 2,097,152.

    If so, this is expected as it is the maximum size for a LDF file.

    Saturday, January 28, 2012 6:53 PM

All replies

  • I suspect that you are seeing a Restricted File Growth (MB) of 2,097,152.

    If so, this is expected as it is the maximum size for a LDF file.

    Saturday, January 28, 2012 6:53 PM
  • http://www.sqlskills.com/BLOGS/KIMBERLY/post/Transaction-Log-VLFs-too-many-or-too-few.aspx
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Sunday, January 29, 2012 6:54 AM
  • Dear KevinNicholas,

    Yes you are right... My LDF is 2,097,152 MB in size by default. Is this is the maximum allowed limit (i.e. we cant change it to infinite) ?


    Muhammad Tariq khan
    Monday, January 30, 2012 5:47 AM
  • Its a strange one, because 2TB (the figure 2,097,152) in the max limit for a LDF, so although there is the term infinite, 2TB is basically the infinite value for a LDF. If that makes sense.

    Why it doesn't say unrestricted (like a data file which has a limit of 16TB) as opposed to 2,097,152 I'm not sure, but you could raise a suggestion or bug here http://connect.microsoft.com/

    Monday, January 30, 2012 5:59 AM
  • Hi,

    How large is your database? How are you backing up a 2TB log file? Do you use some kind SAN snap backups, etc? Just wondering out of curiosity sake. Never had a log file hit the 2TB limit, so would be interesting to here your experience.


    Sean Massey | Consultant, iUNITE

    Feel free to contact me through My Blog, Twitter or Hire Me.
    Please click the Mark as Answer button if a post solves your problem!

    Monday, January 30, 2012 7:10 AM
  • Dear KevinNicholas and Sean Massey,

    Appreciate your help and response...

    I meant to say by default LDF settings are set to 2,097,152 MB.... Actually I was getting an error related to SharePoint 2010 which states that your MDF and LDF should be set to unrestricted growth that why I put my query here for LDF settings...



     


    Muhammad Tariq khan
    Tuesday, January 31, 2012 4:57 AM