SQL server move table to another file in filegroup

Have created a new filegroup for a database and want to move a table called "Pictures" from the primary filegroup to the new "Pictures" filegroup.

Dont know how to move the specific table to the new filegroup. I have done some googling and can see that I have to move the Index.

In SQL Server Management Studio I navigate to server / Databases / Mydatabase/ Tables/ Pictures / Indexes:

I see the following three indexes:
IxTblPictures (Non-Unique, Non-Clustered)
IxTblPictures1 (Non-Unique, Non-Clustered)
PkTblPictures (Clustered)

If I right click/properties on the clustered index and select Storage in the left pane, then the right pane is grayed out, so I cannot choose the new filegroup. WHY???

On the Non-Unique, Non-Clustered indexes I can change settings in the right pane under Storage.

I have never tried this before. Am I on the right track or is there a better way of moving a table to another file group.
itpartner_danmarkAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Barry CunneyCommented:
ALTER TABLE Pictures
    DROP CONSTRAINT [PkTblPictures]
        WITH (MOVE TO Pictures);

-- Check the after move
SELECT    DISTINCT(object_name(id))
        , filegroup_name(groupid)
FROM    sysindexes
WHERE    object_name(id) = 'Pictures';
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
itpartner_danmarkAuthor Commented:
In the meanwhile I did this: What is the difference?

CREATE UNIQUE CLUSTERED INDEX [PkTblPictures] ON [dbo].[tblPictures]
(
      [PictureID] ASC
) WITH (DROP_EXISTING = ON)
ON [Pictures]
GO


CREATE NONCLUSTERED INDEX [IxTblPictures] ON [dbo].[TblPictures]
(
      [WorkID],[PictureID] ASC
) WITH (DROP_EXISTING = ON)
ON [Pictures]
GO


CREATE NONCLUSTERED INDEX [IxTblPictures1] ON [dbo].[TblPictures]
(
      [PictureID],[WorkId] ASC
) WITH (DROP_EXISTING = ON)
ON [Pictures]
GO
0
itpartner_danmarkAuthor Commented:
This one tells me that the Pictures filegroup is almost empty. It says 6 GB free space in the Pictures filegroup and that is the initial size.

SELECT      file_id, name, physical_name,
            'Free Space (MB)' =
            (size - FILEPROPERTY(name,'SpaceUsed'))/128.00
FROM        sys.database_files


So Im wondering if I did something wrong?
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

itpartner_danmarkAuthor Commented:
It seems that the table pictures now is on the filegroup called Pictures:

Placement of tabe Pictures

But when I check for free space it seems that no data has been moved since there is allmost 6 gb free space in the filegroup Pictures (GalArtoPics) and that is the initial size of the file.

Free space in physical files
When will the data be moved. Do I have to take the Database offline and online again, and how long will it take for the DB to come online again?
0
Scott PletcherSenior DBACommented:
The CREATE INDEX commands you ran should have worked, and I think they did.

Try running:
DBCC SHOWFILESTATS
and see how much space it shows as used.

Also, SQL will sometimes defer large DELETEs until later, but not INSERTs, AFAIK.

You could try running UPDATE STATISTICS on that table.  Or do a "SELECT <column_name_not_in_index> FROM" the table to force a table scan.
0
itpartner_danmarkAuthor Commented:
DBCC SHOWFILESTATS

Filestats
0
itpartner_danmarkAuthor Commented:
I have now taken the DB offline and then online again. Still same result.

What does this mean: Text filegroup PRIMARY
Textfilegroup
0
Scott PletcherSenior DBACommented:
Hmm, I don't see a fg named "Pictures" in that output, which was in the table commands.  But there is a lot of data stored in the primary fg ("GalArto") there.

If the CREATE INDEX commands completed and you did not receive an error, the tables should be moved.
0
itpartner_danmarkAuthor Commented:
PICTURES is the Filegroup 2 in the filestats output.

I got no errors when running the CREATE INDEX commands, and it also seems that the TblPictures is stored on the PICTURES filegroup.

But its strange that the data doesnt move. This table stores a lot of highres images.
0
Scott PletcherSenior DBACommented:
You moved the clustered index, i.e. the table itself, but that won't affect a separate text filegroup.  AFAIK, once you specify a text filegroup, you can't alter/move it.
0
itpartner_danmarkAuthor Commented:
I dont know what you mean / sorry.

So the text filegroup has nothing to do with my problem with data not moving?
0
itpartner_danmarkAuthor Commented:
I think I can change it here. But when i save the table properties i get a timeout error. I can overrule that in the tools / options / designers

Then I think I can save it. But what will happen for the users using the DB- will they get any interruption in the meanwhile?

Text image filegroup
0
itpartner_danmarkAuthor Commented:
SOLVED: It took 12 minutes to apply the change.
0
Scott PletcherSenior DBACommented:
Thanks for pointing this out.  I never use the GUI to make table structure changes, so I was not aware of this.  

I didn't see anything in the normal SQL commands to make this change.  My guess is that the GUI is writing a script that: copies the table data out, (re)creates the table, and copies the data back in.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.