Database file growth settings

  • Dear All,

    Iam afraid, since all the databases in our organization have been set with defaul file growth settings ( 10% ,unrestricted growth) and database's size nearly 250-400 GB.

    1. Please advise whether it's fine and also provide me if you have any related document about file growth settings.

    2. MODEL database used space is 100%[ 1 MB, unrestricted growth ] & MSDB database used space is 95% [10% ,unrestricted growth ]. Please advise how can I reduce data file size or no need since autogrowth was already set? please advise.

    Thanks and Regards,

    Ravichandra.

  • Approach depends database to database.

    the database which is being updated very frequently(kind of highly OLTP database), % growth is not recommended. Its better to have autogrowth with fixed size* and unrestricted growth.

    * you can see how quickly your file are growing and can decide accordingly on fixed autogrowth size.

    ----------
    Ashish

  • Thanks.

    But could you advise how I can analyze/monitor the growth of the files? And also suggest ,is there any documents related to this?

    I think, I didn’t get answer for my previous question that, what is the action required from my side if used space of the data(mdf) file is reached to 98-100% of the production database.

    Best Regards,

    Ravichandra.

  • But could you advise how I can analyze/monitor the growth of the files? And also suggest ,is there any documents related to this?

    if you google it, you will find the script which tells the growth of your data. I did in past and found it on one of the sql forum. Unfortunately the script is no more with me.

    I think, I didn’t get answer for my previous question that, what is the action required from my side if used space of the data(mdf) file is reached to 98-100% of the production database.

    you need to either increase the disk space where you have your mdf or need to move mdf(using attach/dettach) to new location where you have sufficient space for it to grow.

    ----------
    Ashish

  • But could you advise how I can analyze/monitor the growth of the files? And also suggest ,is there any documents related to this?

    Do you have access to the Internet????

    Top searches from Bing:

    http://sqlserverpedia.com/blog/sql-server-bloggers/monitoring-log-file-growth-using-perfmon/[/url]

    http://technet.microsoft.com/en-us/library/ms365418.aspx

    Shawn Melton
    Twitter: @wsmelton
    Blog: wsmelton.github.com
    Github: wsmelton

  • Shawn Melton (9/23/2010)


    Do you have access to the Internet????

    Was that supposed to be a trick question? 😎



    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]

  • Yes, I have access to the internet.

    Please advise on my previous question regarding mdf file size growth reached to 100%.

    Thanks and Regards,

    Ravichandra.

  • i have just read another very good article related to moving the database and advice you to have a look into it.

    http://www.sqlservercentral.com/articles/Administration/65896/

    article describe why using alter database is preferred on detach/attach.

    ----------
    Ashish

  • Thanks Ashish.

    But I think, this is not answer for my question?

    please advise on my question about mdf file size is reached to 100%.

    Regards,

    Ravichandra.

  • But I think, this is not answer for my question?

    think again, your answer is already answered

    Approach depends database to database.

    the database which is being updated very frequently(kind of highly OLTP database), % growth is not recommended. Its better to have autogrowth with fixed size* and unrestricted growth.

    if you have disk space available on that drive then you need to modify the autogrowth of your mdf. else need to move files to some other disk where you have sufficient space and then configure the autogrowth as suggested.

    ----------
    Ashish

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

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