Moving table to new file group

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Moving table to new file group

Author

Topic

venkath
Posting Yak Master

202 Posts

Posted - 2007-10-17 : 23:59:40

Hi all

I have a table called ACTIVATION_CONSUMPTION which is in PRIMARY file group, in order to move this table to new file group [FG_ACTV], I have done the following

1. ALTER DATABASE [MYDB] ADD FILEGROUP [FG_ACTV] (i have not attached a file to this file group)

2. IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[ACTIVATION_CONSUMPTION]') AND name = N'ACTIVATION_CONSUMPTION_PK')
ALTER TABLE [dbo].[ACTIVATION_CONSUMPTION] DROP CONSTRAINT [ACTIVATION_CONSUMPTION_PK]
GO

3.ALTER TABLE [dbo].[ACTIVATION_CONSUMPTION] ADD CONSTRAINT [ACTIVATION_CONSUMPTION_PK] PRIMARY KEY CLUSTERED
(
[ACTIVATION_CONSUMPTION_ID] ASC
)WITH (PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [FG_ACTV]

The above command executed successfully and now the table is in new file group [FG_ACTV]

My question is how come the table was moved to new file group though it has no files attached to it?

Thanks in advance.

crosan
Starting Member

13 Posts

Posted - 2007-10-18 : 09:24:56

That's odd, I get the error message I would have expected you to get:

Msg 622, Level 16, State 3, Line 1
The filegroup "FG_ACTV" has no files assigned to it. Tables, indexes, text columns, ntext columns, and image columns cannot be populated on this filegroup until a file is added.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
The statement has been terminated.

Are you sure that no file was attached to the new fg?:

venkath
Posting Yak Master

202 Posts

Posted - 2007-10-18 : 09:41:15

i got the same error when i tried to insert a record into activation_consumption table

I have not attached a file to [FG_ACTV]

my concern is how come table moved to this file group which has no files in it.

Thanks

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-10-18 : 23:07:40

How do you know table is moved?

venkath
Posting Yak Master

202 Posts

Posted - 2007-10-19 : 15:43:43

by seeing the properties of the table from management studio.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-10-19 : 23:16:32

Then you should see file names in the group as well there.