Post reply

Sorting out .NDF files

  • May 17, 2019 at 9:28 am

    #3644471

    Hi all

     

    We have a failry large database (around 400GB) that has split into the following:-

    1 x .MDF file

    1 x .LDF file

    9 x .NDF files

    All the .MDF and .NDF files are of different sizes (although they do have the same growth increments set, which is 10% and unimited max size).

    The log file also has the maximum file size set to the 2TB default.

     

    I'm think, due to the above, that read/write performance is being impacted (although I can't prove it without the tidy-up).

     

    I've attached a screenshot of the current set-up.

     

    What I want to do is to tidy it up (and reset the log file max size to something reasonable).

     

    My thoughts are as follows:

    Get rid of all the current .NDF files by pulling them back into the .MDF file

    Set up some new .NDF files (I'm not sure how many as yet but i'm thinking keep the 9 to keep the individual file sizes reasonable) with a decent initial size (probably 50GB) and a better growth increment)

    Split the .MDF file back into the new .NDF files

     

     

    Can anyone see any issues with what I want to do or suggest any better ideas?

     

    TIA

    Richard

    Attachments:
    You must be logged in to view attached files.

  • May 17, 2019 at 4:09 pm

    #3644646

    Are these in one filegroup or multiple filegroups?

  • May 17, 2019 at 8:28 pm

    #3644807

    I think they're in one file-group but I'd have to check on Monday to make sure.

     

    Richard

  • May 18, 2019 at 3:19 pm

    #3645013

    In one filegroup, you want to have the files the same size. Otherwise you have different data being written at different rates according to the proportional file fill.

    https://www.sqlskills.com/blogs/paul/investigating-the-proportional-fill-algorithm/

     

    If these are separate filegroups, then you can size the files in different filegroups separately, but keep them the same size.

  • May 20, 2019 at 8:16 am

    #3645246

    Thanks steve

     

    I've just double-checked and they are in separate file-groups.

    I'm still concerned that they are different sizes and would like to tidy them up (if only, for nothing else, to assuage my inner OCD-ness).

     

    I'd still like to pull all the NDF's back into the main MDF and then split them back out properly.  I'm also fairly certain that everything is one one spindle so I'm not sure if that makes a difference.

     

    I'm 99% certain on how to pull the NDFs back in to the MDF but I'm not sure how to split them back out again and transfer the data (or does the data transfer automgically when I create the the new files/filegroups?).

     

    Thanks

    Richard

  • May 20, 2019 at 12:14 pm

    #3645326

    Hold the phone a minute.  Have you determined WHY the NDF files exist?  If they're associated with partitioning, you absolutely DO NOT WANT TO MAKE THEM THE SAME SIZE.  It would be a total waste of space.

    You can change the file settings to clean them up but until you've identified the real purpose as to why someone made multiple NDF files, I wouldn't change a thing about how they operate.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • May 20, 2019 at 1:23 pm

    #3645355

    Hi Jeff

     

    As far as I can see, there is no reason for these files to exist (there are no partitions to any of our databases).

     

    I did read that NDF files can be created when there is file fragmentation on the disk.

     

    We also moved from a physical to a virtual machine a couple of years ago and I do remember that those extra files were there at that point.

     

    Our vendor (a 3rd-party) claim to know next to nothing about SQL installs (apart from giving us minimum specifications and what versions they support) so I doubt if they'd know when/how/why the extra files exist.

     

    Richard

  • May 20, 2019 at 2:05 pm

    #3645375

    .NDF files are explicitly created. Nothing to do with fragmentation. Usually you create multiple files to split the load for the filegroup to multiple spindles, or because you want to spread the space across drives. Older advice sometimes talked about separating different types of objects, or even indexes, but that sometimes gets taken to extremes with no benefit.

    If you have no idea why this is there, and want to move to one filegroup/file, then you would slowly move the data. You can use DBCC SHRINKFILE with the empty option to clean files, or rebuild indexes with a target of the MDF file. Then when you have empty files/filegroups, drop them.

    Script your code and make sure that tables aren't targeting other filegroups or you might have issues in the process. As Jeff mentioned, do your best diligence to understand anything you can about the set up and proceed 1 at a time.

    I'm not sure there's benefit to having 9 files, but that's an oddly specific number.

  • May 20, 2019 at 2:31 pm

    #3645394

    It looks to me like those .ndf files have been created by or for use with the Full Text Search feature.

  • May 20, 2019 at 2:34 pm

    #3645400

    Thanks for that.

     

    I was going to do this first on our test database (after 2 backups to make sure that at least one of them is good!).

    It's either that or backup the test database, restore it under a different name and tinker with that (I think that's my preferred option).

     

    My theory was to proceed as follows:-

    • Move all the NDF's back into the main MDF file
    • Create the new filegroups
    • Separate the data across to them to even the split
    • Shrink the main MDF file to reclaim what will now be unused space
    • Do a full rebuild on all the indexes to get rid of any fragmentation

     

    The reason for 9 NDF files was to get 10 files in total (plus one for the log).

    I was going to keep to 9 NDFs in order to keep a similar setup to what we currently have.

     

    Doing some more thinking about it (especially from Steve's comments above), is it worth hav ing the extra NDFs as we are now on a virtual machine and I haven't a clue about what spindles anything is sat on?

     

    Cheers

    Richard

  • May 20, 2019 at 2:39 pm

    #3645406

    Stop thinking NDF v MDF. This is filegroups that you're considering. The files in a filegroup all need to be the same size. The filegroups do not need to be the same size. There's no real value in consistency here. Use the size that is appropriate for the filegroup divided by files in that filegroup.

     

  • May 20, 2019 at 2:43 pm

    #3645408

    If the .ndf files are being used by Full Text Indexing you will not be able to remove the files without first dropping the full-text indexes and then removing the full text catalogs.

    You can check if you have full text indexing catalogs with the following query.

     

    select * from sys.fulltext_catalogs

  • May 21, 2019 at 8:59 am

    #3645627

    Steve - Thanks for that, it looks like we've only got one file in each of the filegroups.

     

    tripleAxe - We do have full-text indexing but I'm not convinced it's used/necessary.

    I'll have a chat with our vendor to see if it's actually needed/used.

     

    Thanks

    Richard

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply