Shrinking a database file accross filegroups
-
I inherited a database with multiple data files accross two file groups. Dividing up the database like this does nothing for performace and the size of the data files is not an issue so I want to migrate the data into 1 data file on the same filegroup. In EM I can use Shrink Database and specify one of the files and choose the option to migrate the data out of the one file and into others. I want to do this until I only have 1 data file. The problem I am experiencing is I get an error stating that the filegroup I have specified as the container of the file I want to migrate from is full. I have set restrictions on file growth in each file in filegroup 2 and set the file growth on a file in the other file group to grow without restriction. Basically I want to empty and delete all the files in the 2nd file group into one file in the original filegroup and SQL doen't want to let me move data accross filegroups.
Is it possible to migrate the data from one filegroup to another?
-
I don't use enterprise manager except for the cases in which I have no other choise. My approach is
1. find all the tables that are on the Filegroup that I need to Empty
2. Find the name of the clustered indexes on those tables
3. Create a script like this:
Create clustered index IDX_XXX on TableName(Col1,...) with DROP_EXISTING ON NEWFILEGROUP
4. Drop old file with
ALTER DATABASE Dbname remove file Filename
5. Drop old Filegroup
ALTER DATABASE Dbname remove filegroup Filegroupname
GO HOME
BTW Take a backup FIRST
* Noel -
noeld ... short, sweet and it'll work !!!
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply