April 2, 2013 at 5:33 am
Hi,
I wonder if someone can help?
I was attempting to move contents of mdf to a secondary data file ndf but this failed about 20% through the process with the error "cannot move all contents of file .... using emptyfile command"
Command was: dbcc shrinkfile(1,emptyfile).
There are 3 files in same filegroup MDF, LDF and newly created NDF.
I created NDF on separate drive as this had plenty of space
MDF was around 5GB with almost 5GB free space.
When the process failed, around 1GB of data was "emptied" to NDF.
My question is this: the original MDF is still showing 5GB. Can I simply remove the NDF and try another method on MDF, i.e. SHRINKFILE (1, 100)
Someone suggested installing SP3 on 2k5 but I'm not in a position to do this nor is it advisable due to some applications running off this server.
So, I just wish to shrink MDF and avoid the NDF method but I don't want to lose any data with this incomplete process.
My original plan was:
a)create ndf
b)empty mdf using shrinkfile/emptyfile
c)shrink mdf
d)empty ndf to move data back to mdf
e)remove ndf
Any suggestions?
Thanks,
Dunc
April 2, 2013 at 5:37 am
Couple points...
The ldf is not in the filegroup. Log files are not part of data filegroups.
The primary file (the mdf, file 1) cannot be emptied, there will always be data in there.
Why are you moving data back and forward across files? What's the goal here?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 2, 2013 at 5:44 am
Hi Gail,
Thanks for replying.
Yes, sorry I knew log was in different filegroup - typo.
I have a warehouse server (2k8) with limited space available on one drive.
Used 99GB out of 100GB on drive but there is actually about 90GB free space on this drive as the main data is stored on the larger 300GB drive now.
I wanted to simply shrink this 99GB mdf file without fragmentation issues and so I was thinking about the "NDF Empty" technique.
So, I went to another server which is currently offline, happens to be running 2k5, in order to test this process and hopefully then use on 2k8 server.
April 2, 2013 at 8:41 am
Shrinking with EmptyFile will give you just as much fragmentation as shrinking without that option. It's not a magic good shrink.
If all you're trying to do is remove empty space in the file, do a once off shrinkfile and then rebuild the indexes.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 2, 2013 at 9:59 am
ok that's fine, but can you advise on my original point?
What to do with the NDF which contains the 1GB?
How do I restore the MDF to what it was?
Do I need to do anything else or can I just drop the NDF?
Let me know if I'm being unclear - I think I put it all in original post
Sorry to labour the point.
April 2, 2013 at 10:04 am
ShrinkFile with the EmptyFile option the ndf then drop it. Then just shrink the remaining data file to a reasonable size and rebuild your indexes afterwards.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 2, 2013 at 10:11 am
ok thanks Gail - just one last thing however - failed to mention that I have already shrunk the mdf.
Will the action you suggest work ok in this scenario?
i.e. will emptying the NDF effectively append the NDF data back into the MDF whether that is shrunk or not?
I guess it's my narrow understanding of what the original emptyfile command from mdf to ndf actually does, i.e. does it "copy" or "cut" the data?
April 2, 2013 at 11:40 am
DuncEduardo (4/2/2013)
ok thanks Gail - just one last thing however - failed to mention that I have already shrunk the mdf.Will the action you suggest work ok in this scenario?
Yes.
I guess it's my narrow understanding of what the original emptyfile command from mdf to ndf actually does, i.e. does it "copy" or "cut" the data?
From Books Online
EMPTYFILE
Migrates all data from the specified file to other files in the same filegroup.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 3, 2013 at 4:29 am
ok Gail works fine thanks. Noticed couple of things after the process completed:
a) The mdf data file could not be shrunk below it's new size (approx 1GB)
b) The log file could not be shrunk as all pages allocated
For b) I didn't need this data and the dB is test so I put dB into Simple recovery mode and then was able to shrink the log with truncateonly, then reverting back to Full recovery
For a) I'm assuming that in rebuilding the indexes any unallocated space would then be freed
May the force be with you 😉
April 3, 2013 at 5:23 am
DuncEduardo (4/3/2013)
For b) I didn't need this data and the dB is test so I put dB into Simple recovery mode and then was able to shrink the log with truncateonly, then reverting back to Full recovery
If it's a test, why full recovery?
Maybe take a read through this: http://www.sqlservercentral.com/articles/Administration/64582/
For a) I'm assuming that in rebuilding the indexes any unallocated space would then be freed
No. Rebuilding indexes will never reduce the file size.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply