Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


2k5 MDF Emptyfile -> NDF


2k5 MDF Emptyfile -> NDF

Author
Message
DuncEduardo
DuncEduardo
SSC-Enthusiastic
SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)

Group: General Forum Members
Points: 132 Visits: 460
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
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47412 Visits: 44399
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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


DuncEduardo
DuncEduardo
SSC-Enthusiastic
SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)

Group: General Forum Members
Points: 132 Visits: 460
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.
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47412 Visits: 44399
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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


DuncEduardo
DuncEduardo
SSC-Enthusiastic
SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)

Group: General Forum Members
Points: 132 Visits: 460
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.
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47412 Visits: 44399
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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


DuncEduardo
DuncEduardo
SSC-Enthusiastic
SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)

Group: General Forum Members
Points: 132 Visits: 460
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?
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47412 Visits: 44399
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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


DuncEduardo
DuncEduardo
SSC-Enthusiastic
SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)

Group: General Forum Members
Points: 132 Visits: 460
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 ;-)
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47412 Visits: 44399
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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search