Database Sizing Question

  • Hi All,

    Elementary question: Every book I've read on determining the size of a DB you're designing says the same thing: figure out how big your DB will be so you can specify this in the Create Database statement. BUT, a database constantly grows as new records are added, so, how big do I create the .mdf file over WHAT period of time? A year? I have a DB that will have roughly 40MB stuffed into it the first year of use. Adding 50% for indexes, do I create the DB initially with 60MB? Also, is there a good formula to decipher filegrowth, or do I just throw out 20% as a starting point?

    I'm a bit of a newbie, so any suggestions would be very much appreciated.

    Thanks!

  • File growth is something you need to track over time. You might try to build a tracking method using the output of any one or more of the following:

    sp_helpdb,

    DBCC SHOWFILESTATS

    xp_getfiledetails

    DBCC SQLPERF ( LOGSPACE )

    or some other method of getting the actual file sizes of your databases.

    -------------------------

    Gregory Larsen, DBA

    If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples

    Gregory A. Larsen, MVP

  • Thanks Greg. Actually, I'm trying to determine how big to designate the database... it isn't created yet. If you would, please read my first post again. When documentation says "figure out how big your DB will be", do they imply over a year's time? This is the $64,000.00 question that I can't seem to get answered. Help!

  • I'm not sure if there is a set timeframe to measure growth. I have not come accross one.

    You mentioned it will grow to 60Mb with all your indexes. I'd set the Data file to 100Mb. Give yourself a bit of breathing space.

    If you monitor your DB's regulalry you will soon work out an average of growth for a set period. As you only estimate 40Mb growth, there is not a great deal of INSERTS going on. If you monitor daily, you will soon notice an average growth. This will help in future upsizing.

    SQL Server makes this so easy to monitor. EM is perfect for this by showing the Total DB Size and also the percentage used.

    Clive Strong

    clivestrong@btinternet.com

  • Thanks Clive. Given today's HD capacities, mapping out 100mb for this thing is really a drop in the bucket. Other than HD space, allotting a large amount for a DB file really isn't an issue I suppose...

  • That will also depend of how amny transactions do you expect, and what type also. If you expect, many inserts, the growth in a period of time, will be different for selects.

    I don't think you will find an exact formula of what to do with this.

    If you can't determine how much will it growth and you will be administering the database all day (and can check for database size), then don't create the database with a very big size at begining.

  • Racosta,

    Is there any drawback to creating the database at 100mb as a starter, instead of, say, the "default" 10mb or 20mb? In other words, if HD space was not an issue, and I thought that I might have 30 or 40mb of data inserted in the first year (with read/write being 50-50), is there any problem or issue starting out my DB at 100mb or even larger? I may not be in a position to monitor this DB on a daily basis, unfortunately.

  • You will loose in performance if you have to growth the size of the data files frecuently, because SQL must ask for space to the system and allocate all the pages.

    Is very provably that the space that you specified for your db will not be enough sooner or later. So I recommend that you create the db with more space than what you think that you will use. The size you specified is correct for me.

    Also is not recomendable to let the Db to growth automatically (much less in a production server) because once the Hard Disk becomes full, all transactions won't run.

    If you want to let the Db to growth automatically, don't specified a low incremental value of growth. It is much better that SQL reclaims the system for space not frecuently.

  • Good advice... Thanks!

  • Agree with racosta. I prefer to give the data & transaction log plenty of space and if they need resizing, doing it out of hours. Fortunately, we don't run a 24hr production site, so thats easy for me.

    No harm in giving your Data file plenty of space, especially as you won't get to monitor it often...with that in mind, I'd advise plenty of space to grow. Also give your Transaction Log plenty of space to save it growing automatically during production hours. You could start at say 50Mb. Of course, don't forget to include a maintenance plan to backup your DB/Logs. If you don't back up your logs, they'll keep on growing until they eat up your diskspace!

    Clive Strong

    clivestrong@btinternet.com

  • Thanks Clive! I was thinking of starting the main file at 100MB with a filegrowth of 60MB (or should I just do 100MB?). I figure it'll take them 18-24 months to hit that first 100MB.

    Also, if I start the log at 50MB, do I also do a 50MB filegrowth for that?

  • That sounds like a good place to start for both data & log files. If of course you notice them filling up too quickly you can resize them manually (rather than have SQL Server do it - my personal choice).

    Clive Strong

    clivestrong@btinternet.com

  • For the transaction log I would consider 20mb on a 100MB database as bare min. Now depending on how often you perform full backups I would measure 3 times the size of the file between those dates and make sure file truncates right after a TL backup and before a FULL backup. Get an average and set file growth to difference between average and largest size.

    Just a perspective item here but I have a database which is 75MB of data but the number of transactions causing the log file to be 400mb between weekly backups and the largest ever reached was 1GB for the log while the DB was 75MB roughly. So you really never know and listening to a set value without testing does not bennefit you.

    "Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

  • That's a good chunk of advice, Antares. Thanks!! (I was wondering when you were going to show up...)

    : )

  • the best way to determine the future size of your database is to compute it!

    the formula for computing the database size for sql server 7 (i guess it applies to 2000 as well) can be found in microsoft's web site (i forgot where exactly).

    what i personally do, is to use the formula for 6.5 even for 7 and beyond. 6.5 is less efficient at storage than 7 or above, so using 6.5's formula gives me an automatic allowance. the 6.5 formula is in the 6.5 books online appendix. you can also see http://www.bg.ic.ac.uk/SDG/Books/UsingSQLServer/ch17.htm. we use a quick and dirty excel worksheet that we developed where we just plug in the necessary values and it computes the size automatically.

Viewing 15 posts - 1 through 15 (of 17 total)

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