1

I have a huge table that stores pdf documents using datatype "image". 

Recently we started to offload files to blob storage, that is why we want to shrink main data file.

All my tries to shrink the file wasn't successful. 

Usually, it stucks on 97-98% and doesn't go any further. 

Even shrinking by small chunks 1024 mb is not working. 

What else should I try to solve this problem?

This is query I am using for shrinking:

DECLARE @i int = 4501095 -- set original size

WHILE @i >= 4481000 Begin -- set final size
DBCC SHRINKFILE (N'DataFile' , @i)
--Shrink by 1024 MB at a time. 
SET @i -= 1024
END
GO

Table description:

Table occupies 80% of the database and has:

Rows: 6,472,619 rows;

Size: 2641.5 GB;

LOB data:   2639.1 GB LOB

data files:

enter image description here

Improve this question
3
  • The main reason for shrinking is to speed up backup time and for disaster recovery.
    – Serdia
    Feb 16, 2022 at 18:55
  • 1
    Your backups won't contain the empt space from your data pages. So, I wouldn't expect much of an improvement there from shrinking.
    – Brendan McCaffrey
    Feb 16, 2022 at 19:14
  • @Serdia If you moved the data, have you dropped the column that used to contain the PDFs? You might want to try doing a REBUILD on the clustered index of that table and then try shrinking again.
    – Solomon Rutzky
    Feb 17, 2022 at 15:29

1 Answer 1

Reset to default
1

How long does it take to do a table scan for this table, including reading the LOB data? Multiply that with 346,161,152 and you get how long time it will take to do a full shrink, roughly. Then reduce some, based on at what point it reaches the target file size. Still a very very long time.

Say that you shrink "only" 1 GB at a time. That is 131,072 pages. Say that "only" 100,000 of those pages are LOB pages. Still a hefty number of table scans!

Think about what shrink does. It moved pages from the end of the file to the beginning of the file. For a LOB page, SQL Server need to adjust whatever points to this page to reflect the new page adress. A LOB page doesn't have a back-pointer, so it needs to do a table scan for each page that is moved. That is a whole lot of table scans!

Of course, the shrink will stop when it gets to the target size.

There are some details buried in here, but it doesn't change above. Data for LOB types can be in-row (when it fits), but since you say you have 2639 GB LOB data, I assume those are in fact LOB pages. A lob page can be shared between rows, reducing space usage compared to the predating design - or it can be dedicated to a row. There's also a B-Tree structure for the LOB data (allowing "searched in the middle") but the nodes in the tree are not necessarily pages, they are data on those pages. So, regardless of the details, it if is indeed a LOB pages that is moved, then all pages need to be read to find out which of those pages might point to this moved LOB page.

In your case, just live with the free space. It doesn't hurt anything and it doesn't make your backup significantly larger (since backup only backs up used extents).

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.