Database Sizing in SQL Server 7.0

Dynamic Database Sizing in SQL Server 7.0

Fixed upper limits to database size frustrate database administrators (DBAs) using SQL Server 6.5. You must pay close attention to know when a database is nearing capacity, and then give it more space. If you're unlucky, you won't notice the warning signs. You'll discover a database has reached capacity when users start complaining about not being able to insert or update data.

Increasing the size of a database, even when you know an increase is necessary, has gotchas. You must expand a database onto a device, and any available devices might not have room for the expansion. In that case, you must enlarge a device or create a new one.

Microsoft changed space management in SQL Server 7.0, making your life easier. You don't need to worry about devices, and databases can grow and shrink automatically.

No More Devices

You don't need to create SQL Server 7.0 databases on SQL Server logical devices. SQL Server 7.0 has changed the syntax for the CREATE DATABASE statement to let you specify the disk files you want to use for your database. SQL Server 7.0 creates each database on at least two disk files, one for the data and one for the log. Unlike previous versions of SQL Server, version 7.0 never stores the data and log together. Unlike SQL Server 6.5, more than one database can never use the same file.

The CREATE DATABASE command lets you specify the disk location of the files you want the new database to use. For each file, you can specify values for several properties, including the initial size of the file, whether it will automatically grow as needed, in what increments it will grow, and a maximum size.

Listing 1 shows a SQL Server 7.0 CREATE DATABASE command that creates a database called MyDB with two 100MB data files and one 100MB log file. If you do not specify the LOG ON clause, SQL Server 7.0 automatically creates a log file 25 percent the size of the sum of all the data file sizes. SQL Server 7.0 places the file for the log in the default data directory (mssql7data), and the file name will be name of database_log.ldf. If you do not specify MAXSIZE, the file will grow until the disk is full. You can specify SIZE and MAXSIZE in MB (the default) or KB.

You can specify FILEGROWTH in MB (the default) or KB, or as a percentage of the size of the file at the time the growth needs to occur (not as a percentage of the initial size of the database). As in Listing 1, page 31, you can enter FILEGROWTH = 20% to specify a 20 percent increment. If you don't give an increment, the default FILEGROWTH is 10 percent of the current size. SQL Server 7.0 rounds up each specified size to the nearest 64KB. If you want no file growth, specify 0 for the FILEGROWTH property.

If you specify no filenames, SQL Server will create the database on two files using the following naming convention:

You can control all these database features with a GUI in the new Enterprise Manager. If you have installed SQL Server 7.0, look at the dialog box for creating a new database. From the main console in Enterprise Manager, right-click the database folder and select New Database. Screen 1 shows the dialog box you'll see.

Automatic Database Sizing

SQL Server 7.0 databases can grow and shrink automatically. Each file grows whenever that file reaches capacity. The MAXSIZE and FILEGROWTH file properties control the automatic growth. The File Properties options in the dialog box in Screen 1 let you choose a growth increment and a maximum size. Also, you can use the CREATE DATABASE statement to specify these values.

Log files can grow automatically in the same way data files grow, because log files also have MAXSIZE and FILEGROWTH properties. You use the Database Properties dialog box to set the transaction log properties. You can change these properties for any file by right-clicking the name of the database in the left panel of the Enterprise Manager and selecting EDIT, or by using the ALTER DATABASE command. The online documentation provides details for using the command.

As in SQL Server 6.x, SQL Server 7.0 usually allocates space for tables and indexes in units of 8 contiguous pages, or extents. If your database contains more than one data file, SQL Server allocates space from the files in a proportional-fill manner. For example, if you have one file with twice as much free space as a second file, SQL Server will allocate two extents from the first file for each extent it allocates from the second file.

The Autoshrink option lets a database shrink automatically. You set this option through the Query Analyzer, which is a basic tool that lets you send any SQL command to SQL Server. To use the system procedure sp_dboption, type

sp_dboption 'mydb', 'autoshrink', true

SQL Server provides a special thread for performing autoshrink. The thread that performs autoshrink will shrink databases at 30-minute intervals. The automatic shrinking operation will attempt to shrink the database to a size that includes 25 percent free space. Autoshrink returns any space beyond 25 percent to the OS.

To shrink data files, SQL Server moves data to the beginning of the files. Within an index, SQL Server will move whole pages so that the rows stay in their properly sorted order. SQL Server will adjust page pointers to link the moved pages to the correct sequence in the table. For data from tables without a clustered index, in which the rows have no implied ordering, individual rows can move to wherever the file has room.

Automatic shrinking of log files works differently from automatic shrinking of data files. Automatic shrinking of the log is possible only if the log has free space. The two ways to get free space are to do a backup or to use the sp_dboption stored procedure to set truncate log on checkpoint to true. When the autoshrink process kicks in every 30 minutes, it determines the size to shrink the log to. The autoshrink process can determine the size because SQL Server accumulates statistics on the maximum amount of log space used in the 30-minute interval between shrinks. The autoshrink process sets the shrink point of the log to 125 percent of the maximum log space used or to the minimum size of the log, whichever is larger. (The minimum size is the creation size of the log unless the DBA has specifically increased the size.)

Options for DBAs

SQL Server 7.0 makes your job much easier by providing options for dynamic database sizing. Databases can grow and shrink automatically, but you can set growth limits and thereby control the space available on the disks.