cannot drop datafile in empty filegroup

Question

The following situation:

Database with 4 filegroups: PRIMARY, DATA, INDEX, INDEX2 (yeah, and LOG)
I migrated all indexes from INDEX to INDEX2

Filegroup INDEX contained 8 datafiles. I was able to delete all those datafiles except for 1 datafile, SQL is saying this is not empty. The question is: How do I determine which object (index) is in this datafile/filegroup?

I already queried sys.indexes to verify nothing is in filegroup INDEX. I also queried sys.allocation_units for this. This gave empty results. Any ideas? Note: SQL 2005EE SP3 CU10, database is mirrored

Answer 1

You might want to check that there are no partition schemes that still reference INDEX, and also that there are no full text indexes that reference it. For reference, here's a full list of system tables that can make a reference to a data space:

Schema_Name    System_View_Name           Column_Name
sys            tables                     filestream_data_space_id
sys            tables                     lob_data_space_id
sys            fulltext_indexes           data_space_id
sys            allocation_units           data_space_id
sys            xml_indexes                data_space_id
sys            indexes                    data_space_id
sys            database_files             data_space_id
sys            fulltext_catalogs          data_space_id
sys            destination_data_spaces    data_space_id
sys            master_files               data_space_id
sys            internal_tables            filestream_data_space_id
sys            internal_tables            lob_data_space_id
sys            spatial_indexes            data_space_id

You may also find it useful to have a look with my space analysis tool.

Edit -> It is also worth running DBCC SHRINKFILE ([file_id], EMPTYFILE) to ensure that the file is emptied prior to dropping it.

Answer 2

0

The actual link may just be a pointer in the cache.

You can try to recycle SQL Server or run FREEPROCCACHE then try to delete that last file.

answered Jul 28 '10 at 05:23 AM

Blackhawk-17
10.5k ● 23 ● 29 ● 34

I see you got voted down too. I thought it was a good shout!

Jul 28 '10 at 07:42 AM Matt Whitfield ♦♦

@Matt - I believe this OP marks them down if it doesn't work in their particular case. Not exactly the spirit of the thing I would have thought, but it is their prerogative. The suggestions have worked for me in similar scenarios - just tried to pass 'em along.

Jul 28 '10 at 07:50 AM Blackhawk-17

@Blackhawk-17 @Matt Whitfield What a day, both of you got voted down by @Apeman which makes me think that he was probably trying to vote you up but missed the right button :( I voted you up to "rollback" that tran.

freeproccache is a very good idea, voting down certainly does not fit. As a matter of fact, all of the below could be tried (for sanity check):

dbcc dropcleanbuffers with no_infomsgs;
go
dbcc freeproccache with no_infomsgs;
go
dbcc freesystemcache ('All') with no_infomsgs;
go

Jul 28 '10 at 07:53 AM Oleg

@Oleg - this is another one sir - I would vote for it if it were an answer!

Jul 28 '10 at 08:01 AM Matt Whitfield ♦♦

@Blackhawk-17 - I am wondering if maybe the hover over text is wrong - because it says 'I do not like' or 'I like' rather than 'this is helpful' / 'this is not helpful'.

However, looking at the stats for the guy, 1 up vote and 3 down votes doesn't exactly seem right, does it?

Jul 28 '10 at 08:02 AM Matt Whitfield ♦♦

(comments are locked)

Answer 3

0

Thanks for your answer, Unfortunately, sys.partition_schemes als returns no data.

answered Jul 28 '10 at 04:58 AM

Apeman
311 ● 11 ● 12 ● 15

So what about full text indexes?

Jul 28 '10 at 05:02 AM Matt Whitfield ♦♦

(comments are locked)

Answer 4

0

also empty

answered Jul 28 '10 at 05:17 AM

Apeman
311 ● 11 ● 12 ● 15

Well thanks for voting me down, was only trying to help. Will stop now.

Jul 28 '10 at 05:19 AM Matt Whitfield ♦♦

Sorry, it was not my intention to vote you down, corrected that

Jul 28 '10 at 05:26 AM Apeman

Oh, ok! Did you find what it was?

Jul 28 '10 at 05:27 AM Matt Whitfield ♦♦

No, to resume: - no data sys.indexes, sys.partition_schemas and sys.fulltext_indexes - the FREEPROCCACHE trick didn't work either (already tried to delete this file after a SQL restart)

Maybe mirroring is causing this issue?

Jul 28 '10 at 05:30 AM Apeman

It may be, but I'm not sure why it would, if everything was removed from the filegroup. Try running this query (basically testing all above tables) on both sides of the mirror, and see what you get back...

DECLARE @i int
SELECT @i = data_space_id FROM sys.data_spaces WHERE name = 'INDEX'
SELECT * FROM sys.tables WHERE filestream_data_space_id = @i
SELECT * FROM sys.tables WHERE lob_data_space_id = @i
SELECT * FROM sys.fulltext_indexes WHERE data_space_id = @i
SELECT * FROM sys.allocation_units WHERE data_space_id = @i
SELECT * FROM sys.xml_indexes WHERE data_space_id = @i
SELECT * FROM sys.indexes WHERE data_space_id = @i
SELECT * FROM sys.database_files WHERE data_space_id = @i
SELECT * FROM sys.fulltext_catalogs WHERE data_space_id = @i
SELECT * FROM sys.destination_data_spaces WHERE data_space_id = @i
SELECT * FROM sys.master_files WHERE data_space_id = @i
SELECT * FROM sys.internal_tables WHERE filestream_data_space_id = @i
SELECT * FROM sys.internal_tables WHERE lob_data_space_id = @i
SELECT * FROM sys.spatial_indexes WHERE data_space_id = @i

Jul 28 '10 at 05:36 AM Matt Whitfield ♦♦

(comments are locked)

cannot drop datafile in empty filegroup

Follow this question

Related Questions