cannot drop datafile in empty filegroup

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

more ▼

asked Jul 28, 2010 at 02:11 AM in Default

avatar image

311 13 15 19

(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

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.

more ▼

answered Jul 28, 2010 at 02:35 AM

avatar image

Matt Whitfield ♦♦
29.5k 62 66 88

(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Jul 28, 2010 at 05:23 AM

avatar image

12k 30 35 42

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

Jul 28, 2010 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, 2010 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;
dbcc freeproccache with no_infomsgs;
dbcc freesystemcache ('All') with no_infomsgs;
Jul 28, 2010 at 07:53 AM Oleg

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

Jul 28, 2010 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, 2010 at 08:02 AM Matt Whitfield ♦♦
(comments are locked)
10|1200 characters needed characters left

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

more ▼

answered Jul 28, 2010 at 04:58 AM

avatar image

311 13 15 19

So what about full text indexes?

Jul 28, 2010 at 05:02 AM Matt Whitfield ♦♦
(comments are locked)
10|1200 characters needed characters left

also empty

more ▼

answered Jul 28, 2010 at 05:17 AM

avatar image

311 13 15 19

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

Jul 28, 2010 at 05:19 AM Matt Whitfield ♦♦

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

Jul 28, 2010 at 05:26 AM Apeman

Oh, ok! Did you find what it was?

Jul 28, 2010 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, 2010 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, 2010 at 05:36 AM Matt Whitfield ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Jul 28, 2010 at 02:11 AM

Seen: 3180 times

Last Updated: Jul 28, 2010 at 02:11 AM

Copyright 2016 Redgate Software. Privacy Policy