April 19, 2011 at 9:33 am
Hi,
I was wondering if someone could help me, I have table which is currently about 500mb on my dev machine (although is much bigger in other environments) and I want to move it to another file group.
I've built the file group on another drive and after doing a bit of reading it appears that all I need to do was move the PK, so I did this using;
CREATE UNIQUE CLUSTERED INDEX PK_TABLENAME
ON dbo.TableName(ColumnA ASC,ColumnB ASC)
WITH DROP_EXISTING
ON NEWFILEGROUP
GO
When I go into SSMS, it says the the table is on the other new file group, but the size of the file has not changed (for the new one), it remains at 12mb, which I set it, when it should be 500mb.
I've then tried dropping all the FK and PK, and then rebuilding the PK on the new file group, but still no joy. I've obviously mis-understood something or am missing something out.
Can anyone help me out?
Thanks,
Nic
April 19, 2011 at 11:51 am
Only thing I can think of would be making sure that the filegroup has the file you are looking for properly assigned. You are doing the correct method for moving a table. If the file isn't changing, maybe the file isn't in the filegroup to which you are moving it? Or maybe there are multiple files in that filegroup?
April 19, 2011 at 3:39 pm
Just curious really, your index name has PK, but your DDL is only creating a UNIQUE CLUSTERED INDEX.
CREATE UNIQUE CLUSTERED INDEX PK_TABLENAME
ON dbo.TableName(ColumnA ASC,ColumnB ASC)
WITH DROP_EXISTING
ON NEWFILEGROUP
Is there a reason why you did not go for a full PK?
SET XACT_ABORT ON
GO
BEGIN TRAN
GO
ALTER TABLE dbo.TableName DROP CONSTRAINT PK_TABLENAME
GO
ALTER TABLE dbo.TableName ADD CONSTRAINT PK_TABLENAME
PRIMARY KEY CLUSTERED (ColumnA ASC,ColumnB ASC)
ON [NEWFILEGROUP]
GO
COMMIT TRAN
GO
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 19, 2011 at 4:08 pm
Does this table have any image, binary, or LOB (ntext, varchar(max) etc) data in it?
That data requires a different process in order to move it. In order to move that kind of data to the new filegroup, you need to rebuild the table.
Here is an article on one method to do that (otherwise you would need to script everything out manually)
http://blog.jitbit.com/2010/04/moving-sql-table-textimage-to-new.html
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 19, 2011 at 4:20 pm
this is the gist of the change that would be applied
CREATE TABLE [dbo].[yourtable](
yourcolumns,
PRIMARY KEY CLUSTERED
(
[PK_COlumn] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [newdatagroup] TEXTIMAGE_ON [newImgFilegroup]
GO
Bolding added
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 20, 2011 at 2:45 am
Hi Jason,
Spot on, it does contain a BLOB column, I'll read through the link and code you provided and then implement that.
Hi opc.three,
Yeah sorry, it's a bit of code not written by myself, so the naming is a little off, good spot, I got caught up in the big things and didnt spot the naming, I'll be sure to correct it.
Thanks all.
Nic
April 20, 2011 at 10:51 am
You're welcome. I had run into a similar thing many moons ago.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 20, 2011 at 11:11 am
Nic-306421 (4/20/2011)
Hi Jason,Spot on, it does contain a BLOB column, I'll read through the link and code you provided and then implement that.
Indeed, nice catch!
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply