Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
I have a SQL Server 12.0.5223.6 instance with a database on it.
I'm wondering if I can create a filegroup for one (or more) tables while it's running? Or does it have to have no data in the tables?
I could take the DB offline, but I can't take it offline for long. Right now all the data is inside Data.mdf. I'm hoping to split out some data from maybe three or four tables into separate filegroups.
Could this be done?
You can definitely add a filegroup to a database while it's running, but it will have no data until you create a table in it, or rebuild a table onto the new filegroup.
This would mean dropping the clustered key on the table, and re-creating it on the filegroup:
CREATE CLUSTERED INDEX IK_Example ON Example ( id ) ON NewFileGroup;
OR
ALTER TABLE Example ADD CONSTRAINT PK_Example PRIMARY KEY CLUSTERED (id) ON NewFileGroup;
The Microsoft Docs on this one is pretty good.
