Re: Problem with database filegroup restructuring in SQL Server?
- From: Erland Sommarskog <esquel@xxxxxxxxxxxxx>
- Date: Tue, 7 Mar 2006 23:20:47 +0000 (UTC)
(jkv10_2005@xxxxxxxxx) 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@xxxxxxxxxxxxx
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
.
- References:
- Problem with database filegroup restructuring in SQL Server?
- From: jkv10_2005
- Problem with database filegroup restructuring in SQL Server?
- Prev by Date: Re: How to check if DB Constraints are enabled in a database?
- Next by Date: Re: beginner: "Login failed for user 'sa'."
- Previous by thread: Problem with database filegroup restructuring in SQL Server?
- Next by thread: How to check if DB Constraints are enabled in a database?
- Index(es):
Relevant Pages
|
