Remove files from databases

Hi, I've created a new filegroup, then added to it a new filename, then create a table pointing to this filegroup. So far everything is ok, but if I want to revert the process in this way:

1- Drop the table - OK

2- Drop the filegroup - OK

3- Drop the filename - ERROR: Msg 5009, Level 16, State 9, Line 2
One or more files listed in the statement could not be found or could not be initialized.

When I query a catalog view with this query:

Select*FromSys.Database_Files

I get the file that I had recently deleted, it is offline but I can not delete it using the ALTER DATABASE instructions.

This is the code I use:

Use [TESTING ]

Go

-- Add a FileGroup to the Database

AlterDatabase TESTING AddFileGroup FG01

Go

-- Add a file to a FileGroup

AlterDatabase TESTING AddFile(

NAME= TESTING_DATA01,

FILENAME='D:\Sql Server\Data\Testing_Data01.ndf',

SIZE= 1 MB,

MAXSIZE= 10 MB,

FILEGROWTH= 1 MB

)ToFileGroup FG01

Go

-- Create the table using a specific FileGroup

CreateTable TABLE1 (

Id IntNotNull,

FirstName VarChar(30)NotNull,

LastName VarChar(30)NotNull,

BirthDate SmallDateTime

)

On FG01

So far everything works ok, but in the next code there is an error:

-- Delete the table

Drop Table TABLE1

Go

-- Remove a FileGroup from the Database

AlterDatabase TESTING Remove FileGroup FG01

Go

-- Try to remove the file

AlterDatabase TESTING Remove File TESTING_DATA01

When I try to remove the file there is an error:

"Msg 5009, Level 16, State 9, Line 2
One or more files listed in the statement could not be found or could not be initialized"

You simply need to change the order of your Remove statements to remove the file BEFORE you remove the filegroup.|||Thanks for the answer, but how can I delete the entries in the Sys.Database_Files that already exists ?

Remove FileGroup

I have removed all the files from a filegroup ...when I do sp_helpdb I don't see the filegroup... but when i try to remove the filegroup using ALTER DATABASE <DB> REMOVE FILEGROUP, it says The filegroup 'XXX' cannot be removed because it is not empty

any idea...

Thanks

Ranga

Hi

I've encountered the same issue.

Hi guys, is there a solution for this problem.

Below is other post for the same issue.
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=446565&SiteID=1

thanks,
Larry

|||

Hi Ranga,

Maybe a partition scheme is still mapped to the filegroups.

Try dropping the partition scheme before removing the filegroups

|||

We are having this same problem. We want to drop old files and filegroups from our partitioned database. We can drop the files. However, there does not seem to be anyway to remove a filegroup from an existing partition, even if the filegroup is empty. Dropping the partition is NOT an option in our case, as the partitioned table contain 2 billion+ records and to rebuild the partition would likely take 2 weeks or longer.

Q to Microsoft support: How do we remove a Filegroup from a partition? Once a FG is added to a partition, are we stuck with it being there indefinately?

Thanks.

Remove FileGroup

I have removed all the files from a filegroup ...when I do sp_helpdb I don't see the filegroup... but when i try to remove the filegroup using ALTER DATABASE <DB> REMOVE FILEGROUP, it says The filegroup 'XXX' cannot be removed because it is not empty

any idea...

Thanks

Ranga

Hi

I've encountered the same issue.

Hi guys, is there a solution for this problem.

Below is other post for the same issue.
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=446565&SiteID=1

thanks,
Larry

|||

Hi Ranga,

Maybe a partition scheme is still mapped to the filegroups.

Try dropping the partition scheme before removing the filegroups

|||

We are having this same problem. We want to drop old files and filegroups from our partitioned database. We can drop the files. However, there does not seem to be anyway to remove a filegroup from an existing partition, even if the filegroup is empty. Dropping the partition is NOT an option in our case, as the partitioned table contain 2 billion+ records and to rebuild the partition would likely take 2 weeks or longer.

Q to Microsoft support: How do we remove a Filegroup from a partition? Once a FG is added to a partition, are we stuck with it being there indefinately?

Thanks.

sql

Remove FileGroup

I have removed all the files from a filegroup ...when I do sp_helpdb I don't see the filegroup... but when i try to remove the filegroup using ALTER DATABASE <DB> REMOVE FILEGROUP, it says The filegroup 'XXX' cannot be removed because it is not empty

any idea...

Thanks

Ranga

Hi

I've encountered the same issue.

Hi guys, is there a solution for this problem.

Below is other post for the same issue.
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=446565&SiteID=1

thanks,
Larry

|||

Hi Ranga,

Maybe a partition scheme is still mapped to the filegroups.

Try dropping the partition scheme before removing the filegroups

|||

We are having this same problem. We want to drop old files and filegroups from our partitioned database. We can drop the files. However, there does not seem to be anyway to remove a filegroup from an existing partition, even if the filegroup is empty. Dropping the partition is NOT an option in our case, as the partitioned table contain 2 billion+ records and to rebuild the partition would likely take 2 weeks or longer.

Q to Microsoft support: How do we remove a Filegroup from a partition? Once a FG is added to a partition, are we stuck with it being there indefinately?

Thanks.

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 ?
thx
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
|||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...
>
>
|||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...[vbcol=seagreen]
> 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:
|||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...
>
>
sql

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
> >>
> >>
> >>
>
>

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...
>
>|||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...[vbcol=seagreen]
> 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:
>|||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 i
t.
"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...
>
>