February 27, 2014 at 10:17 am
Hello,
I have a database with one mdf and 2 ndf files all in the same PRIMARY filegroup. I want someone to confirm if there will be no data loss if as per this article (http://www.sqldbpros.com/2010/12/sql-server-combine-mdf-files-the-easy-way/) I simply shrink/empty the ndf files and then remove them. It seems the data will simply merge back into the mdf file with no data loss. Has anyone done this successfully with no data loss?
I will of course backup the database prior.
February 27, 2014 at 12:38 pm
If your files are empty then you can perform
DBCC SHRINKFILE(LogicalNameOfFileToRemove, EMPTYFILE)
But you say "..no data loss"?
Can you empty/remove a non-empty file? You cannot.
Why do you need to merge the ndf files to the mdf? Multiple files to a filegroup don't bring performance loss.
They can be more efficiently used by the IO system if they are put on different drives, or disk arrays or on EVA storage and bring you performance.
Regards,
Igor
Igor Micev,My blog: www.igormicev.com
February 27, 2014 at 1:18 pm
http://msdn.microsoft.com/en-us//library/ms189493.aspx
"EMPTYFILE
Migrates all data from the specified file to other files in the same filegroup. Because the Database Engine no longer allows data to be placed in the empty file, the file can be removed by using the ALTER DATABASE statement."
So it does look like it does migrate the data properly.
It is a good point about performance... I thought perhaps having the one MDF file on just one disk would allow faster read/writes as it doesn't have to "switch" between disks but it does seem best practice to have separate disks/data files so you can have read/write/seek operations happening at the same time. I suspect though that this virtual box's data drives are part of the same SAN so in that case it would add unnecessary I/O.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply