September 16, 2002 at 8:50 am
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!
September 16, 2002 at 9:00 am
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
September 16, 2002 at 9:06 am
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!
September 16, 2002 at 9:25 am
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
September 16, 2002 at 10:31 am
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...
September 17, 2002 at 4:26 pm
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.
September 17, 2002 at 5:44 pm
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.
September 17, 2002 at 6:31 pm
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.
September 17, 2002 at 8:20 pm
Good advice... Thanks!
September 18, 2002 at 4:17 am
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
September 18, 2002 at 9:02 am
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?
September 18, 2002 at 9:34 am
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
September 19, 2002 at 4:25 am
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)
September 19, 2002 at 9:37 am
That's a good chunk of advice, Antares. Thanks!! (I was wondering when you were going to show up...)
: )
September 19, 2002 at 12:07 pm
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