Expanding or Shrinking a Database | Inside Microsoft SQL Server 2005: The Storage Engine (Solid Quality Learning)

Databases can be expanded and shrunk 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; I'll discuss changes in log file size in Chapter 5.

Warning

Shrinking a database or any data file is an extremely resource-intensive operation, and the only reason to do it is if you absolutely must recover disk space.

Automatic File Expansion

Expansion 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 it is always rounded up to the nearest 64 KB. If the value is specified as a percentage, 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.

Allowing SQL Server to grow your data files automatically is no substitute for good capacity planning before you build or populate any tables. Enabling autogrow might prevent some failures due to unexpected increases in data volume, but it can also cause problems. If a data file is full and your autogrow percentage is set to grow by 10 percent, if an application attempts to insert a single row and there is no space, the database might start to grow by a large amount. (Ten percent of 10,000 MB is 1000 MB.) This in itself can take a lot of time if fast file initialization (discussed in the next section) is not being used. The growth might take so long that the client application's timeout value is exceeded, which means the insert query will fail. The query would have failed anyway if autogrow wasn't set, but with autogrow enabled, SQL Server will spend a lot of time trying to grow the file, and you won't be informed of the problem immediately.

With autogrow enabled, your database files still cannot grow the database size beyond the limits of the available disk space on the drives on which files are defined, or beyond the size specified in the MAXSIZE file property. So if you rely on the autogrow functionality to size your databases, you must still independently check your available hard disk space or the total file size. To reduce the possibility of running out of space, you can watch the Performance Monitor counter SQL Server: Databases Object: Data File Size and set up a performance alert to fire when the database file reaches a certain size.

Manual File Expansion

You can manually expand a database file by 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 a file, you use the DBCC SHRINKFILE command, which I'll tell you about shortly.

Fast File Initialization

In SQL Server 2005, data files can be initialized instantaneously. This allows for fast execution of the file creation and growth. Instant file initialization adds space to the data file without filling the newly added space with zeros. Instead, the actual disk content is overwritten only as new data is written to the files. Until the data is overwritten, there is always the chance that a hacker using an external file reader tool can see the data that was previously on the disk. Although the SQL Server 2005 documentation describes the instant file initialization feature as an "option," it is not an option within SQL Server. It is actually controlled through a Windows security setting called SE_MANAGE_VOLUME_NAME, which is granted to Windows Administrators by default. (This right can be granted to other Windows users by adding them to the Perform Volume Maintenance Tasks security policy.) If your SQL Server (MSSQLSERVER) service account is in the Windows Administrator role and your SQL Server is running on a Windows XP or Windows 2003 file system, instant file initialization will be used. If you want to make sure your database files are zeroed out as they are created and expanded, you can use traceflag 1806 to always zero the space, as previous SQL Server versions did.

Automatic 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 autoshrinkwhich always has a session ID (SPID) of 6 in SQL Server 2005 (but there's no guarantee SQL Server will use the same SPID in future versions)shrinks databases at 30-minute intervals. I'll discuss the DBCC SHRINKDATABASE command in more detail momentarily.

Manual Shrinkage

You can manually shrink a database using one of the following 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 you specify target_size, 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

DBCC SHRINKDATABASE shrinks all files in a database. The database can't be made smaller than the model database, and DBCC SHRINKDATABASE does not allow any file to be shrunk 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 its 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 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:

NOTRUNCATE This option causes all the freed file space to be retained in the database files. SQL Server compacts the data only by moving it to the front of the file. The default is to release the freed file space to the operating system.
TRUNCATEONLY This 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.
EMPTYFILE This 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 command and the DBCC SHRINKDATABASE command 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 shrunk to 256 pages of 8 KB each, which is 2 MB. But only 152 pages were used. There might 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.
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. Shrinking a log file is very different from shrinking a data file, and understanding how much you can shrink a log file, and what exactly happens when you shrink it, requires an understanding of how the log is used. For this reason, I will postpone the discussion of shrinking log files until Chapter 5.

As the warning at the beginning of this section indicated, shrinking a database or any data files is a resource-intensive operation. If you absolutely need to recover disk space from the database, you should plan the shrink operation carefully and perform it when it will have the least impact on the rest of the system. You should never enable the AUTOSHRINK option, which will shrink all the data files at regular intervals and wreak havoc with system performance. Because shrinking data files can move data all around a file, it can also introduce fragmentation, which you then might want to remove. Defragmenting your data files can then have its own impact on productivity because it uses system resources. I'll discuss fragmentation and defragmentation in Chapter 7.

It is possible for shrink operations to be blocked by a transaction that has been enabled for the snapshot isolation level. When this happens, DBCC SHRINKFILE and DBCC SHRINKDATABASE print out an informational message to the error log every five minutes in the first hour and then every hour after that. SQL Server 2005 also provides progress reporting for the SHRINK commands, available through the sys.dm_exec_requests view. I discuss progress reporting in the section on DBCC commands, or you can get the full details from the Books Online page for sys.dm_exec_requests.