What happens when I drop a huge table with LOBs? will the space be reused?

  • NJ-DBA

    SSChampion

    Points: 13832

    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?

  • Site Owners

    SSC Guru

    Points: 80378

    Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • webrunner

    SSC-Dedicated

    Points: 30102

    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

  • NJ-DBA

    SSChampion

    Points: 13832

    No, not really.  That article talks about deleting row.  I'm talking about a table that has been dropped.

Viewing 4 posts - 1 through 4 (of 4 total)

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