November 20, 2014 at 11:22 am
Hi good people,
I added a new filegroup to my database, and added two new files to it.
I have a table that is around 2.2 GB in size (on primary filegroup)
I tried moving that table to the other filegroup, using all the methods normally suggested (recreating the clustered index with drop existing, etc...) and it does so successfully, but not the actual data!
I can see that the PK is now on the other filegroup, but that filegroup is barely 100 MB in size.
I want the 2+ GB to be moved there, not just the indexes.
The table has 2 INT columns, 1 DATETIME column, 1 NVARCHAR(75) and 1 NTEXT.
Please advise.
Thanks!
November 20, 2014 at 1:21 pm
It's almost certainly the NTEXT that is the bulk of the data, and you can't move it without recreating the table entirely. Btw, you should change the ntext to nvarchar(max) as quickly as you can.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
November 20, 2014 at 1:21 pm
Nevermind, figured it out.
It was because of the NTEXT field. LOB columns don't get moved so easily. I have to go the "create table/rename table" route.
November 20, 2014 at 1:23 pm
In other words, you need to create a new table that explicitly specifies the clause:
CREATE TABLE ... (
...
TEXTIMAGE_ON [new_filegroup_name]
...
)
After you copy the old table to the new one, for example, you could drop the old table, and rename the new table to the old name.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
November 20, 2014 at 1:47 pm
Another trick to move that is to partition the table with an unbounded function. Then you can rebuild the clustered index and merge the data into the new partition.
It is a little quicker than rebuilding the table.
Drawback is enterprise edition is required.
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply