Filegroup with no file, not possible to remove it!

Hi All,
I have a problem with one of my filegroup. Every files have been removed,
but when I try to remove the filegroup, I have the following error:
Msg 5042, Level 16, State 8, Line 1
The filegroup 'TEXT' cannot be removed because it is not empty.
When I run the following query, to see which filegroup have no
allocation_unit:
SELECT * FROM sys.filegroups f
WHERE NOT EXISTS (SELECT * FROM sys.allocation_units i WHERE
i.data_space_id = f.data_space_id)
The Filegroup named 'TEXT' is returned.
Does anyone have an idea to know why I cannot remove this filegroup?
Regards,
JeremyAs a sanity check, verify that there are no remaining files in the
filegroup:
EXEC sp_helpfilegroup 'TEXT'
or
SELECT *
FROM sys.data_spaces AS ds
JOIN sys.database_files AS df ON
df.data_space_id = ds.data_space_id
WHERE ds.NAME = 'TEXT'
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Jeremy Calles" <Jeremy Calles@.discussions.microsoft.com> wrote in message
news:C24E794B-0241-495A-8E87-3F57F9BB4E67@.microsoft.com...
> Hi All,
> I have a problem with one of my filegroup. Every files have been removed,
> but when I try to remove the filegroup, I have the following error:
> Msg 5042, Level 16, State 8, Line 1
> The filegroup 'TEXT' cannot be removed because it is not empty.
> When I run the following query, to see which filegroup have no
> allocation_unit:
> SELECT * FROM sys.filegroups f
> WHERE NOT EXISTS (SELECT * FROM sys.allocation_units i WHERE
> i.data_space_id = f.data_space_id)
> The Filegroup named 'TEXT' is returned.
> Does anyone have an idea to know why I cannot remove this filegroup?
> Regards,
> Jeremy|||Hi Dan,
Thanks for your message, but I have already checked that no files are
assigned to this filegroup. And of course, there are no files.
So this is why it seems really strange to me.
I don't know what could it be.
Regards,
Jeremy|||Jeremy
DBCC SHRINKFILE command has an EMPTYFILE parameter.Specify a file that is
on filegroup and run it
"Jeremy Calles" <JeremyCalles@.discussions.microsoft.com> wrote in message
news:A3D24B42-5CB4-4E27-AD85-15DCB1BDA701@.microsoft.com...
> Hi Dan,
> Thanks for your message, but I have already checked that no files are
> assigned to this filegroup. And of course, there are no files.
> So this is why it seems really strange to me.
> I don't know what could it be.
> Regards,
> Jeremy|||Hi Uri,
As I have said in my first post: There is no files in this filegroups.
I have removed all files using DBCC SHRINKFILE
When I execute "EXEC sp_helpfilegroup 'TEXT'"I have no result.
This is why I have posted in this filegroup. Cause a filegroup without any
files attached cannot be removed.
Regards,
Jeremy|||Sorry, did not read it properly, how did you try to remove it ? by EM or
ALTER DATABASE... in QA?
"Jeremy Calles" <JeremyCalles@.discussions.microsoft.com> wrote in message
news:D418E95E-EE3A-4049-BFDB-8432D31000F7@.microsoft.com...
> Hi Uri,
> As I have said in my first post: There is no files in this filegroups.
> I have removed all files using DBCC SHRINKFILE
> When I execute "EXEC sp_helpfilegroup 'TEXT'"I have no result.
> This is why I have posted in this filegroup. Cause a filegroup without any
> files attached cannot be removed.
> Regards,
> Jeremy
>
>|||You're welcome, Uri.
I'm doing ALTER DATABASE XXX REMOVE FILEGROUP [TEXT] which has worked for
many filegroups before, and I have the following error:
Msg 5042, Level 16, State 8, Line 1
The filegroup 'TEXT' cannot be removed because it is not empty.
Regards,
Jeremy|||I haven't seen this problem before. Can you post the exact steps you
performed the remove the files?
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Jeremy Calles" <JeremyCalles@.discussions.microsoft.com> wrote in message
news:A3D24B42-5CB4-4E27-AD85-15DCB1BDA701@.microsoft.com...
> Hi Dan,
> Thanks for your message, but I have already checked that no files are
> assigned to this filegroup. And of course, there are no files.
> So this is why it seems really strange to me.
> I don't know what could it be.
> Regards,
> Jeremy|||Hi Dan,
I have done the following:
- First extract the name of files attached to [TEXT] filegroup:
SELECT name FROM sys.database_files WHERE data_space_id =FILEGROUP_ID('TEXT')
-For each file, I have done:
DBCC SHRINKFILE(XXXXXXX_01,EMPTYFILE)
ALTER DATABASE XXX REMOVE FILE XXXXXXX_01
-When all files have been remove, I do the following on filegroup:
ALTER DATABASE XXX REMOVE FILEGROUP [Text]
I have done the following many times, and it used to works on all
filegroups, but not on this one.
Regards,
Jeremy|||How did you move existing objects from filegroup? I just want to be sure
I'm not missing something.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Jeremy Calles" <JeremyCalles@.discussions.microsoft.com> wrote in message
news:5E8B5C25-C8B6-4EEC-8A03-8CB2042439CD@.microsoft.com...
> Hi Dan,
> I have done the following:
> - First extract the name of files attached to [TEXT] filegroup:
> SELECT name FROM sys.database_files WHERE data_space_id => FILEGROUP_ID('TEXT')
> -For each file, I have done:
> DBCC SHRINKFILE(XXXXXXX_01,EMPTYFILE)
> ALTER DATABASE XXX REMOVE FILE XXXXXXX_01
> -When all files have been remove, I do the following on filegroup:
> ALTER DATABASE XXX REMOVE FILEGROUP [Text]
> I have done the following many times, and it used to works on all
> filegroups, but not on this one.
> Regards,
> Jeremy|||I have dropped a lot of objects, but the one I need to move, I have use the
syntax below:
CREATE CLUSTERED INDEX [CIdxTemp] ON [dbo].[YYYY]
(
[PK] ASC
)WITH (DROP_EXISTING = ON) ON [OtherFilegroup]
Regards,
Jeremy|||With no files in the filegroup, the error message is obviously erroneous.
Unless someone else jumps in, I suggest you open a support case.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Jeremy Calles" <JeremyCalles@.discussions.microsoft.com> wrote in message
news:EBACBC50-2B8B-46F8-9FE1-E161470A8A75@.microsoft.com...
>I have dropped a lot of objects, but the one I need to move, I have use the
> syntax below:
> CREATE CLUSTERED INDEX [CIdxTemp] ON [dbo].[YYYY]
> (
> [PK] ASC
> )WITH (DROP_EXISTING = ON) ON [OtherFilegroup]
> Regards,
> Jeremy
>