Moving a table with LOB from one Filegroup to another

Posted on 2010-01-06
Last Modified: 2012-05-08
I have a table with an image datatype (i.e. a LOB).
I'd like to move this table from one filegroup to another, dedicated to this table alone.
I have done this by dropping the primary key index and re-creating on the new filegroup.

sp_help now reports the table as being on that filegroup.

However, sp_spaceused for the table shows around 124Mb (test database) and the file for that filegroup is only 50Mb. So I presume this didn't move all the data.

I have tried reorganise indexes, and rebuild stats.

Have I missed something? I'd like to get this right before I move on to production databases where this table represents several GB of data.
Question by:PCIIain
    LVL 60

    Expert Comment

    How big is the file in the operating system?
    LVL 13

    Accepted Solution

    Hmm, found it. According to BOL :-

    TEXTIMAGE_ON { filegroup | "default" }
    Are keywords that indicate that the text, ntext, image, xml, varchar(max), nvarchar(max), varbinary(max), and CLR user-defined type columns are stored on the specified filegroup.

    TEXTIMAGE_ON is not allowed if there are no large value columns in the table. TEXTIMAGE_ON cannot be specified if  is specified. If "default" is specified, or if TEXTIMAGE_ON is not specified at all, the large value columns are stored in the default filegroup. The storage of any large value column data specified in CREATE TABLE cannot be subsequently altered.
    LVL 60

    Expert Comment

    LVL 13

    Author Comment

    @chapmandew - 52Mb, same as shown in Shrink files.
    Seems to be that the textimagedata is not transferred, and can't be altered. I'm going to have to work out how to copy, and re-name the table.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
    I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
    Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
    how to add IIS SMTP to handle application/Scanner relays into office 365.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now