Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2009

    Exclamation Unanswered: Re- distribute extents to a number of datafiles

    I have a Microsoft SQL 2005 database, originally created with a single database
    data file (XXXDATA1.mdf) and a single transaction log file (XXXLOG.ldf).
    The first database file is now 12640000 total extents large (ca. 800 GB),
    so we decided to create 9 additional database files (XXXDATA2.ndf to
    XXXDATA10.ndf) and re- distribute the data from the first one.

    We created nine *.ndf files first with 720000 extents (ca. 45 GB) each file.
    All files belong to the same PRIMARY file group, as the first XXXDATA1.mdf file.
    This was working fine.

    Then we started the procedure of extents re- allocation with the help of:


    We also used the following DBCC procedure to control the re- allocation
    of the extents:


    After a long time of running the DBCC SHRINKFILE command we received
    the following error message:

    DBCC SHRINKFILE: System table SYSFILES1 Page 1:94029742 could not be
    moved to other files because it only can reside in the primary file of the database.
    Msg 2555, Level 16, State 1, Line 1
    Cannot move all contents of file "XXXDATA1" to other places to complete the emptyfile operation.
    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

    OK, I know, the system tables can not be removed from the first file. But we
    do not really want to remove all the data / all extents from the first file. We would
    like to partially re- distribute some data, then really shrink the file to a reasonable
    size ( 80 to 100 GB ). The issue is, the status of the re- allocation looks like the following:

    FG FID Total Extents Used Extents Name Filename
    1 1 12640000 11388587 XXXDATA1 F:\XXXDATA1\XXXDATA1.mdf
    3 1 720000 106515 XXXDATA2 F:\XXXDATA2\XXXDATA2.ndf
    4 1 720000 106516 XXXDATA3 F:\XXXDATA3\XXXDATA3.ndf
    5 1 720000 106512 XXXDATA4 F:\XXXDATA4\XXXDATA4.ndf
    6 1 720000 106520 XXXDATA5 F:\XXXDATA5\XXXDATA5.ndf
    7 1 720000 106513 XXXDATA10 F:\XXXDATA10\XXXDAT10.ndf
    8 1 720000 106519 XXXDATA6 F:\XXXDATA6\XXXDATA6.ndf
    9 1 720000 106524 XXXDATA7 F:\XXXDATA7\XXXDATA7.ndf
    10 1 720000 106496 XXXDATA8 F:\XXXDATA8\XXXDATA8.ndf
    11 1 720000 107283 XXXDATA9 F:\XXXDATA9\XXXDATA9.ndf

    For sure we still have a lot of information / extents, which can be moved from
    the first file to the other new created files. But DBCC has an issue and can not
    execute the move.

    Have somebody experience with such an issue? Any ideas, what can we do next?

  2. #2
    Join Date
    Nov 2004
    on the wrong server
    Provided Answers: 6
    Nothing you have done actually moves the data. The last time I was doing this exercise some time ago, I think I dropped the clustered indexes on my tables and recreated them specifying the new filegroup. Since the index pages are the data pages in a clustered index, that should move the tables.

    See the following for syntax...

    CREATE INDEX (Transact-SQL)
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts