Moving a table with LOB from one Filegroup to another

Posted on 2010-01-06
Medium Priority
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
  • 2
  • 2
LVL 60

Expert Comment

ID: 26190260
How big is the file in the operating system?
LVL 13

Accepted Solution

PCIIain earned 0 total points
ID: 26190290
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

ID: 26190303
LVL 13

Author Comment

ID: 26190315
@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.

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

612 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