Do you have a comment or suggestion to improve SQL Server? We’d love to hear it!

Can't SHRINK data files due to off-row LOB in large tables

There are times where I believe it's very important to be able to SHRINK (EMPTYFILE) database files that are on obsolete storage technology in an online manner. An example is moving an secondary data file from older HDD technology to newer SSD technology in an online manner.

However, when shrinking a database ROWS data file that has LOB data where the table has many rows it requires a table scan for each piece of off-row LOB data encountered. While this table scan occurs a LCK_M_SCH_S lock occurs on the table preventing operational queries from accessing the data, resulting in unacceptable data availability. Per Paul Randal it would be nice if off-row LOB had a backlink back to the �owning� data record, allowing faster shrinking and preventing schema locks from blocking data access.

See Paul Randal's writeup:
https://www.sqlskills.com/blogs/paul/why-lob-data-makes-shrink-run-slooooowly-t-sql-tuesday-006/

4 votes
Sign in
(thinking…)
Password icon
Signed in as (Sign out)

We’ll send you updates on this idea

AdminMicrosoft SQL Server (Product Manager, Microsoft Azure) shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

2 comments

Sign in
(thinking…)
Password icon
Signed in as (Sign out)
Submitting...
  • Jeff Moden commented  ·   ·  Flag as inappropriate

    I second this request. It would also be nice to have LOB_Compaction actually work on LOB data, which is typically off-row but doesn't work there. This is especially important not only to the ShrinkFile process but to the very reason why one may need to do a ShrinkFile to begin with and that is the interleaved deletion of large quantities of LOB data. LOB_Compaction needs to be made to actually work even if you don't do a ShrinkFile.

Feedback and Knowledge Base