DBCC SHRINKFILE
Shrinks the size of the specified data file or log file for the related database.
Syntax
DBCC SHRINKFILE
( { file_name | file_id }
{ [ , target_size ]
| [ , { EMPTYFILE | NOTRUNCATE | TRUNCATEONLY } ]
}
)
Arguments
file_name
Is the logical name of the file shrunk. File names must conform to the rules for identifiers. For more information, see Using Identifiers.
file_id
Is the identification (ID) number of the file to be shrunk. To obtain a file ID, use the FILE_ID function or search sysfiles in the current database.
target_size
Is the desired size for the file in megabytes, expressed as an integer. If not specified, DBCC SHRINKFILE reduces the size to the default file size.
If target_size is specified, DBCC SHRINKFILE attempts to shrink the file to the specified size. 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, if there is 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 available free slots in the first 8 MB of the file. DBCC SHRINKFILE does not shrink a file past the size needed to store the data in the file. For example, if 7 MB of a 10-MB data file is used, a DBCC SHRINKFILE statement with a target_size of 6 shrinks the file to only 7 MB, not 6 MB.
EMPTYFILE
Migrates all data from the specified file to other files in the same filegroup. Microsoft® SQL Server™ no longer allows data to be placed on the file used with the EMPTYFILE option. This option allows the file to be dropped using the ALTER DATABASE statement.
NOTRUNCATE
Causes the freed file space to be retained in the files.
When NOTRUNCATE is specified along with target_size, the space freed is not released to the operating system. The only effect of the DBCC SHRINKFILE is to relocate used pages from above the target_size line to the front of the file. When NOTRUNCATE is not specified, all freed file space is returned to the operating system.
TRUNCATEONLY
Causes any unused space in the files to be released to the operating system and shrinks the file to the last allocated extent, reducing the file size without moving any data. No attempt is made to relocate rows to unallocated pages. target_size is ignored when TRUNCATEONLY is used.
Remarks
DBCC SHRINKFILE applies to the files in the current database. Switch context to the database to issue a DBCC SHRINKFILE statement referencing a file in that particular database. For more information about changing the current database, see USE.
The database cannot be made smaller than the size of the model database.
Use DBCC SHRINKFILE to reduce the size of a file to smaller than its originally created size. The minimum file size for the file is then reset to the newly specified size.
To remove any data that may be in a file, execute DBCC SHRINKFILE('file_name', EMPTYFILE) before executing ALTER DATABASE.
The database being shrunk does not have to be in single-user mode; other users can be working in the database when the file is shrunk. You do not have to run SQL Server in single-user mode to shrink the system databases.
For log files, SQL Server uses target_size to calculate the target size for the entire log; therefore, target_size is the amount of free space in the log after the shrink operation. Target size for the entire log is then translated to target size for each log file. DBCC SHRINKFILE attempts to shrink each physical log file to its target size immediately. If no part of the logical log resides in the virtual logs beyond the log file's target size, the file is successfully truncated and DBCC SHRINKFILE completes with no messages. However, if part of the logical log resides in the virtual logs beyond the target size, SQL Server frees as much space as possible and then issues an informational message. The message tells you what actions you need to perform to move the logical log out of the virtual logs at the end of the file. After you perform the actions, you can then reissue the DBCC SHRINKFILE command to free the remaining space. For more information about shrinking transaction logs, see Shrinking the Transaction Log.
Because a log file can only be shrunk to a virtual log file boundary, it may not be possible to shrink a log file to a size smaller than the size of a virtual log file, even if it is not being used. For example, a database with a log file of 1 GB can have the log file shrunk to only 128 MB. For more information about truncation, see Truncating the Transaction Log. For more information about determining virtual log file sizes, see Virtual Log Files.
Result Sets
This table describes the columns in the result set.
| Column name | Description |
|---|---|
| DbId | Database identification number of the file SQL Server attempted to shrink. |
| FileId | The file identification number of the file SQL Server attempted to shrink. |
| CurrentSize | The number of 8-KB pages the file currently occupies. |
| MinimumSize | The number of 8-KB pages the file could occupy, at minimum. This corresponds to the minimum size or originally created size of a file. |
| UsedPages | The number of 8-KB pages currently used by the file. |
| EstimatedPages | The number of 8-KB pages that SQL Server estimates the file could be shrunk down to. |
Permissions
DBCC SHRINKFILE permissions default to members of the sysadmin fixed server role or the db_owner fixed database role, and are not transferable.
Examples
This example shrinks the size of a file named DataFil1 in the UserDB user database to 7 MB.
USE UserDB
GO
DBCC SHRINKFILE (DataFil1, 7)
GO