06-13-09, 15:15 #1Registered User
- Join Date
- Jun 2009
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:
DBCC SHRINKFILE ( XXXDATA1.mdf, EMPTYFILE )
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?
06-14-09, 09:19 #2Annie's Dog Walker
Provided Answers: 6
- Join Date
- Nov 2004
- on the wrong server
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.