Using Files and Filegroups to Manage Database Growth
When you create a database, you must either specify an initial size for the data and log files or accept the default size. 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, the data files grow as much as required until no disk space remains. Therefore, if you do not want the database files to grow any larger than when they were first created, this must be specified at the time the database is created by using SQL Server Management Studio or the CREATE DATABASE statement.
Alternatively, SQL Server lets you create data files that can 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 put a limit on the growth by specifying a maximum data file growth size that leaves some available space on the hard disk. This lets the database 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, re-evaluate the expected maximum database size. Then, plan accordingly by adding more disk space, if required, 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 until the existing files are expanded.
Fragmentation of Files
Letting files grow automatically can cause fragmentation of those files if several of them share the same disk. Therefore, you should create the files or filegroups on as many different local physical disks as you can. Also, put objects that compete heavily for space in different filegroups.
See Also
Concepts
Using Files and Filegroups
Read-Only Filegroups and Compression
Other Resources
ALTER DATABASE (Transact-SQL)
CREATE DATABASE (Transact-SQL)
Designing Files and Filegroups