Database File Size

Once database file placement is worked out, one final step in the database configuration must be determined: the size of the files used for each user database, as well as tempdb. Again, what you do not know at this stage can hurt you when you move into production.

In versions of SQL Server prior to SQL Server 7.0, if you wanted to expand your database, you would have to do it by adding another segment. When you ran out of segments, you were out of luck. SQL Server 7.0 introduced the automatic growth functionality for a database, which allows a database to grow on the fly. This is a great feature, but it should be used with care, because if you accept the defaults, which are 10 percent growth and a file size of 1 MB (see Figure 4-8), you might be constantly chunking out to disk while the database is trying to continually expand as it is used.

Figure 4-8: The GUI for setting growth at database creation.

As the database grows, 10 percent growth might be sufficient, but in an initial, small database, this generally won t fit your data curve. That creates a performance and potential availability problem because a large portion of your disk I/O is dedicated to expanding your disks, not just SQL Server usage. You can alter the growth properties of a database through Enterprise Manager, or use the ALTER DATABASE Transact -SQL command with the SIZE, MAXSIZE, UNLIMITED, or FILEGROWTH options. You should not set the file growth to UNLIMITED, as it allows the file chosen to grow until the disk is full. Some might want to set a maximum file size as well. If you do use the automatic growth, set a reasonable size, either by a percentage or physical size on disk, so that the database does not have to constantly grow.

Tip

Do not set the system databases to autogrow without good reason. Extra space in the system databases (with some exceptions, such as using transactional replication heavily in msdb where it stores history as well as the transactions) is generally not needed unless you are doing some sort of version upgrade or installing a service pack, and this would be detailed in the information supplied with the software you are upgrading to. Pick a size you are comfortable with when SQL Server is first installed.

User databases What you probably care about most are the databases for your applications, whether third party or custom. The initial size of your database files should cover how much data you expect to have in the database for a time period of your determination plus some reasonable amount of growth.

Tempdb Provides important functionality to SQL Server ” especially for queries ”so its size must be properly set. Do you know if your applications use tempdb heavily? If not, find out. If tempdb is continually expanding to meet your needs, you will always experience disk contention for tempdb, and if that file is placed in the same location as your data or log files, it has a domino effect on performance. Especially if you consolidate onto fewer SQL Servers, knowing how each application uses tempdb helps you determine the size of tempdb (it needs to accommodate all workloads), and also helps you determine if the workloads will actually work well together.

Important

You can always add more space to tempdb as needed. You can also take away space, but keep in mind that you cannot reduce the size of tempdb below the size at which you initially configured it when starting the SQL Server service without stopping and restarting SQL Server.

You can use SQL Server s alerting capabilities to warn you when the file size for your database equals, exceeds, or falls below a certain size, as shown in Figure 4-9. This is a great way to notify you that there might be an upcoming problem, and if you have set a hard maximum size, you can take action, or let the alert take an action, such as issuing an ALTER DATABASE statement. See Chapter 14, Administrative Tasks to Increase Availability, or Chapter 15, Monitoring for High Availability, for more information.

Figure 4-9: Configuring an alert for file size.

Shrinking Databases and Files

You can also decrease the size of your database and database files if, for example, you created them too large and have not experienced the growth you planned for. Like automatic growth, this should be done with care, as it might cause excessive disk I/O that will affect performance and possibly availability. To access the functionality in Enterprise Manager, right-click on a database, select All Tasks, and then select Shrink Database (see Figure 4-10).

Figure 4-10: Shrinking a database in Enterprise Manager.

To shrink a database using Transact-SQL, use the DBCC SHRINKDATABASE command. DBCC SHRINKDATABASE shrinks all data and log files for a specified database. If you only want to shrink a particular data or log file, you can issue a DBCC SHRINKFILE command. Whether you shrink the entire database or just one file, it shrinks to the specified amount of free space you designate . You can also set the AUTO_SHRINK property ON or OFF for the database. If set to ON, this property periodically shrinks the database.