Microsoft SQL Server 2000 High Availability

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.

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.

Категории