How to shrink LOB data in sql server 2017

Question

How to shrink LOB data in sql server 2017

olegserdia

Old Hand

Points: 381
More actions
February 16, 2022 at 5:32 pm

#3984478
I have a huge table that stores pdf documents with 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? What is the best approach in situations like this?
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:

Viewing 15 posts - 1 through 15 (of 22 total)

You must be logged in to reply to this topic. Login to reply

ScottPletcher SSC Guru Points: 101098 More actions · Answer 1

SQL could have to rewrite roughly 3.4TB of data (the used space) to shrink that file -- that will take a long time.

Are you using data compression on the tables? If not, you might be better off adding a new filegroup, moving while page compressing the data to there. For indexes that don't need compressed, just move them with a REBUILD too.

Eventually you will empty out the current 'DataFile' filegroup and can delete it. Then you rename the new filegroup back to 'DataFile'.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

olegserdia Old Hand Points: 381 More actions · Answer 2

Thanks.

So you mean to move data to a new filegroup just that one table?

Because it's LOB data, isn't it's only gonna move pointer? But data itself will stay with primary group?

Michael L John One Orange Chip Points: 27055 More actions · Answer 3

When you shrink in 1024 chunks, it should be sized at each chunk. It does not "rollback" at the 98% failure.

What messages appear in the window when you execute a single call to shrinkfile?

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

olegserdia Old Hand Points: 381 More actions · Answer 4

Michael,

Shrinking 1024 mb takes more than 8 hours. Which is out of my timeframe. So I had to terminate it.

Approximately in 1 hour it gets to 98% and stays there for the rest of the time.

Couple of times, it did go through and successfully shrank 20 gb. I'd say out of 10 times shrinking, 2 times went successfully within 2 hours or so.

ScottPletcher SSC Guru Points: 101098 More actions · Answer 5

So you mean to move data to a new filegroup just that one table?

No, all the (very) large tables. Until the original filegroup is small.

Because it's LOB data, isn't it's only gonna move pointer? But data itself will stay with primary group? <<

Correct, LOB data will not move. If it's mostly LOB data, you'll need a different method.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Michael L John One Orange Chip Points: 27055 More actions · Answer 6

How much memory is allocated to SQL on this server? And how much activity is occurring when you are attempting this?

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

olegserdia Old Hand Points: 381 More actions · Answer 7

Michael,

this is development server, so I do not think there is any activity.

It has 4 virtual processors, 2 sockets.

It is much less powerful than production.

CSName : DEV_ENV
TotalVirtualMemorySize_MB : 37,631
TotalVisibleMemorySize_MB : 32,767
FreePhysicalMemory_MB : 24,319
FreeVirtualMemory_MB : 28,500
FreeSpaceInPagingFiles_MB : 4,864
NumberofProcesses : 171
NumberOfUsers : 40

Michael L John One Orange Chip Points: 27055 More actions · Answer 8

You are in the process of removing the files currently stored as an image to a file storage location. Until you finish this, I think you are probably wasting your time.

This dev server is woefully inadequate for this job. The shrink file in SQL server is also not the most efficiently written code.

Is the point of this exercise to determine how long it will take in production? Or are you just trying to free up disk space? What is the process to move these files from the table onto the file storage area?

How desperate of a situation is freeing up this space?

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

olegserdia Old Hand Points: 381 More actions · Answer 9

You are in the process of removing the files currently stored as an image to a file storage location. Until you finish this, I think you are probably wasting your time. - moving those files to Azure storage going to take around 2 years.

This dev server is woefully inadequate for this job. The shrink file in SQL server is also not the most efficiently written code. - DBCC ShrinkFile is pretty straightforward command, not sure how to make it more efficient.

Is the point of this exercise to determine how long it will take in production? Or are you just trying to free up disk space? - yes, doing that on DEV server I would like to know whether any errors gonna occur, how much time it's gonna take etc.,

What is the process to move these files from the table onto the file storage area? - Not sure the coding details of moving file but after that column Document looks as below: (before and after)

How desperate of a situation is freeing up this space? - I wouldn't say is very desperate since we were going to start working on it couple years ago.

ratbak Ten Centuries Points: 1358 More actions · Answer 10

2 years!

DBCC ShrinkFile is pretty straightforward command, not sure how to make it more efficient.

That's the point -- it's not great and we can't make it better.

Why do you want to shrink the file? Are you running out of space? Or does somebody just want to reallocate it for something else?

If you could move all the documents in Dev to Azure at once, you could create a new table, copy the data from old table to new table, drop old table, & rename new table to the original table name.

If you can't migrate all at once, you could perhaps rename the current table, create another for migrated files, create a view that unions the old table & new, using the original name of the existing table, & insert migrated rows to the new table & delete from existing table as they get migrated.

Michael L John One Orange Chip Points: 27055 More actions · Answer 11

olegserdia wrote:

This dev server is woefully inadequate for this job. The shrink file in SQL server is also not the most efficiently written code. - DBCC ShrinkFile is pretty straightforward command, not sure how to make it more efficient.

No, I mean the code internal to SQL server is pretty poor. There's nothing a DBA can do to make it better.

olegserdia wrote:

Is the point of this exercise to determine how long it will take in production? Or are you just trying to free up disk space? - yes, doing that on DEV server I would like to know whether any errors gonna occur, how much time it's gonna take etc.,

Well I think, at best, the only thing you will gain is how the process will work. Your hardware is very inadequate.

If this process is going to take a few years, has there been a recognition that there may need to be some significant coding changes made to accommodate this?

There's probably a million holes in this, but can the table that contains these files be moved to a different file and filegroup? Then, as the files are moved out of these tables, move the changed rows to a different table in the existing file/filegroup?

If you move that table to it's own location, shrinking the original file should be far less intensive. The new file group can be left alone until it's empty, and then it can simply be deleted.

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

fgrodriguez SSC Veteran Points: 270 More actions · Answer 12

Perhaps you should check the information contained in the link I send you: compacting LOBs could help you.

https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-index-transact-sql?view=sql-server-ver15#reorganize-with--lob_compaction---on--off--

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 13

@olegserdia

Check the page density on the Clustered Index for your big table that you deleted images from. Because you used the IMAGE datatype, they should have gone "out-of-row" but it's worth checking to see what condition the Clustered and other indexes have for page density. You may have to rebuild them to free up the space.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

olegserdia Old Hand Points: 381 More actions · Answer 14

olegserdia

Old Hand

Points: 381

February 22, 2022 at 5:47 am

#3986690

The output of sys.dm_db_index_physical_stats