You are hereMoving large table to another file group

Moving large table to another file group


By hkai - Posted on 23 February 2010

I have a large errorlog table in my database, with 2M records and 1GB data. I'd like to create a standalone file group for the error log. I tried to use management studio to modify the table, but it's timeout. So I have to do it manually. I'm sure there's better way to do this, but here's my note which works:

  • in management studio, database properties, create a new file, and a new filegroup
  • right click the table, and select: script table as->create to->new query editor window
  • in the window, modify the sql script to create the able in the new filegroup, and change the table name to errorlog1: create table [dbo].[errorlog1] () on [errorlog]
  • Now you have a new table on the new filegroup, use this to insert all the data from errorlog to errorlog1:
    set IDENTITY_INSERT errorlog1 ON insert errorlog1(errorid, description, createtime) select * from errorlog go
  • You should see this message as result: (2042251 row(s) affected)
  • Rename the table:
    sp_rename ErrorLog, ErrorLog2 sp_rename ErrorLog1, ErrorLog
  • Now you have ErrorLog and ErrorLog2, verify your code still works fine and drop errorlog2
  • shrink the primary filegroup to save some sapece