locked
File growth size RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    Dear All,

    I wanted to create some databases but I wanted to know how to determine how much file frowth to specify in MB please?

    Thank you in advance.

    Friday, November 29, 2013 1:28 PM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote

    Dear All,

    I wanted to create some databases but I wanted to know how to determine how much file frowth to specify in MB please?

    Thank you in advance.

    Yes, just as others said, it really depends on your  db\app is for. however,  I would suggest you to read Paul Randal's effective database maintenance tips and one of the topic is about "database and log file managament"..

     refer http://technet.microsoft.com/en-us/magazine/2008.08.database.aspx

    Hope it Helps!!

    • Marked as answer by Sofiya Li Friday, December 6, 2013 6:12 AM
    Friday, November 29, 2013 3:07 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Dear All,

    I wanted to create some databases but I wanted to know how to determine how much file frowth to specify in MB please?

    Thank you in advance.

    Hello,

    How much Autogrowth value to choose is really a tough question to answer unless database growth and other stats are available.It depends on business logic and disk space present on your system.

    Identify how much your data grows within week or month see daily backup size to get the exact value.You should not set it too low such that file has to add more files too frequently and not too high such that when you look at DB free space it holds unnecessary free space

    http://support.microsoft.com/kb/315512?wa=wsignin1.0

    https://www.simple-talk.com/sql/database-administration/sql-server-database-growth-and-autogrowth-settings/

    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers


    • Edited by Shanky_621MVP Friday, November 29, 2013 3:47 PM
    • Proposed as answer by Sofiya Li Monday, December 2, 2013 2:20 AM
    • Marked as answer by Sofiya Li Friday, December 6, 2013 6:12 AM
    Friday, November 29, 2013 3:46 PM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi,

    it depends on what the database is intended for.

    Autogrowth can be a performance issue, so it should not grow too often. The setting should consider the amount of daily data growth. You could choose a setting that autogrowth is only done once or twice a day.

    Alex

    Friday, November 29, 2013 1:33 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    it depends upon your bussiness logic.

    you can have liner growth or compound growth

    Ramesh Babu Vavilla MCTS,MSBI

    Friday, November 29, 2013 1:39 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Generally If db size is very big and growing fast then put in % else put mb for small databases.
    Friday, November 29, 2013 2:21 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Dear All,

    I wanted to create some databases but I wanted to know how to determine how much file frowth to specify in MB please?

    Thank you in advance.

    Yes, just as others said, it really depends on your  db\app is for. however,  I would suggest you to read Paul Randal's effective database maintenance tips and one of the topic is about "database and log file managament"..

     refer http://technet.microsoft.com/en-us/magazine/2008.08.database.aspx

    Hope it Helps!!

    • Marked as answer by Sofiya Li Friday, December 6, 2013 6:12 AM
    Friday, November 29, 2013 3:07 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Dear All,

    I wanted to create some databases but I wanted to know how to determine how much file frowth to specify in MB please?

    Thank you in advance.

    Hello,

    How much Autogrowth value to choose is really a tough question to answer unless database growth and other stats are available.It depends on business logic and disk space present on your system.

    Identify how much your data grows within week or month see daily backup size to get the exact value.You should not set it too low such that file has to add more files too frequently and not too high such that when you look at DB free space it holds unnecessary free space

    http://support.microsoft.com/kb/315512?wa=wsignin1.0

    https://www.simple-talk.com/sql/database-administration/sql-server-database-growth-and-autogrowth-settings/

    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers


    • Edited by Shanky_621MVP Friday, November 29, 2013 3:47 PM
    • Proposed as answer by Sofiya Li Monday, December 2, 2013 2:20 AM
    • Marked as answer by Sofiya Li Friday, December 6, 2013 6:12 AM
    Friday, November 29, 2013 3:46 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Thank you for your reply all! I have a database which is only about 2g but the log file is 1.5g and I'm not sure if that is normal. The database is set to "full" recovery model and I am taking transactional log backup which is done every 15 minutes so I'm not sure what I can do?

    Thank you

    Tuesday, December 17, 2013 12:04 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Log backup is very different from data files backup. Log backup does not copy log file, but only a portion of it: from the LSN of the last log backup. So, you can have a huge transaction log, but very small transaction log backup.

    In BULK LOGGED recovery model, because of extents modified by minimally logged operations, you can have the opposite situation: small db log, and big log backup. But that is rare case in practice.

    For the rule of thumb on how to properly size transaction log, see this post: http://blog.sqlxdetails.com/set-transaction-log-initial-size/


    Tuesday, December 17, 2013 12:47 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Thank you for your reply all! I have a database which is only about 2g but the log file is 1.5g and I'm not sure if that is normal. The database is set to "full" recovery model and I am taking transactional log backup which is done every 15 minutes so I'm not sure what I can do?

    Thank you

    Hello Ti2,

    Please create a separate thread for your new question.Its not a good idea to reply on old post reason why is since it is marked as answer visibility becomes less and Visibility of new thread is always high and plus you get all new person to answer your question

    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Tuesday, December 17, 2013 1:20 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi,

    Its all upto your database usage and growth, if you think that you database will grow 1 GB daily or 10 monthly then give 5 GB initial size and autogrowth set to 1 GB or 2GB. its good to have large value in autogrowth instead of having small values in MB's in highly used system, if your DB grows only 1 GB or less than that monthly then you can set 100 to 500 MB autogrowth.

    hope this give some idea.

    Raju Rasagounder MSSQL DBA

    Tuesday, December 17, 2013 2:33 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Would it be best to run the following?

    DBCC SHRINKFILE(transactionloglogicalfilename, TRUNCATEONLY)
    Tuesday, December 17, 2013 4:11 PM