SQL Server Shrink Best Practices: When to Shrink and When Not To

When a SQL Server disk starts filling up, shrink is one of the first commands that comes to mind. Used in the right situation and the right way, it really does reclaim space. Used the wrong way, it fragments your indexes, consumes resources for hours, blocks user queries, and the space it won disappears again a few days later when the file grows back.

This article explains exactly what shrink does in SQL Server, why it shouldn’t be routine maintenance, when it is genuinely necessary, and how to shrink data and log files safely when you do need to.

What Does Shrink Do in SQL Server?

Shrink reduces the space a database file takes on disk, but it works differently for data files and log files:

• Data files (.mdf, .ndf): SQL Server moves used pages from the end of the file into free space near the beginning. Once enough free space has been created at the end, that part is returned to the operating system. This page movement is why shrink can be both slow and harmful.
• Log files (.ldf): no pages are moved. Unused virtual log files (VLFs) at the end of the log file are removed. If the end of the log is still in use, the log won’t shrink to the target size.

There are two commands: DBCC SHRINKDATABASE shrinks every file in the database towards a percentage target, while DBCC SHRINKFILE shrinks one file to a specific size in MB. Because it lets you control exactly which file shrinks and by how much, DBCC SHRINKFILE should almost always be the choice.

Why Shrink Shouldn’t Be Routine Maintenance

Microsoft is clear on this: shrink operations shouldn’t be treated as regular maintenance, and data and log files that grow because of regular business operations don’t need to be shrunk. The reasons:

• Index fragmentation: moved pages are placed in any free location in the file. That breaks the physical order of indexes and slows down queries that scan large ranges.
• Resource consumption: shrinking a data file is a long-running, logged operation that uses a lot of I/O and CPU.
• Blocking: while moving pages, shrink needs short schema modification (Sch-M) locks. A long-running query can hold shrink up, and new queries can then queue behind the waiting shrink.
• The gain doesn’t last: a database needs free space for normal operation. A shrunk file soon grows again, and every autogrowth event slows transactions down while it happens. In Microsoft’s words, repeatedly shrinking a database that keeps growing back is a wasted operation.

That’s why maintenance plans shouldn’t contain a “Shrink Database” step, and shrink shouldn’t run regularly from a scheduled job. We cover how to design maintenance plans in SQL Server Maintenance Plans.

Keep AUTO_SHRINK Turned Off

With AUTO_SHRINK on, SQL Server automatically shrinks a file in the background once more than 25% of it is unused. That means getting all the problems above at uncontrolled, unpredictable times. It is off by default, but it may have been left on in older or migrated databases. Check the whole server with one query:

-- Databases with AUTO_SHRINK enabled
SELECT name, is_auto_shrink_on
FROM sys.databases
WHERE is_auto_shrink_on = 1;

-- Turn it off
ALTER DATABASE [SalesDB] SET AUTO_SHRINK OFF;

In SSMS, you can also right-click the database, open Properties → Options and set Auto Shrink to False.

When Is Shrink Actually Necessary?

Shrink is legitimate when a large amount of space has been freed permanently and won’t be used again:

• After a large archive or data cleanup, when you know the deleted data won’t come back.
• After large tables or unused large indexes have been permanently dropped.
• After a one-off operation (such as a large data load or a migration) pushed the log far beyond its normal size.
• When a data file is to be emptied completely and removed from the database (EMPTYFILE).

Before You Shrink: How Much Free Space Is There Really?

Before deciding to shrink, check how much free space each file actually has. Run this inside the database to see allocated, used and free space for every file, in MB:

USE [SalesDB];

SELECT name,
       type_desc,
       CAST(size / 128.0 AS decimal(18, 2)) AS allocated_mb,
       CAST(FILEPROPERTY(name, 'SpaceUsed') / 128.0 AS decimal(18, 2)) AS used_mb,
       CAST((size - FILEPROPERTY(name, 'SpaceUsed')) / 128.0 AS decimal(18, 2)) AS free_mb,
       growth,
       is_percent_growth
FROM sys.database_files;

If a file has no free space, shrink gains nothing. If the log file is large but full, the fix isn’t shrink but finding out why the log can’t be truncated. We explain how in SQL Server Transaction Log Too Big?

How to Shrink a Data File Safely

If you’ve decided a shrink is genuinely needed, follow these steps:

1. Make sure you have a restorable backup first.
2. Pick a low-activity window. You can’t take a backup while a shrink is running, and you can’t shrink while a backup is running, so plan the window around your backup jobs.
3. Set a realistic target size. Don’t shrink the file down to almost nothing; leave the free space the database will need over the coming months. Otherwise the file grows again immediately.
4. Shrink large files in chunks. Rather than taking a 500 GB file down to 200 GB in one command, shrinking a few tens of GB at a time keeps the operation controllable. DBCC SHRINKFILE can be stopped at any point, and the work completed so far is kept.
5. Watch for blocking. On SQL Server 2022 and later, use the WAIT_AT_LOW_PRIORITY option. In this mode shrink doesn’t block user queries; if it can’t get the lock it needs within one minute, it ends itself.

USE [SalesDB];

-- Find the file's logical name and file_id
SELECT file_id, name, type_desc, size / 128 AS size_mb
FROM sys.database_files;

-- Shrink the data file towards 200,000 MB (work in steps)
DBCC SHRINKFILE (N'SalesDB_Data', 200000);

-- SQL Server 2022+: try without blocking user queries
DBCC SHRINKFILE (N'SalesDB_Data', 200000)
WITH WAIT_AT_LOW_PRIORITY (ABORT_AFTER_WAIT = SELF);

You can follow the progress of a running shrink from another session:

SELECT session_id, command, percent_complete,
       estimated_completion_time / 60000 AS est_minutes_left,
       blocking_session_id, wait_type
FROM sys.dm_exec_requests
WHERE command LIKE N'Dbcc%';

A long transaction that uses row versioning (for example one running under snapshot isolation) can also hold a shrink up. When that happens, SQL Server writes informational messages to the error log (5203 for SHRINKFILE) saying which transaction the shrink is waiting for.

DBCC SHRINKFILE options

DBCC SHRINKDATABASE can’t shrink a database below the size it was created with or last explicitly set to. DBCC SHRINKFILE, by contrast, can reset that minimum size to the new target.

After the Shrink: Deal With Fragmentation

After shrinking a data file, your indexes are very likely fragmented. Microsoft recommends rebuilding indexes after a shrink to remove the fragmentation. There is a trade-off to keep in mind:

• ALTER INDEX ... REBUILD removes fragmentation completely, but it needs free space to build a new copy of the index. Rebuilding a large index can grow the file you just shrank.
• ALTER INDEX ... REORGANIZE reorders pages in place and doesn’t grow the file, but it’s less effective against fragmentation and slow on large indexes.

Shrinking the Log File

Shrinking a log file is different from shrinking a data file: no pages move and no fragmentation is created. But the log can only shrink if the VLFs at its end aren’t in use. The right order is:

1. Find out why the log grew and fix the cause (log_reuse_wait_desc in sys.databases).
2. Under the full recovery model, take a log backup so the log is truncated.
3. Shrink the log, then grow it back to its real requirement in one step with a fixed autogrowth value. That also keeps the VLF count reasonable.

USE [SalesDB];

BACKUP LOG [SalesDB]
TO DISK = N'E:\Backup\SalesDB_LOG_before_shrink.trn'
WITH COMPRESSION, CHECKSUM;

DBCC SHRINKFILE (N'SalesDB_log', 1024);

ALTER DATABASE [SalesDB]
MODIFY FILE (NAME = N'SalesDB_log', SIZE = 8192MB, FILEGROWTH = 512MB);

Microsoft’s own examples also show switching the database temporarily to the simple recovery model to truncate the log. On a production database, though, that breaks the log chain and removes point-in-time restore capability. If you have to do it, switch back to full immediately and take a full backup. For how VLFs affect performance, see How High VLF Counts Kill Your SQL Server Performance.

What to Do Instead of Shrinking

If you find yourself needing shrink often, the real problem is usually somewhere else:

• Capacity planning: size files for their actual growth rate and plan disks accordingly. Free space the database isn’t using yet but will use later isn’t a problem; it’s a reserve.
• Fixed autogrowth: use fixed MB values rather than percentages for data and log files.
• Instant file initialization: giving the SQL Server service account the Perform volume maintenance tasks right makes data file growth almost instant, which lowers the cost of keeping files pre-sized. See Improve SQL Server Performance by Enabling Instant File Initialization.
• An archiving strategy: regularly moving old data to a separate database or partition prevents the main database from growing out of control.
• The right disk layout: place data, log and tempdb files on suitable disks. See Best Practices for Disk Storage in SQL Server.

Frequently Asked Questions