January 17, 2020 at 2:28 pm
Thanks in advance for your help.
I have a 5TB database with mutliple filegroups. The primary filegroup has one file, the .mdf and which is about 2.5TB. About 700GB of the space in the primary FG is made up of a single table which contains LOBs (Varbinar(max) column). After copying the table to a new filegroup and dropping the table in the primary FG, I see the "unused space" in the primary FG increase as expected.
Because I have freed such a large chunk of space and the 2.5TB file is close to the capacity of the drive it resides on, I am tempted to shrink the file to 2TB or so. Two question:
1.) If I don't shrink the file, will the unused LOB space be reused?
2.) When shrinking the file, I see a lot of LOBCompaction operations happening, but I don't think there are many LOBs left in the database. Does the shrink operation still have to do work on LOBs from a table which has been dropped?
January 18, 2020 at 3:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
January 21, 2020 at 5:22 pm
Hi,
Do you mean something like the topic of this article?
Deleting the data from the database is the easy part, a simple delete loop will handle that nicely. The problem is when you delete rows from a table which contains LOB data the LOB pages aren’t cleared when they are deallocated.
https://itknowledgeexchange.techtarget.com/sql-server/deleting-lob-data-and-shrinking-the-database/
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
January 21, 2020 at 8:18 pm
No, not really. That article talks about deleting row. I'm talking about a table that has been dropped.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply