DBCC SHRINKFILE (Transact-SQL)

Άρθρο
07/02/2024

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance

Shrinks the current database's specified data or log file size. You can use it to move data from one file to other files in the same filegroup, which empties the file and allows for its database removal. You can shrink a file to less than its size at creation, resetting the minimum file size to the new value. Use DBCC SHRINKFILE only when necessary.

Note

Shrink operations should not be considered a regular maintenance operation. Data and log files that grow due to regular, recurring business operations do not require shrink operations.

Transact-SQL syntax conventions

Syntax

DBCC SHRINKFILE   
(  
    { file_name | file_id }   
    { [ , EMPTYFILE ]   
    | [ [ , target_size ] [ , { NOTRUNCATE | TRUNCATEONLY } ] ]  
    }  
)  
[ WITH 
  {     
      [ WAIT_AT_LOW_PRIORITY 
        [ ( 
            <wait_at_low_priority_option_list>
        )] 
      ] 
      [ , NO_INFOMSGS]
  }
]
       
< wait_at_low_priority_option_list > ::=  
    <wait_at_low_priority_option>
    | <wait_at_low_priority_option_list> , <wait_at_low_priority_option>
 
< wait_at_low_priority_option > ::=
    ABORT_AFTER_WAIT = { SELF | BLOCKERS }

Arguments

file_name

The file to be shrunk's logical name.

file_id

The file to be shrunk's identification (ID) number. To get a file ID, use the FILE_IDEX system function or query the sys.database_files catalog view in the current database.

target_size

An integer representing the file's new megabyte size. If not specified or 0, DBCC SHRINKFILE reduces to the file creation size.

You can reduce an empty file's default size using DBCC SHRINKFILE <target_size>. For example, if you create a 5-MB file and then shrink the file to 3 MB while the file is still empty, the default file size is set to 3 MB. This applies only to empty files that have never contained data.

This option isn't supported for FILESTREAM filegroup containers.

If specified, DBCC SHRINKFILE tries to shrink the file to target_size. Used pages in the file's area to be freed are moved to free space in the file's kept areas. For example, with a 10-MB data file, a DBCC SHRINKFILE operation with an 8 target_size moves all used pages in the file's last 2 MB into any unallocated pages in the file's first 8 MB. DBCC SHRINKFILE doesn't shrink a file past the needed stored data size. 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. In other words, EMPTYFILE migrates data from a specified file to other files in the same filegroup. EMPTYFILE assures you that no new data gets added to the file, despite this file not being read-only. You can use the ALTER DATABASE statement to remove a file. If you use the ALTER DATABASE statement to change file size, the read-only flag is reset, and data can be added.

For FILESTREAM filegroup containers, you can't use ALTER DATABASE to remove a file until the FILESTREAM Garbage Collector has run and deleted all the unnecessary filegroup container files that EMPTYFILE has copied to another container. For more information, see sp_filestream_force_garbage_collection. For information on removing a FILESTREAM container, see the corresponding section in ALTER DATABASE File and Filegroup Options (Transact-SQL)

EMPTYFILE is not supported in Azure SQL Database or Azure SQL Database Hyperscale.

NOTRUNCATE

Moves allocated pages from a data file's end to unallocated pages in a file's front with or without specifying target_percent. The free space at the file's end isn't returned to the operating system, and the file's physical size doesn't change. Therefore, if NOTRUNCATE is specified, the file appears not to shrink.

NOTRUNCATE is applicable only to data files. The log files aren't affected.

This option isn't supported for FILESTREAM filegroup containers.

TRUNCATEONLY

Releases all free space at the file's end to the operating system but doesn't perform any page movement inside the file. The data file is shrunk only to the last allocated extent.

target_size is ignored if specified with TRUNCATEONLY.

The TRUNCATEONLY option doesn't move information in the log, but does remove inactive VLFs from the end of the log file. This option isn't supported for FILESTREAM filegroup containers.

WITH NO_INFOMSGS

Suppresses all informational messages.

WAIT_AT_LOW_PRIORITY with shrink operations

Applies to: SQL Server 2022 (16.x) and later versions, Azure SQL Database, Azure SQL Managed Instance

The wait at low priority feature reduces lock contention. For more information, see Understanding concurrency issues with DBCC SHRINKDATABASE.

This feature is similar to the WAIT_AT_LOW_PRIORITY with online index operations, with some differences.

You cannot specify ABORT_AFTER_WAIT option NONE.

WAIT_AT_LOW_PRIORITY

Applies to: SQL Server (SQL Server 2022 (16.x) and later) and Azure SQL Database.

When a shrink command is executed in WAIT_AT_LOW_PRIORITY mode, new queries requiring schema stability (Sch-S) locks are not blocked by the waiting shrink operation until the shrink operation stops waiting and starts executing. The shrink operation will execute when it is able to obtain a schema modify lock (Sch-M) lock. If a new shrink operation in WAIT_AT_LOW_PRIORITY mode cannot obtain a lock due to a long-running query, the shrink operation will eventually timeout after 1 minute by default and will silently exit.

If a new shrink operation in WAIT_AT_LOW_PRIORITY mode cannot obtain a lock due to a long-running query, the shrink operation will eventually timeout after 1 minute by default and will silently exit. This will occur if the shrink operation cannot obtain the Sch-M lock due to concurrent query or queries holding Sch-S locks. When a timeout occurs, an error 49516 message will be sent to the SQL Server error log, for example: Msg 49516, Level 16, State 1, Line 134 Shrink timeout waiting to acquire schema modify lock in WLP mode to process IAM pageID 1:2865 on database ID 5. At this point, you can simply retry the shrink operation in WAIT_AT_LOW_PRIORITY mode knowing that there would be no impact to the application.

ABORT_AFTER_WAIT = [ SELF | BLOCKERS ]

Applies to: SQL Server (SQL Server 2022 (16.x) and later) and Azure SQL Database.

SELF

Exit the shrink file operation currently being executed without taking any action.
BLOCKERS

Kill all user transactions that block the shrink file operation so that the operation can continue. The BLOCKERS option requires the login to have ALTER ANY CONNECTION permission.

Result set

The following table describes result set columns.

Column name	Description
`DbId`	Database identification number of the file the Database Engine tried to shrink.
`FileId`	The file identification number of the file the Database Engine tried to shrink.
`CurrentSize`	Number of 8-KB pages the file currently occupies.
`MinimumSize`	Number of 8-KB pages the file could occupy, at minimum. This number corresponds to the minimum size or originally created size of a file.
`UsedPages`	Number of 8-KB pages currently used by the file.
`EstimatedPages`	Number of 8-KB pages that the Database Engine estimates the file could be shrunk down to.

Remarks

DBCC SHRINKFILE applies to the current database's files. For more information about how to change the current database, see USE (Transact-SQL).

You can stop DBCC SHRINKFILE operations at any point and any completed work is preserved. If you use the EMPTYFILE parameter and cancel the operation, the file isn't marked to prevent additional data from being added.

When a DBCC SHRINKFILE operation fails, an error is raised.

Other users can work in the database during file shrinking; the database doesn't have to be in single-user mode. You don't have to run the instance of SQL Server in single-user mode to shrink the system databases.

When specified with WAIT_AT_LOW_PRIORITY, the shrink operation's Sch-M lock request will wait with low priority when executing the command for 1 minute. If the operation is blocked for the duration, the specified ABORT_AFTER_WAIT action will be executed.

Database and file shrink operations are currently in preview for Azure SQL Database Hyperscale. For more information on the preview, see Shrink for Azure SQL Database Hyperscale.

Known issues

Applies to: SQL Server, Azure SQL Database, Azure SQL Managed Instance, Azure Synapse Analytics dedicated SQL pool

Currently, LOB column types (varbinary(max), varchar(max), and nvarchar(max)) in compressed columnstore segments are not affected by DBCC SHRINKDATABASE and DBCC SHRINKFILE.

Understand concurrency issues with DBCC SHRINKFILE

The shrink database and shrink file commands can lead to concurrency issues, especially with active maintenance such as rebuilding indexes, or on busy OLTP environments. When your application executes queries against database tables, these queries will acquire and maintain a schema stability lock (Sch-S) until the queries complete their operations. When attempting to reclaim space during regular usage, shrink database and shrink file operations currently require a schema modify lock (Sch-M) when moving or deleting Index Allocation Map (IAM) pages, blocking the Sch-S locks needed by user queries. As a result, long-running queries will block a shrink operation until the queries complete. This means that any new queries requiring Sch-S locks are also queued behind the waiting shrink operation and will also be blocked, further exacerbating this concurrency issue. This can significantly impact application query performance and will also cause difficulties completing the necessary maintenance to shrink database files. Introduced in SQL Server 2022 (16.x), the shrink wait at low priority feature addresses this problem by taking a schema modify lock in WAIT_AT_LOW_PRIORITY mode. For more information, see WAIT_AT_LOW_PRIORITY with shrink operations.

For more information on Sch-S and Sch-M locks, see Transaction locking and row versioning guide.

Shrink a log file

For log files, the Database Engine uses target_size to calculate the whole log's target size. Therefore, target_size is the log's free space after the shrink operation. The whole log's target size is then translated to each log file's target size. DBCC SHRINKFILE tries to shrink each physical log file to its target size immediately. However, if part of the logical log resides in the virtual logs beyond the target size, the Database Engine frees as much space as possible, and then issues an informational message. The message describes what actions are required to move the logical log out of the virtual logs at the end of the file. After the actions are performed, DBCC SHRINKFILE can be used to free the remaining space.

Because a log file can only be shrunk to a virtual log file boundary, shrinking a log file to a size smaller than the size of a virtual log file might not be possible, even if it isn't being used. The Database Engine dynamically chooses the virtual file log size when log files are created or extended.

Best practices

Consider the following information when you plan to shrink a file:

A shrink operation is most effective after an operation that creates a large amount of unused space, such as a truncate table or a drop table operation.
Most databases require some free space to be available for regular day-to-day operations. If you shrink a database file repeatedly and notice that the database size grows again, this indicates that the free space is required for regular operations. In these cases, repeatedly shrinking the database file is a wasted operation. Autogrow events necessary to grow the database file a hinder performance.
A shrink operation doesn't preserve the fragmentation state of indexes in the database, and generally increases fragmentation to a degree. This fragmentation is another reason not to repeatedly shrink the database.
Shrink multiple files in the same database sequentially instead of concurrently. Contention on system tables can cause blocking and lead to delays.

Troubleshoot

This section describes how to diagnose and correct issues that can occur when running the DBCC SHRINKFILE command.

The file doesn't shrink

If the file size doesn't change after an error-less shrink operation, try the following to verify that the file has adequate free space:

Run the following query.

SELECT name
    , size / 128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT) / 128.0 AS AvailableSpaceInMB
FROM sys.database_files;

Run the DBCC SQLPERF command to return the space used in the transaction log.

The shrink operation can't reduce the file size any further if there's insufficient free space available.

Typically it's the log file that appears not to shrink. This non-shrinking is usually the result of a log file that hasn't been truncated. To truncate the log, you can set the database recovery model to SIMPLE, or back up the log and then run the DBCC SHRINKFILE operation again.

The shrink operation is blocked

A transaction running under a row versioning-based isolation level can block shrink operations. For example, if a large delete operation running under a row versioning-based isolation level is in progress when a DBCC SHRINKDATABASE operation executes, the shrink operation waits for the delete to complete before continuing. When this blocking happens, DBCC SHRINKFILE and DBCC SHRINKDATABASE operations print an informational message (5202 for SHRINKDATABASE and 5203 for SHRINKFILE) to the SQL Server error log. This message is logged every five minutes in the first hour and then every hour. For example, if the error log contains the following error message then the following error will occur:

DBCC SHRINKFILE for file ID 1 is waiting for the snapshot
transaction with timestamp 15 and other snapshot transactions linked to
timestamp 15 or with timestamps older than 109 to finish.

This message means snapshot transactions with timestamps older than 109 (the last transaction that the shrink operation completed) are blocking the shrink operation. It also indicates the transaction_sequence_num, or first_snapshot_sequence_num columns in the sys.dm_tran_active_snapshot_database_transactions dynamic management view contains a value of 15. If either the transaction_sequence_num or first_snapshot_sequence_num view column contains a number less than a shrink operation's last completed transaction (109), the shrink operation waits for those transactions to finish.

To resolve the issue, you can do one of the following tasks:

End the transaction that is blocking the shrink operation.
End the shrink operation. Any completed work is kept if the shrink operation ends.
Do nothing and allow the shrink operation to wait until the blocking transaction completes.

Permissions

Requires membership in the sysadmin fixed server role or the db_owner fixed database role.

Examples

A. Shrink a data file to a specified target size

The following example shrinks the size of a data file named DataFile1 in the UserDB user database to 7 MB.

USE UserDB;
GO
DBCC SHRINKFILE (DataFile1, 7);
GO

B. Shrink a log file to a specified target size

The following example shrinks the log file in the AdventureWorks2022 database to 1 MB. To allow the DBCC SHRINKFILE command to shrink the file, the file is first truncated by setting the database recovery model to SIMPLE.

USE AdventureWorks2022;
GO
-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE AdventureWorks2022
SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (AdventureWorks2022_Log, 1);
GO
-- Reset the database recovery model.
ALTER DATABASE AdventureWorks2022
SET RECOVERY FULL;
GO

C. Truncate a data file

The following example truncates the primary data file in the AdventureWorks2022 database. The sys.database_files catalog view is queried to obtain the file_id of the data file.

USE AdventureWorks2022;
GO
SELECT file_id, name
FROM sys.database_files;
GO
DBCC SHRINKFILE (1, TRUNCATEONLY);

D. Empty a file

The following example demonstrates emptying a file so it can be removed from the database. For this example's purposes, a data file is first created and contains data.

USE AdventureWorks2022;
GO
-- Create a data file and assume it contains data.
ALTER DATABASE AdventureWorks2022
ADD FILE (
    NAME = Test1data,
    FILENAME = 'C:\t1data.ndf',
    SIZE = 5MB
    );
GO
-- Empty the data file.
DBCC SHRINKFILE (Test1data, EMPTYFILE);
GO
-- Remove the data file from the database.
ALTER DATABASE AdventureWorks2022
REMOVE FILE Test1data;
GO

E. Shrink a database file with WAIT_AT_LOW_PRIORITY

The following example attempts to shrink the size of a data file in the current user database to 1 MB. The sys.database_files catalog view is queried to obtain the file_id of the data file, in this example, file_id 5. If a lock can't be obtained within one minute, the shrink operation will abort.

USE AdventureWorks2022;
GO

SELECT file_id, name
FROM sys.database_files;
GO

DBCC SHRINKFILE (5, 1) WITH WAIT_AT_LOW_PRIORITY (ABORT_AFTER_WAIT = SELF);

Κοινή χρήση μέσω

DBCC SHRINKFILE (Transact-SQL)

Syntax

Arguments

file_name

file_id

target_size

EMPTYFILE

NOTRUNCATE

TRUNCATEONLY

WITH NO_INFOMSGS

WAIT_AT_LOW_PRIORITY with shrink operations

WAIT_AT_LOW_PRIORITY

ABORT_AFTER_WAIT = [ SELF | BLOCKERS ]

Result set

Remarks

Known issues

Understand concurrency issues with DBCC SHRINKFILE

Shrink a log file

Best practices

Troubleshoot

The file doesn't shrink

The shrink operation is blocked

Permissions

Examples

A. Shrink a data file to a specified target size

B. Shrink a log file to a specified target size

C. Truncate a data file

D. Empty a file

E. Shrink a database file with WAIT_AT_LOW_PRIORITY

Σχόλια

Πρόσθετοι πόροι

Κοινή χρήση μέσω

DBCC SHRINKFILE (Transact-SQL)

Syntax

Arguments

file_name

file_id

target_size

EMPTYFILE

NOTRUNCATE

TRUNCATEONLY

WITH NO_INFOMSGS

WAIT_AT_LOW_PRIORITY with shrink operations

WAIT_AT_LOW_PRIORITY

ABORT_AFTER_WAIT = [ SELF | BLOCKERS ]

Result set

Remarks

Known issues

Understand concurrency issues with DBCC SHRINKFILE

Shrink a log file

Best practices

Troubleshoot

The file doesn't shrink

The shrink operation is blocked

Permissions

Examples

A. Shrink a data file to a specified target size

B. Shrink a log file to a specified target size

C. Truncate a data file

D. Empty a file

E. Shrink a database file with WAIT_AT_LOW_PRIORITY

Related content

Σχόλια

Πρόσθετοι πόροι