When creating a database using files and filegroups, you must specify an initial size for the file. Microsoft® SQL Server™ creates the data files based on the size you provide. As data is added to the database, these files become full. However, you need to consider if 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 the database files should not be allowed to grow any larger than when 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.
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 will allow the database to grow if more data is added than expected, but will 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 must never be allowed to grow beyond its initial size, set the maximum growth size of the database to zero. This will prevent 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.
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.
| ALTER DATABASE | CREATE DATABASE |