0

How to move big table (more then 100GB) from one filegroup to another?

After I have started the script generated by SSMS:

ALTER TABLE [log].[MyTable] DROP CONSTRAINT [PK_MyTable] WITH ( ONLINE = OFF )
GO


ALTER TABLE [log].[MyTable] ADD  CONSTRAINT [PK_MyTable] PRIMARY KEY CLUSTERED 
(
    [MyTableId] ASC
) ON [SECONDARY]
GO

Nothing happened during an hour of work: SECONDARY file did not grow up. Only RAM constatly growing. I have canceled the script (SSMS cancel) after an hour and it stoped... immediately. Looks like it even not started to do any work.

What kind of preparation SQL Server do during this period? How to avoid it? I need to move files during manageable time period.

P.S. Process was not locked, sp_who2 status RUNNING. Buffer reveals that Iam still in "DROP CONSTRAINT" statement (that why I have decided to stop it - DROP during an hour? ).

There is similar question "How can I move a table to another filegroup in MS SQL Server?" but my is specific only to BIG tables.

Roman Pokrovskij
  • 9,449
  • 21
  • 87
  • 142
  • 1
    If you have other indexes on the table you will need to drop those first before dropping the PK, assuming the PK is clustered what will be happening is that these other indexes will need to be rebuilt first to use a rowId instead of the clustered key, so drop all indexes and re-add after. – Stu Jan 04 '22 at 10:26
  • 1
    There is also the `MOVE TO` syntax, see https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-table-transact-sql?view=sql-server-ver15 – Charlieface Jan 04 '22 at 11:00
  • 2
    The `CREATE UNIQUE CLUSTERED INDEX...WITH (DROP_EXISTING=ON)` will relocate the table without rebuilding the non-clustered indexes and avoids the sort as well. – Dan Guzman Jan 04 '22 at 12:29
  • @Stu Thank you. It explains me the situation. – Roman Pokrovskij Jan 04 '22 at 21:48
  • I should have mentioned @DanGuzman's comment, I do that frequently but never gave it a thought. – Stu Jan 04 '22 at 21:49

0 Answers0