Emptying the primary data file

Greetings all
I have a database where the primary filegroup has been allocated very
fragmented. I have tried to defragment my database by adding a new file
to the primary filegroup, allocation space properly and calling DBCC
SHRINKFILE('File1', EMPTYFILE) to move all the data from one file to the
other.
However, when I try to remove the original file i get an error message
that "The primary data file cannot be removed". After some research, I
discovered that the system tables seem to be allocated din the primary
data file and that EMPTYFILE does not move these.
Is there any way to move system tables to another file in the PRIMARY
filegroup making the other file the new primary datafile?
/Thomas Kejser
Thomas Kejser wrote:
> Greetings all
> I have a database where the primary filegroup has been allocated very
> fragmented. I have tried to defragment my database by adding a new
> file to the primary filegroup, allocation space properly and calling
> DBCC SHRINKFILE('File1', EMPTYFILE) to move all the data from one
> file to the other.
> However, when I try to remove the original file i get an error message
> that "The primary data file cannot be removed". After some research, I
> discovered that the system tables seem to be allocated din the primary
> data file and that EMPTYFILE does not move these.
> Is there any way to move system tables to another file in the PRIMARY
> filegroup making the other file the new primary datafile?
> /Thomas Kejser
I don't think you can remove the primary data file. See this thread as
it mentnions a possible hack. Just test it on a dev server and back up
your database before trying it in production:
http://webservertalk.com/t429554.html
David Gugick
Imceda Software
www.imceda.com
|||if you have extent fragmentation then adding another filegroup will not
necessarilly resolve your problem. you need to
1) drop the additional file (run dbcc shrinkfile with the emptyfile option
and delete secondary file)
2) run dbcc checkdb with repair_rebuild to repair allocation errors and
rebuild indexes.(this will fix extent fragmentation currently experienced)
3) if this is resolved but you still feel that you need to defragment the
MDF file .. .then you can either
- stop sql server
- copy mdf and ldf file off the OS drive
- defragment drive
- copy mdf file back to the OS drive
OR
- use a utility which it's name escapes me at the moment(SQLDefrag?)
- this will allow you to perform task without copying the database files
off the drive(I have not found it as effective as the first option)
HTH
"Thomas Kejser" <thomas@.kejser.org> wrote in message
news:%23bNEQe7IFHA.2640@.TK2MSFTNGP09.phx.gbl...
> Greetings all
> I have a database where the primary filegroup has been allocated very
> fragmented. I have tried to defragment my database by adding a new file
> to the primary filegroup, allocation space properly and calling DBCC
> SHRINKFILE('File1', EMPTYFILE) to move all the data from one file to the
> other.
> However, when I try to remove the original file i get an error message
> that "The primary data file cannot be removed". After some research, I
> discovered that the system tables seem to be allocated din the primary
> data file and that EMPTYFILE does not move these.
> Is there any way to move system tables to another file in the PRIMARY
> filegroup making the other file the new primary datafile?
> /Thomas Kejser
|||You might want to consider moving your data to another filegroup and use
your primary filegroup just for database metadata.
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:O48tom7IFHA.656@.TK2MSFTNGP14.phx.gbl...
> Thomas Kejser wrote:
> I don't think you can remove the primary data file. See this thread as it
> mentnions a possible hack. Just test it on a dev server and back up your
> database before trying it in production:
> http://webservertalk.com/t429554.html
>
>
> --
> David Gugick
> Imceda Software
> www.imceda.com