Using Files and Filegroups to Manage Database Growth
When creating a database using files and filegroups, you must specify an initial size for the file. Microsoft® SQL Server™ 2000 creates the data files based on the size you provide. As data is added to the database, these files become full. However, you must consider whether and how the database will grow beyond the initial space you allocate if more data is added to the database than will fit in the files.
By default, SQL Server allows the data files to grow as much as necessary until disk space is exhausted. Therefore, if you do not want the database files to be allowed to grow any larger than when they were initially created, this must be specified at database creation time using SQL Server Enterprise Manager or the CREATE DATABASE statement.
Alternatively, SQL Server allows you to create data files that are allowed to grow automatically when they fill with data, but only to a predefined maximum size. This can prevent the disk drives from running out of disk space completely.
Recommendations
When you create a database, make the data files as large as possible, based on the maximum amount of data you expect in the database. Permit the data files to grow automatically but place a limit on the growth by specifying a maximum data file growth size that leaves some available space on the hard disk. This allows the database to grow if more data is added than expected, but does not fill up the disk drive. If the initial data file size is exceeded and the file starts to grow automatically, reevaluate the expected maximum database size and plan accordingly by adding more disk space (if necessary) and creating and adding more files or filegroups to the database.
However, if the database is not supposed to expand beyond its initial size, set the maximum growth size of the database to zero. This prevents the database files from growing. If the database files fill with data, no more data is added until more data files are added to the database or existing files are expanded.
Fragmentation of Files
Allowing files to grow automatically can cause fragmentation of those files if a large number of files share the same disk. Therefore, it is recommended that files or filegroups be created on as many different available local physical disks as possible. Place objects that compete heavily for space in different filegroups.