The most important element of a database is its data, which is stored in data files. The size of these data files affects database availability, performance, and support. Effectively managing their size involves:
- Keeping size in mind when creating the database.
- Creating additional file groups for nonsystem objects.
- Proactively managing database growth.
To demonstrate this practical approach to managing the data file size, I'll walk you through an example. You can follow along by downloading the 129260.zip file, which contains the code and the other files I discuss. Go to the top of this page and click the Download the Code Here button.
Creating the Database
Creating databases in SQL Server is easy. In its simplest form, you can issue the CREATE DATABASE statement, with the database name as the only parameter. SQL Server will create the database using the default settings for various other parameters. Although the simplicity of this approach is appealing, it's best avoided in production systems.
One parameter that you need to define is SIZE. How large should you make the database? SQL Server Books Online (BOL) makes this recommendation: "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". In other words, the initial file size should match the expected size of the database. To estimate the expected database size, you need to estimate the size of the tables and their indexes. The "Estimating the Size of a Database" web page can help with these estimations.
In most production systems, database size is an upward moving target, so you also need to factor in expected growth. Again, BOL offers some guidance: "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". To do that, you need to define the FILEGROWTH and MAXSIZE parameters. By default, the database will automatically grow in 1MB increments in SQL Server 2005 and later. (In earlier versions, the default is 10 percent.) However, you should determine whether this growth is acceptable for your database and, if necessary, customize the autogrow setting by using the FILEGROWTH parameter. In "Considerations for the 'Autogrow' and 'Autoshrink' Settings in SQL Server", Microsoft notes that the general rule is to set it to one-eighth the size of the file for testing purposes. The FILEGROWTH parameter's value can be expressed in bytes or as a percentage.
The MAXSIZE parameter's value should be smaller than the size of the disk hosting the file. By "disk" I mean whatever storage you use. These days this is often a SAN mount point.
The code in Listing 1 includes a CREATE DATABASE statement that defines the SIZE, FILEGROWTH, and MAXSIZE parameters. As callout A shows, the TestDB database is initialized to 10MB and will grow in 15 percent increments until it reaches 500MB.
Note that when a database reaches full capacity, any attempt to insert or update records will result in an error, so MAXSIZE isn't something that you should ever allow to be reached. Even before the file is full, you should avoid relying solely on autogrow. The main reason is that a transaction requiring the file to be expanded has to wait for this to happen before it's committed. Transactions that usually execute quickly could suddenly take a long time—as long as it takes for the I/O-intensive file growth operation to complete. This can have negative implications on performance and could even result in deadlocks and timeouts. Indeed, in "Considerations for the 'Autogrow' and 'Autoshrink' Settings in SQL Server," Microsoft states, "For a managed production system, you must consider autogrow to be merely a contingency for unexpected growth. Do not manage your data and log growth on a day-to-day basis with autogrow."
If autogrow is bad, autoshrink is even worse, especially since it's often unnecessary. After all, a database will most likely have to grow again after being shrunk. As a general rule, the autoshrink option should be turned off in all production systems and file shrinking should be done only when absolutely necessary. This should always be done during a maintenance window and index defragmentation should be performed afterward.