SQL Server Performance

Data Splitting: MDF & NDF Files

Discussion in 'General DBA Questions' started by DaveLavers, May 5, 2006.

  1. DaveLavers New Member

    I have a database with mdf & ldf files only.

    I want to create an ndf file on a new drive.

    Is there a way to split the current data between the mdf and the ndf files.

    I have been using DBSS Shrinkfile without success.

    Thanks,
    Dave
  2. Luis Martin Moderator

    mdf,ndf are conventional names.

    If you need to split current database, said in other drive or not, you have to create a new filegroup (check BOL) and then you can move any table to the new filegroup.

    Is that what you need?

    Luis Martin
    Moderator
    SQL-Server-Performance.com

    Although nature commences with reason and ends in experience it is necessary for us to do the opposite, that is to commence with experience and from this to proceed to investigate the reason.
    Leonardo Da Vinci

    Nunca esperes el reconocimiento de tus hijos, eso ocurrirá luego de tu muerte


    All postings are provided “AS IS” with no warranties for accuracy.



  3. satya Moderator

  4. DaveLavers New Member

    I was hoping to do this without creating a new filegroup.

    Is that possible?

    Thanks,
    Dave
  5. Luis Martin Moderator

    No.

    Luis Martin
    Moderator
    SQL-Server-Performance.com

    Although nature commences with reason and ends in experience it is necessary for us to do the opposite, that is to commence with experience and from this to proceed to investigate the reason.
    Leonardo Da Vinci

    Nunca esperes el reconocimiento de tus hijos, eso ocurrirá luego de tu muerte


    All postings are provided “AS IS” with no warranties for accuracy.



  6. joechang New Member

    ??? luis: what do you mean,
    a file group can have many files, the fileid type is smallint, so presumably there could be 32k files (total, not per filegroup)

    the shrink file doesn't seem to have fine control, try running dbcc dbreindex,
    preferably, drop the big nonclustered indexes first

    alternate method
    add a new file group, + files
    drop the noncl. IX, drop the clust IX/PK,
    create the PK/Cl IX on the new file group,
    shrink the files on the original file group
    drop the P/CL IX, recreate on the original file group
  7. Luis Martin Moderator

    I understand the member don't want to split files in the same filegroup. Sorry.


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    Although nature commences with reason and ends in experience it is necessary for us to do the opposite, that is to commence with experience and from this to proceed to investigate the reason.
    Leonardo Da Vinci

    Nunca esperes el reconocimiento de tus hijos, eso ocurrirá luego de tu muerte


    All postings are provided “AS IS” with no warranties for accuracy.



  8. DaveLavers New Member

    The dbcc dbreindex does appear to move the indexes to the ndf.

    I guess that I'll need to use filegroups to move data (table by table).

    I appreciate all of your help with this.

    Thanks,
    Dave
  9. satya Moderator

    Need not to create new filegroup and you can follow referred link to move the indexes & data.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  10. cmdr_skywalker New Member

    if you want to have control which data goes to what drive, you may want to create a separate filegroup and use that filegroup in the DDL. However, if you just want to have additional space, add a file in the existing filegroup in the new drive and let SQL handle the allocation.

    May the Almighty God bless us all!
    www.empoweredinformation.com

Share This Page