SQL Server Performance Forum – Threads Archive

Moving large table

Hi All, I have a database on my 2005 cluster that contains a table with 110GB of data in it. I need to archive this data onto another filegroup which is located on some cheaper storage – it’s always about the cost <img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ /> To make matters worse the table is mainly ntext data (95%) and has a full text index on it. So my question is how would you move this table between filegroups? The options I can see are: 1) Move the table using ALTER TABLE or the GUI. This however will do it in a transaction which means the log will get huge (if anyone could estimate the size it will get I would be interested). Currently the log drive has only 50GB – but I could possibly find some more for a bit. 2) Move the table in batches of say 100,000 rows using INSERT… SELECT… However, this way I will lose my full text index (which takes 24-36 hours and lots of CPU to rebuild). Has anyone got any better ideas? I know full text has some new options in 2005 like detach and backup – but I’m not sure how this would work in this situation. Any ideas appreciated. Simon

Looks like critical and huge work is involved, referhttp://codebetter.com/blogs/raymond.lewallen/archive/2005/06/22/64989.aspx about detach & attach of FT information in your case. Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

This looks like a job for partitions! Create a partition function to segregate the data you want to archive from the data you want live. Then create a partition scheme with the new file and old file groups group.
However, if you are considering moving the whole database (not your original premise) it’s much simpler to dettach the database, move the file, reattach it – see "Detaching and Attaching a Database "
BOL:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/d0de0639-bc54-464e-98b1-6af22a27eb86.htm You may want to implement partitioning in any case for performance reasons.

]]>