Expanding and Shrinking a Database

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 I'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 happens. The FILEGROWTH specified when the file is first defined can be qualified using the suffix MB, KB, or % and is always 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.

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 you alter a database, the new size of a file must be larger than the current size. To decrease the size of files, you use the DBCC SHRINKFILE command, which I'll tell you about shortly.

Automatic File Shrinkage

The database property 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 is returned to the operating system. The thread that performs autoshrink—which always has server process ID (spid) 6—shrinks databases at 30-minute intervals. I'll discuss the DBCC SHRINKDATABASE command 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][, {EMPTYFILE | NOTRUNCATE | TRUNCATEONLY} ] ) DBCC SHRINKDATABASE (database_name [, target_percent] [, {NOTRUNCATE | TRUNCATEONLY} ] ) 

DBCC SHRINKFILE

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 15-MB data file, a DBCC SHRINKFILE with a target_size of 12 causes all used pages in the last 3 MB of the file to be reallocated into any free slots in the first 12 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 5 shrinks the file to only 7 MB, not 5 MB.

DBCC SHRINKDATABASE

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 does 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 becomes the new minimum size.

The numeric target_percent argument passed to the DBCC SHRINKDATABASE command is a percentage of free space to leave in each file of the database. For example, if you've used 60 MB of a 100-MB database file, you can specify a shrink percentage of 25 percent. SQL Server will then shrink the file to a size of 80 MB, and you'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. If, on the other hand, you've used 80 MB or more of a 100-MB database file, there is no way that SQL Server can shrink this file to leave 25 percent free space. In that case, the file size remains unchanged.

Because DBCC SHRINKDATABASE shrinks 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 first moves pages to the front of files to free up space at the end, and then it releases 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 of the two steps just mentioned, while a third option is available only to DBCC SHRINKFILE:

  • 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.
  • The EMPTYFILE option, available only with DBCC SHRINKFILE, empties the contents of a data file and moves them to other files in the filegroup.

NOTE


DBCC SHRINKFILE specifies a target size in megabytes. DBCC SHRINKDATABASE specifies a target percentage of free space to leave in the database.

Both the DBCC SHRINKFILE and DBCC SHRINKDATABASE commands give a report for each file that can be shrunk. For example, if my pubs database currently has an 8-MB data file and a log file of about the same size, I get the following report when I issue this DBCC SHRINKDATABASE command:

 DBCC SHRINKDATABASE(pubs, 10) RESULTS: DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages ------ ------ ----------- ----------- ----------- -------------- 5 1 256 80 152 152 5 2 1152 63 1152 56 

The current size is the size in pages after any shrinking takes place. In this case, the database file (FileId = 1) was able to be shrunk to 256 pages of 8 KB each, which is 2 MB. But only 152 pages are used. There could be several reasons for the difference between used pages and current pages:

  • If I asked to leave a certain percentage free, the current size will be bigger than the used pages because of that free space.
  • If the minimum size to which I can shrink a file is bigger than the used pages, the current size cannot become smaller than the minimum size. (The minimum size of a file is the smaller of the initial creation size and the size the file has been increased to using the ALTER DATABASE command.)
  • If the size of the data file for the model database is bigger than the used pages, the current size cannot become smaller than the size of model's data file.

For the log file (FileId = 2), the only values that really matter are the current size and the minimum size. The other two values are basically meaningless for log files because the current size is always the same as the used pages, and because there is really no simple way to estimate how small a log file can be shrunk down.



Inside Microsoft SQL Server 2000
Inside Microsoft SQL Server 2000
ISBN: 0735609985
EAN: 2147483647
Year: 2005
Pages: 179
Authors: Kalen Delaney

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