DBCC SHRINKFILE (Transact-SQL)

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance SQL database in Microsoft Fabric

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 because shrink is a long-running and resource-intensive operation.

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 logical name of the file to shrink.

file_id

The identification (ID) number of the file to shrink. 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 you set target_size to 0 or don't specify it, DBCC SHRINKFILE reduces the file to its 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.

If you specify target_size with TRUNCATEONLY, DBCC SHRINKFILE might not release free space at the end of the file.

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

EMPTYFILE isn't supported in Azure SQL Database, Azure SQL Database Hyperscale, or SQL database in Microsoft Fabric.

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.

If target_size is specified with TRUNCATEONLY, free space at the end of the file might not be released.

The TRUNCATEONLY option doesn't move information in the log, but does remove inactive virtual log files (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, SQL database in Microsoft Fabric

The wait at low priority feature reduces lock contention during the shrink operation. For more information, see Understanding concurrency issues with DBCC SHRINKFILE.

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

• You can't specify the ABORT_AFTER_WAIT option NONE.
• You can't set the MAX_DURATION option. The low priority lock timeout for a shrink operation is always one minute.

WAIT_AT_LOW_PRIORITY

When a shrink command is executed in WAIT_AT_LOW_PRIORITY mode, queries requiring schema stability (Sch-S) locks on the Index Allocation Map (IAM) pages aren't blocked by the shrink operation. However, the shrink operation can be blocked by a Sch-S lock on an IAM page. Shrink continues to execute only when it's able to obtain a schema modify lock (Sch-M) lock on an IAM page it requires.

If a shrink operation in WAIT_AT_LOW_PRIORITY mode can't obtain this lock due to a long-running query holding a Sch-S lock, the shrink operation times out with error 49516, 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.

{ ABORT_AFTER_WAIT = [ SELF | BLOCKERS ] }

Applies to: SQL Server (SQL Server 2022 (16.x) and later versions), Azure SQL Database, SQL database in Microsoft Fabric.

• SELF

  SELF is the default option. Exit the shrink file operation currently being executed without taking any further 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 the ALTER ANY CONNECTION or KILL DATABASE CONNECTION permission.

Result set

The following table describes result set columns.

Remarks

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

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.

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.

Known issues

Applies to: SQL Server, Azure SQL Database, SQL database in Microsoft Fabric, Azure SQL Managed Instance, Azure Synapse Analytics dedicated SQL pool

• In versions SQL Server earlier than SQL Server 2025 (17.x), the pages used by large object (LOB) column types (varbinary(max), varchar(max), and nvarchar(max)) in compressed columnstore segments can't be moved by DBCC SHRINKDATABASE and DBCC SHRINKFILE. For more information, see What's new in columnstore indexes.

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 in busy online transaction processing (OLTP) environments.

For example, a user query might acquire a schema stability (Sch-S) lock on an Index Allocation Map (IAM) page and hold it until completion. When attempting to reclaim space during regular usage, shrink database and shrink file operations require a schema modification (Sch-M) lock when moving or deleting IAM pages, blocking the Sch-S locks needed by user queries. As a result, long-running queries can block a shrink operation. This also means that any new query requiring a Sch-S lock on an IAM page can queue behind the shrink operation, further exacerbating this concurrency issue.

Introduced in SQL Server 2022 (16.x), the wait at low priority feature for shrink operations addresses this problem by taking the schema modify lock on IAM pages in the 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 counterproductive. The file growth necessary to allocate new space after shrink can hinder performance.

• A shrink operation doesn't preserve the fragmentation state of indexes in the database, and can increase index fragmentation, which might reduce read I/O throughput for queries using large scans.

• If you need to shrink the data files of a large database, consider using the ShrinkDriver PowerShell script. The script automates and simplifies the shrink process, turning it into a single, observable, and resumable operation. The script shrinks multiple files in parallel, retries when interrupted, and outputs detailed status reports as it runs.

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 steps to verify that the file has sufficient 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;
  

• If you want to shrink the transaction log file, use the sys.dm_db_log_space_usage dynamic management view (DMV) to see the space used in the transaction log.

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

A common reason for a transaction log file not to shrink is the absence of regular transaction log backups. To truncate the log, back up the transaction log and then run the DBCC SHRINKFILE operation again. If point-in-time recovery isn't required, consider the Recovery models (SQL Server) to avoid log file growth.

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:

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, do one of the following steps:

• 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

The code samples in this article use the AdventureWorks2025 or AdventureWorksDW2025 sample database, which you can download from the Microsoft SQL Server Samples and Community Projects home page.

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 AdventureWorks2025 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 AdventureWorks2025;
GO

-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE AdventureWorks2025
    SET RECOVERY SIMPLE;
GO

-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (AdventureWorks2025_Log, 1);
GO

-- Reset the database recovery model.
ALTER DATABASE AdventureWorks2025
    SET RECOVERY FULL;
GO

C. Truncate a data file

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

USE AdventureWorks2025;
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 AdventureWorks2025;
GO

-- Create a data file and assume it contains data.
ALTER DATABASE AdventureWorks2025
    ADD FILE (NAME = Test1data, FILENAME = 'C:\t1data.ndf', SIZE = 5 MB);
GO

-- Empty the data file.
DBCC SHRINKFILE (Test1data, EMPTYFILE);
GO

-- Remove the data file from the database.
ALTER DATABASE AdventureWorks2025
     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 aborts.

USE AdventureWorks2025;
GO

SELECT file_id,
       name
FROM sys.database_files;
GO

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

Related content

• Shrink a database
• Shrink a file
• DBCC SHRINKDATABASE (Transact-SQL)
• Considerations for the autogrow and autoshrink settings in SQL Server
• Database files and filegroups
• sys.database_files (Transact-SQL)
• sys.databases (Transact-SQL)
• FILE_ID (Transact-SQL)
• ALTER DATABASE (Transact-SQL)
• Manage file space for databases in Azure SQL Database