SQL Server Shrinkfile Stops at 76% Due to LOB and System Data—Is Partial Shrink Possible?

**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

.dm_db_database_page_allocations

and

.allocation_units

, I found that: - **LOB data and system objects** (e.g.,

,

,

) 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:**

SHRINKFILE

with a target size -

SHRINKFILE

with

**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

SHRINKFILE

, even if system LOB data cannot be moved.