Moving tables to new file group

Question

Moving tables to new file group

wak_no1

SSCrazy

Points: 2400
More actions
March 16, 2015 at 5:10 am

#315663

Hi
I'm presented with an issue where by I need to reclaim a fair bit of unused space currently sat in the primary data file for my database. I don't want to run DBCC SHRINKDATABASE as we all know this could potentially have a some serious negative effects relating to index fragmentation.
So, how do I get the free space out of the data file? - I've decided to:
1. Add new new file group
2. Add a clustered index for all tables on the new file group
3. Shrink the primary file group as much as possible (hopefully giving me the free space back)
4. Drop the newly created clustered indexes for all tables
There are no clustered indexes currently for any of the tables!, so me temporarily creating/dropping one shouldn't be an issue.
Theoretically I think this will work, what do you guys think? Are there any other ways I can get the free space back to the OS?
Thanks.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

wak_no1 (3/16/2015)
Hi
I'm presented with an issue where by I need to reclaim a fair bit of unused space currently sat in the primary data file for my database. I don't want to run DBCC SHRINKDATABASE as we all know this could potentially have a some serious negative effects relating to index fragmentation.
So, how do I get the free space out of the data file? - I've decided to:
1. Add new new file group
2. Add a clustered index for all tables on the new file group
3. Shrink the primary file group as much as possible (hopefully giving me the free space back)
~~4. Drop the newly created clustered indexes for all tables~~

Looks good (with amendments)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

wak_no1 SSCrazy Points: 2400 More actions · Answer 2

Thanks for the reply.

Why shouldn't I drop the clustered indexes? - they're not needed (hence why non exist), as they're only needed for the move.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Table doesn't have clustered index != table doesn't need clustered index

All (almost all) tables should have a clustered index as that's what the storage engine is designed around. That the table doesn't have one could be a result of the original database architect/designer not understanding SQL Server.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQL Guy 1 SSCoach Points: 15979 More actions · Answer 4

When creating clustered indexes, you can add page level compression.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

SQL Guy 1 (3/16/2015)
When creating clustered indexes, you can add page level compression.

Which is not necessarily a good thing (can massively increase CPU usage). You can compress a heap as well, so that's not an argument for a clustered index.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Igor Micev SSC-Dedicated Points: 33110 More actions · Answer 6

wak_no1 (3/16/2015)
Thanks for the reply.
Why shouldn't I drop the clustered indexes? - they're not needed (hence why non exist), as they're only needed for the move.

You can use the DROP_EXISTING option. E.g:

CREATE CLUSTERED INDEX CIX_NAME

ON <SCHEMA.TABLE>(<COLUMN>)

WITH (DROP_EXISTING = ON) ON [<FG_Name>]

Igor Micev,My blog: www.igormicev.com

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Igor Micev (3/16/2015)
wak_no1 (3/16/2015)
Thanks for the reply.
Why shouldn't I drop the clustered indexes? - they're not needed (hence why non exist), as they're only needed for the move.
You can use the DROP_EXISTING option. E.g:
CREATE CLUSTERED INDEX CIX_NAME
ON <SCHEMA.TABLE>(<COLUMN>)
WITH (DROP_EXISTING = ON) ON [<FG_Name>]

Drop Existing is for when there is a clustered index and you want to change it. In the OP's situation, there is no clustered index, so he's creating one to move the data.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Igor Micev SSC-Dedicated Points: 33110 More actions · Answer 8

GilaMonster (3/16/2015)
Igor Micev (3/16/2015)
wak_no1 (3/16/2015)
Thanks for the reply.
Why shouldn't I drop the clustered indexes? - they're not needed (hence why non exist), as they're only needed for the move.
You can use the DROP_EXISTING option. E.g:
CREATE CLUSTERED INDEX CIX_NAME
ON <SCHEMA.TABLE>(<COLUMN>)
WITH (DROP_EXISTING = ON) ON [<FG_Name>]
Drop Existing is for when there is a clustered index and you want to change it. In the OP's situation, there is no clustered index, so he's creating one to move the data.

If so than it's ok. Just to appoint that the DROP_EXISTING option can be also used for nonclustered indexes.

Igor Micev,My blog: www.igormicev.com