Shrink files with LOB data.

Question

Greetings.

https://dba.stackexchange.com/questions/257512/is-there-any-fastest-way-to-shrink-lob-data-in-sql-server

I'm encountering the scenario described in that link. I have a ton of free space, and a lot of my tables have LOB columns in them. I'm certainly not the first person to have this issue, and the common answer is to create a new table/ export the data from the original table to it/ drop the old table.

Unfortunately I need to do this for 300 tables.

Howeve, I just discovered this: https://decipherinfosys.wordpress.com/2007/08/03/text-in-row-option-in-sql-server/

Seems like a much safer bet. Has anyone else done it? Any pitfalls?

Answer 1

No, the option that you executed means that if the row is smaller than 1000 bytes, then it will be in-row. Else it will be out-of-row.

And, remember that text in row only applies to text, ntext and image. Also, the command (sp_tableoption) doesn't move any data, to do the reorganization of the physical data, you would have to do a rebuild.

Also, large value types out f rows only affect the new lob types, i.e., varchar(max), nvarchar(max), varbinary(max), xml, geography and geometry. It has no effect on other types. And, again the commend it self doesn't move any data, a rebuild has to be done.

Answer 2

Hi @chrisrdba ,

>and the common answer is to create a new table/ export the data from the original table to it/ drop the old table.

Yes. You can follow this way and build index on tables in order to save space.
And there is another method that is not recommended is to shrink the data file with DBCC SHRINKFILE and rebuild index.
Freeing up space once used by lob data types is never a simple and easy task, unless you drop and create tables.
Another thing to note is that any table specific permissions for users will be lost so be careful when dropping and creating objects.

Best regards,
Seeya

---

If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar queries.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

What data type(s) do you have, exactly?

For the new LOB types, the text is in row by default. The option for the new types is "large value types out of row", and the "text in row" option doesn't do anything for the new types. Note that data will only be in row if it fits (row being less and 8000 bytes).