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:
DBCC SHOWFILESTATS
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?
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:
DBCC SHOWFILESTATS
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?
Last edited by a moderator: