February 20, 2006 at 4:38 pm
All:
I recently took over a database of fairly large size (35 Gb). It has two filegroups, PRIMARY and DATA, with DATA being the default. The DATA filegroup has seven *.ndf files.
My project involves consolidating these eight files into one. So far, I've scripted out DBCC SHRINKFILE EMPTYFILE for each file in sysfiles that belongs to the DATA filegroup. Files 1-6 empty out and drop just fine, but the last file which contains all data that has been moved from the other files won't shrink.
I receive the error message 'Server: Msg 1105, Level 17, State 2, Line 2
Could not allocate space for object 'XYZ' in database 'db' because the 'DATA' filegroup is full.'. I added space to the file in question (5 Gb free) and have verified that the volume the file is sitting on has 250+ Gb free. Any suggestions?
Is the EMPTYFILE command attempting to dump the data into the only filegroup left (PRIMARY), or is it trying to empty into itself? I've also tried setting PRIMARY to default and then shrinking to no avail.
Thanks!
Mark
February 21, 2006 at 1:47 am
EMPTYFILE clause can be used only to migrate data within the same filegroup. So you will have to transfer all data stored in your DATA filegroup to PRIMARY filegroup first.
I know of two ways to do it: to create/rebuild clustered indexes using ON clause or use bcp/DTS to export data and import data back to tables created on PRIMARY filegroup.
Whatever you choose, backup your db before the operation and if possible test on non-production server first.
Hope this helps.
February 21, 2006 at 7:53 am
Thanks, Martin. I was thinking along those lines but wasn't able to find anything out there. Much appreciated sir, I'll try that!
MM
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply