Problem: I am working on a SQL Server 2019 database where I deleted a large amount of user data (~500 GB). After deletion, I attempted to shrink the data file using:
DBCC SHRINKFILE (N'web_Data', TARGET_SIZE_MB);
However, the shrink process stopped at 76% and could not proceed further.
Analysis:
Using sys.dm_db_database_page_allocations and sys.allocation_units, I found that:
LOB data and system objects (e.g.,
sysobjvalues,syscerts,sysasymkeys) are allocated toward the end of the file.Since these allocations are not movable, the shrink operation cannot release the remaining space.
Question:
How can I perform a shrink operation that reclaims only the movable free space, without getting stuck on unmovable LOB and system data?
What I Tried:
DBCC SHRINKFILE with a target size
DBCC SHRINKFILEwithTRUNCATEONLY
Clarifications:
I understand that shrinking can cause fragmentation. I’m not asking whether I should shrink, I’m asking how to reclaim only the free space that is actually reclaimable without getting blocked.
Rebuilding the database is not part of this question—I’m focusing on whether SQL Server allows partial shrink in this scenario.
Environment:
SQL Server Version: 2019
Database Size Before Deletion: ~1 TB
Deleted Data: ~500 GB from a single large table
File: Single data file in PRIMARY filegroup
Goal:
- Reclaim as much space as possible using
DBCC SHRINKFILE, even if system LOB data cannot be moved.
