asked on

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.

ASKER CERTIFIED SOLUTION

Barry Cunney

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

itpartner_danmark

ASKER

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

itpartner_danmark

ASKER

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?

itpartner_danmark

ASKER

It seems that the table pictures now is on the filegroup called 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.

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?

SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

itpartner_danmark

ASKER

DBCC SHOWFILESTATS

itpartner_danmark

ASKER

I have now taken the DB offline and then online again. Still same result.

What does this mean: Text filegroup PRIMARY

SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

itpartner_danmark

ASKER

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.

SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

itpartner_danmark

ASKER

I dont know what you mean / sorry.

So the text filegroup has nothing to do with my problem with data not moving?

itpartner_danmark

ASKER

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?

itpartner_danmark

ASKER

SOLVED: It took 12 minutes to apply the change.

Scott Pletcher

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.