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?

(jkv10_2005@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, esquel@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