You are hereMoving large table to another file group
Moving large table to another file group
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
Tags


