Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

2k5 MDF Emptyfile -> NDF Expand / Collapse
Author
Message
Posted Tuesday, April 2, 2013 5:33 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, June 11, 2014 4:16 AM
Points: 33, Visits: 236
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
Post #1437830
Posted Tuesday, April 2, 2013 5:37 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 6:06 AM
Points: 42,468, Visits: 35,537
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 2008, MVP
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

Post #1437834
Posted Tuesday, April 2, 2013 5:44 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, June 11, 2014 4:16 AM
Points: 33, Visits: 236
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.
Post #1437838
Posted Tuesday, April 2, 2013 8:41 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 6:06 AM
Points: 42,468, Visits: 35,537
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 2008, MVP
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

Post #1437936
Posted Tuesday, April 2, 2013 9:59 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, June 11, 2014 4:16 AM
Points: 33, Visits: 236
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.
Post #1437983
Posted Tuesday, April 2, 2013 10:04 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 6:06 AM
Points: 42,468, Visits: 35,537
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 2008, MVP
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

Post #1437989
Posted Tuesday, April 2, 2013 10:11 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, June 11, 2014 4:16 AM
Points: 33, Visits: 236
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?
Post #1437994
Posted Tuesday, April 2, 2013 11:40 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 6:06 AM
Points: 42,468, Visits: 35,537
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 2008, MVP
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

Post #1438027
Posted Wednesday, April 3, 2013 4:29 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, June 11, 2014 4:16 AM
Points: 33, Visits: 236
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
Post #1438246
Posted Wednesday, April 3, 2013 5:23 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 6:06 AM
Points: 42,468, Visits: 35,537
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 2008, MVP
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

Post #1438262
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse