remove a filegroup

I have a database with several filegroups.
I would like to remove 1 filegroup. This filegroup contains 1 file.
I know when I want to remove a file from a filegroup which contains multiple
files, I first have to empty that file (dbcc shrinkfile with emtyfile).
But how do you empty a file in a filegroup in which that file is the only
file.
This is necessary I think because only empty files can be removed from a
filegroup, and only an empty filegroup can be removed from a database.
Can someone help me with this one ?
thxFranky,
You can move a table to a different filegroup by re-creating the clustered
index using the ON FILEGROUP clause of the CREATE INDEX statement. NC
indexes can be moved this way as well.
HTH
Jerry
"Franky" <Franky@.discussions.microsoft.com> wrote in message
news:A28170DF-7CC0-4855-A447-EB2AB8A9C26B@.microsoft.com...
>I have a database with several filegroups.
> I would like to remove 1 filegroup. This filegroup contains 1 file.
> I know when I want to remove a file from a filegroup which contains
> multiple
> files, I first have to empty that file (dbcc shrinkfile with emtyfile).
> But how do you empty a file in a filegroup in which that file is the only
> file.
> This is necessary I think because only empty files can be removed from a
> filegroup, and only an empty filegroup can be removed from a database.
> Can someone help me with this one ?
> thx|||Jerry, thx for the advice, but I should have specified more in detail in my
question that that is not a good solution. There are hundreds of tables in
this file with each of them having several indexes. So I was looking for an
other kind of solution.
"Jerry Spivey" wrote:
> Franky,
> You can move a table to a different filegroup by re-creating the clustered
> index using the ON FILEGROUP clause of the CREATE INDEX statement. NC
> indexes can be moved this way as well.
> HTH
> Jerry
> "Franky" <Franky@.discussions.microsoft.com> wrote in message
> news:A28170DF-7CC0-4855-A447-EB2AB8A9C26B@.microsoft.com...
> >I have a database with several filegroups.
> > I would like to remove 1 filegroup. This filegroup contains 1 file.
> > I know when I want to remove a file from a filegroup which contains
> > multiple
> > files, I first have to empty that file (dbcc shrinkfile with emtyfile).
> > But how do you empty a file in a filegroup in which that file is the only
> > file.
> > This is necessary I think because only empty files can be removed from a
> > filegroup, and only an empty filegroup can be removed from a database.
> >
> > Can someone help me with this one ?
> >
> > thx
>
>|||Hmmm...I don't know. Might consider detach/attach moving the file(s) to
another physical location if that is the issue here.
HTH
Jerry
"Franky" <Franky@.discussions.microsoft.com> wrote in message
news:E355035F-78C2-4C69-B9D1-83401390297E@.microsoft.com...
> Jerry, thx for the advice, but I should have specified more in detail in
> my
> question that that is not a good solution. There are hundreds of tables in
> this file with each of them having several indexes. So I was looking for
> an
> other kind of solution.
>
> "Jerry Spivey" wrote:
>> Franky,
>> You can move a table to a different filegroup by re-creating the
>> clustered
>> index using the ON FILEGROUP clause of the CREATE INDEX statement. NC
>> indexes can be moved this way as well.
>> HTH
>> Jerry
>> "Franky" <Franky@.discussions.microsoft.com> wrote in message
>> news:A28170DF-7CC0-4855-A447-EB2AB8A9C26B@.microsoft.com...
>> >I have a database with several filegroups.
>> > I would like to remove 1 filegroup. This filegroup contains 1 file.
>> > I know when I want to remove a file from a filegroup which contains
>> > multiple
>> > files, I first have to empty that file (dbcc shrinkfile with emtyfile).
>> > But how do you empty a file in a filegroup in which that file is the
>> > only
>> > file.
>> > This is necessary I think because only empty files can be removed from
>> > a
>> > filegroup, and only an empty filegroup can be removed from a database.
>> >
>> > Can someone help me with this one ?
>> >
>> > thx
>>|||That does not solve my problem, I would like to delete the filegroup with
that single file in it. And be back in the state of having 1 primary
filegroup, and 1 user filegroup which is the default with several files in it.
"Jerry Spivey" wrote:
> Hmmm...I don't know. Might consider detach/attach moving the file(s) to
> another physical location if that is the issue here.
> HTH
> Jerry
> "Franky" <Franky@.discussions.microsoft.com> wrote in message
> news:E355035F-78C2-4C69-B9D1-83401390297E@.microsoft.com...
> > Jerry, thx for the advice, but I should have specified more in detail in
> > my
> > question that that is not a good solution. There are hundreds of tables in
> > this file with each of them having several indexes. So I was looking for
> > an
> > other kind of solution.
> >
> >
> >
> > "Jerry Spivey" wrote:
> >
> >> Franky,
> >>
> >> You can move a table to a different filegroup by re-creating the
> >> clustered
> >> index using the ON FILEGROUP clause of the CREATE INDEX statement. NC
> >> indexes can be moved this way as well.
> >>
> >> HTH
> >>
> >> Jerry
> >> "Franky" <Franky@.discussions.microsoft.com> wrote in message
> >> news:A28170DF-7CC0-4855-A447-EB2AB8A9C26B@.microsoft.com...
> >> >I have a database with several filegroups.
> >> > I would like to remove 1 filegroup. This filegroup contains 1 file.
> >> > I know when I want to remove a file from a filegroup which contains
> >> > multiple
> >> > files, I first have to empty that file (dbcc shrinkfile with emtyfile).
> >> > But how do you empty a file in a filegroup in which that file is the
> >> > only
> >> > file.
> >> > This is necessary I think because only empty files can be removed from
> >> > a
> >> > filegroup, and only an empty filegroup can be removed from a database.
> >> >
> >> > Can someone help me with this one ?
> >> >
> >> > thx
> >>
> >>
> >>
>
>