Post reply

Moving Objects From One Filegroup to Another

  • September 13, 2006 at 9:32 am

    #75058

    Hello All!

    What's the most efficient way to move a table from one filegroup to another within the same SQLServer 2000 DB?

    Thanks!

    Allen

  • September 13, 2006 at 9:55 am

    #660142

    Recreate the clustered index on the new filegroup.

    -Eddie

    Eddie Wuerch
    MCM: SQL

  • September 13, 2006 at 10:26 am

    #660151

    Eddie:

    Thanks for the quick response.  Several of the tables do not have a clustered index, only the heap (indid = 0) is on dbo.sysindexes.  For these, what's the most efficent way to move from one filegroup to another?

    Thanks!

    Allen

  • September 13, 2006 at 11:02 am

    #660165

    And why don't they have a clustered index (best/worst pratice)?

  • September 13, 2006 at 11:50 am

    #660178

    In Enterprise Mgr, right click the table, and choose Design Table.  With the Design Table form open, go up to the tool bar and click the 'Table and Index Properties' button.  On the Table tab, select your 'Table Filegroup'.  Click the Save button and look at the hourglass while your table is moved.

  • September 13, 2006 at 11:53 am

    #660180

    Some of these tables are merely "dumps" to be moved out, via DTS Packages, to flat files.  These final tables are never used in the true sense of an RDMS, that is to say used in a query.  So, we did not want to waste space by creating any unneccessary indices.

    I thought there might be a system SP that would facilitate this.  If not, there ought to be, IMHO.

    Thanks!

    Allen

  • September 13, 2006 at 11:56 am

    #660183

    Randy:

    Thanks for the suggestion and it works fine; for a few tables.  I am trying to clean up and need to move > 50 tables from one filegroup to another.  I would like to script this and have it execute as a Job over the weekend.  It's been my experience that mose everything the Enterprise Manager accomplishes can be duplicated via the built-in system SPs.  I was wondering what SP, if any, was executed when you do this via EM.

    Thanks!

    Allen

  • September 13, 2006 at 12:06 pm

    #660189

    I looked at the script. Not much help there.  It basically creates the new table on the secondary file group, remake the constraints and other objects, insert the data, drop/rename.

    Nothing new there !!!

  • September 13, 2006 at 12:43 pm

    #660202

    For the heap tables, you can move them by creating a clustered index on them using the ON [new filegroup] option.  Once they're moved over, you're free to either drop the clustered index or leaving it in place.

    -Eddie

    Eddie Wuerch
    MCM: SQL

  • September 14, 2006 at 6:18 am

    #660342

    Eddie:

    Thank you.  I will create clustered indices on the tables in the new filegroup.

    Thanks!

    Allen

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

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