Problem with database filegroup restructuring in SQL Server?

[jkv10_2005]

Hi,

I have the following SQL procedure I am running to clean up a filegroup
and move all data to a single .MDF file:

use <db_name>
print 'Move <db_name> db contents to MDF file'
DBCC SHRINKFILE ('<db_name>_1_Data', EMPTYFILE)
DBCC SHRINKFILE ('<db_name>_Log', EMPTYFILE)
DBCC SHRINKFILE ('<db_name>_2_Data', EMPTYFILE)
DBCC SHRINKFILE ('<db_name>_log2', EMPTYFILE)
go

Trouble is that I get the following error:

Server: Msg 1105, Level 17, State 2, Line 3
Could not allocate space for object '<company_name>. Inv. Line' in
database '<db_name>' because the 'Data Filegroup 1' filegroup is full.

There are five files:
- a .MDF file (which is already set as the primary filegroup, 43MB in
size);
- <db_name>_1_data.ndf (this file is 11GB in size);
- <db_name>_2_data.ndf (this file is 4GB in size, empty & cannot be
shrunk);
- 2 .LDF files

I have been able to remove the "<db_name>_2_data.ndf" file
successfully, but moving the objects from filegroup 1 to the primary
filegroup has yielded the same error message every time. Any ideas on
what I can do to resolve this?

[Erland Sommarskog]

(jk********@yahoo.com) writes:

I have the following SQL procedure I am running to clean up a filegroup
and move all data to a single .MDF file:

use <db_name>
print 'Move <db_name> db contents to MDF file'
DBCC SHRINKFILE ('<db_name>_1_Data', EMPTYFILE)
DBCC SHRINKFILE ('<db_name>_Log', EMPTYFILE)
DBCC SHRINKFILE ('<db_name>_2_Data', EMPTYFILE)
DBCC SHRINKFILE ('<db_name>_log2', EMPTYFILE)
go

Trouble is that I get the following error:

Server: Msg 1105, Level 17, State 2, Line 3
Could not allocate space for object '<company_name>. Inv. Line' in
database '<db_name>' because the 'Data Filegroup 1' filegroup is full.

As I understand you can only use EMPTYFILE to move data within a
filegroup, you cannot use it to move the data another file group.

To do this, you need to use CREATE CLUSTERED INDEX WITH DROP_EXISTING to
move the tables. Of course, you need to move non-clustered indes as well.
Please check Books Online, for the exact syntax.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx