Expanding and Shrinking Databases

Databases can be expanded and shrunk either automatically or manually. The mechanism for automatic expansion is completely different from the mechanism for automatic shrinkage. Manual expansion is also handled differently than manual shrinkage . Log files have their own rules for growing and shrinking, so we'll discuss changes in log file size in a separate section.

Automatic File Expansion

Expansion of a database can happen automatically to any one of the database's files when that particular file becomes full. The file property FILEGROWTH determines how that automatic expansion will happen. The FILEGROWTH specified when the file is first defined can be qualified using the suffix MB, KB, or % and will always be rounded up to the nearest 64 KB. If the value is specified as a percent, the growth increment is the specified percentage of the size of the file when the expansion occurs. The file property MAXSIZE sets an upper limit on the size to which a file can grow.

Manual File Expansion

Manual expansion of a database file is accomplished using the ALTER DATABASE command to change the SIZE property of one or more of the files. When altering a database, the new size of a file must be larger than the current size. To decrease the size of files, you must use other commands.

Automatic File Shrinkage

The database option autoshrink allows a database to shrink automatically. The effect is the same as doing a DBCC SHRINKDATABASE (dbname, 25) . This option leaves 25 percent free space in a database after the shrink, and any free space beyond that 25 percent is returned to the operating system. The thread that performs autoshrink, which will always have server process ID (spid) 6, will shrink databases at 30-minute intervals. The DBCC SHRINKDATABASE command will be discussed in more detail momentarily.

Manual File Shrinkage

You can manually shrink a database using the following two DBCC commands:

 DBCC SHRINKFILE ( {file_name  file_id }  [, target_size][, {NOTRUNCATE  TRUNCATEONLY} ]  )    DBCC SHRINKDATABASE (database_name [, target_percent]  [, {NOTRUNCATE  TRUNCATEONLY} ]  ) 


DBCC SHRINKFILE allows you to shrink files in the current database. When target_size is specified, DBCC SHRINKFILE attempts to shrink the specified file to the specified size in megabytes. Used pages in the part of the file to be freed are relocated to available free space in the part of the file retained. For example, for a 10-MB data file, a DBCC SHRINKFILE with a target_size of 8 causes all used pages in the last 2 MB of the file to be reallocated into any free slots in the first 8 MB of the file. DBCC SHRINKFILE doesn't shrink a file past the size needed to store the data. For example, if 70 percent of the pages in a 10-MB data file are used, a DBCC SHRINKFILE statement with a target_size of 6 shrinks the file to only 7 MB, not 6 MB.


The DBCC SHRINKDATABASE command shrinks all files in a database. The database can't be made smaller than the model database. In addition, the DBCC SHRINKDATABASE command will not allow any file to be shrunk to a size smaller than its minimum size. The minimum size of a database file is the initial size of the file (specified when the database was created) or the size to which the file has been explicitly extended or reduced, using either the ALTER DATABASE or DBCC SHRINKFILE command. If you need to shrink a database smaller than this minimum size, you should use the DBCC SHRINKFILE command to shrink individual database files to a specific size. The size to which a file is shrunk will then become the new minimum size.

The numeric argument given to the DBCC SHRINKDATABASE command is a percentage of free space to leave in each file of the database. For example, if we have used 60 MB of a 100-MB database file, we can specify a shrink percentage of 25 percent. SQL Server will shrink the file to a size of 80 MB, since 25 percent of 80 is 20, meaning we'll have 20 MB of free space in addition to the original 60 MB of data. In other words, the 80 MB file will have 25 percent of its space free. On the other hand, if we have used 80 MB or more of a 100-MB database file, there is no way SQL Server can shrink this file to leave 25 percent free space. In that case, the file size remains unchanged.

Because DBCC SHRINKDATABASE is shrinking the database on a file-by-file basis, the mechanism used to perform the actual shrinking is the same as that used with DBCC SHRINKFILE. SQL Server will first move pages to the front of files to free up space at the end and then will release the appropriate number of freed pages to the operating system. Two options for the DBCC SHRINKDATABASE and DBCC SHRINKFILE commands can force SQL Server to do either one or the other of the two steps just mentioned:

  • The NOTRUNCATE option causes all the freed file space to be retained in the database files. SQL Server only compacts the data by moving it to the front of the file. The default is to release the freed file space to the operating system.
  • The TRUNCATEONLY option causes any unused space in the data files to be released to the operating system. No attempt is made to relocate rows to unallocated pages. When TRUNCATEONLY is used, target_size and target_percent are ignored.
BCC SHRINKFILE specifies a target size in megabytes. DBCC SHRINK DATABASE specifies a target percentage of free space to leave in the database.

Inside Microsoft SQL Server 7.0
Inside Microsoft SQL Server 7.0 (Mps)
ISBN: 0735605173
EAN: 2147483647
Year: 1999
Pages: 144

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net