We have are trying to reduce the size of our database. I know shrinking is generally bad but please ignore that fact for now we have our reasons. There are several tables that use TEXT data types and a bunch more that use VARCHAR(MAX) which in many cases caused the data to spill and be stored in OFF-ROW-DATA allocations. Everything is in the Primary filegroup. I believe this off-row and lob data (text) is what makes a shrink take 30 minutes per MB (roughly). Usually it causes blocking so we have never been able to keep it running.
My plan is to move the LOB data by recreating the table in a new file group and then copying over all the data to that file group. My questions are two.
Is OFF-ROW storage and Lob_Data storage the same thing? I ask this question because when I move the tables I would like to know if I should just move tables with TEXT data type or also move VARCHAR(MAX) tables that have been identified as having off-row storage allocations?
My other question is about the indexes. Should also move the indexes for said tables or can I recreate them in the original file group or does this not matter in terms of improving the amount of time it takes to shrink?
Lastly I know TEXT, NTEXT types have been replaced. And as part of the moving tables to a new file group, I would likely recreate these tables as VARCHAR(MAX).
If anyone has experience with a good strategy please share. When I say off-row and lob-data, I attached the script that I am using to identify a starting point (aka what tables biggies)