Database Growth

I have collects some facts related to Database growth and how it occurs. I think it will be iterated to know these facts.

SQL Server version 7.0 and later support growing database and log files automatically. Although this option is convenient and reduces the workload of the database administrators, growing database files can impose considerable overhead on the database server and thereby degrade performance. SQL Server 2000 and 2005 configure auto-grow options differently. With SQL Server 2000, each file you create is configured to grow without the upper size limit by 10% of its current size each time it needs to grow. For small to medium size databases, this setting works fine. However, what happens when the database file grows to be 150GB? Next time it needs to grow SQL Server will have to allocate 15GB of storage. This will be a very time and resource intensive operation and could bring your server to its knees. Also note that since the file is configured to grow automatically without the upper limit, it can fill up the entire drive (or drive array) where it resides. This might be disastrous if the database is completely full and no transactions can be committed. You'll encounter unplanned downtime until you create additional data files. 

With SQL Server 2005 each newly created file is automatically configured to grow by 1MB each time it needs to grow without an upper limit. This configuration is safer for large scale applications; however, if you're truly serious about performance, never let your database files grow automatically. Instead, estimate the growth potential for your data files before deploying the application in the production environment. Create the initial database files with plenty of free space. Then, monitor the available space regularly and either add new files or grow existing files manually during minimal user activity. If you must configure database files to grow automatically, be sure to allow plenty of room for growth for each file. If you expect your database to grow to 100GB, start with an 80GB file and then configure the file to grow by 500MB at a time. It is also important to configure the upper limit for each file so your drives don't fill up. 

Note that if you have multiple files in the file-group, the files grow automatically only after all files are full. For example, let's say you have files A, B, C, and D in your SECONDARY file-group. Each file is configured to grow by 100MB automatically and initially each file is 500MB. As data is added to this file group the files will be populated in round-robin fashion. Even if file A fills up it will not grow until files B, C and D are also full.

Posted By: MR. JOYDEEP DAS