Posted by Erland Sommarskog on 03/08/06 01:20

(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/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

• Next in forum: Re: beginner: "Login failed for user 'sa'."
• Prev in forum: Re: How to check if DB Constraints are enabled in a database?
• Thread view: Re: Problem with database filegroup restructuring in SQL Server?

[Reply to this message]