Factors deciding Database Size
-
I was creating a database by using query
CREATE DATABASE 'XYZ' ON PRIMARY
( NAME = N'XYZ', FILENAME = N'c:\_temp\XYZ.mdf' ,
SIZE = 10MB , MAXSIZE = 8GB, FILEGROWTH = 1MB )
LOG ON
( NAME = N'XYZ_log', FILENAME = N'c:\_temp\XYZ_log.ldf' ,
SIZE = 10MB , MAXSIZE = 2GB , FILEGROWTH = 10%)
Question arised in my mind.... in normal practice we we dont keep much attention on Size, Maxsize, filegrowth......
but when we create database for production.... which factors are considered.... I have never created any database for production environment. but soon i will get a chance....
Sagar Sonawane
** Every DBA has his day!!:cool: -
3 recommendations... just my practice...
1. Never use % for growth. It's too small when the DB is small and too big when it gets large. Always use a fixed number of MB. I use somewhere between 100 and 500 MB depending on the situation that I expect.
2. Almost never use a growth of less than 100 MB for any DB you expect to grow over 100 MB. The default of 1 MB will create nearly 1000 fragments just to get to 1 GB.
3. Plan the intial size of your database to hold at least 1 year of data. DB growth should never take you by surprise because it ALWAYS happens at the worst possible time.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
The general recommendations are right, but in real life every database will be different. If this is vendor's application that requires a database you can ask software vendor what should be an initial size of the database, how many changes the database will have and what potential size of the database you will have let's say in 1 year. Usually vendors have pretty good idea about databases growth.
Take these numbers and apply best practices mentioned by Jeff.
-
Deciding on the database size is one of the critical part of database design. It involves following important factors:
1. Transcation rate on database (number of transcation)
2. Size of individual tables
3. Expected growth of tables
4. Index Sizes
5. Expected addition of indexes to the existing list
6. Ammount of data push/pull from database (BULK operations)
Taking above parameters into consideration we can decide on the database size. Since database size is a combination of data file and log file size, we need to consider the transcation rate and RECOVERY model of the database when deciding on size.
Based on transcation happeneing on the database decide on the expected growth of tables for next one year and keep the MAXSIZE parameter equal to that.
Yes setting the FILEGROWTH to MB is more friendlier than setting it as % growth.
While setting GROWTHRATE for t-log file dont keep the low values which may have adverse effect on performance (leads for creation of too many VLFs)
SQLforU
info@sqlforu.com
For online training on SQL Server and Sybase, please contact
contact@sqlforu.com
www.sqlforu.com
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply