x

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 '10 at 02:11 AM in Default

Apeman gravatar image

Apeman
311 12 13 15

(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][1].

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

[1]: http://www.atlantis-interactive.co.uk/products/dataspaceanalyser/default.aspx
more ▼

answered Jul 28 '10 at 02:35 AM

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.4k 61 65 87

(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 '10 at 05:23 AM

Blackhawk-17 gravatar image

Blackhawk-17
11.8k 28 30 35

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)
10|1200 characters needed characters left
Thanks for your answer, Unfortunately, sys.partition_schemes als returns no data.
more ▼

answered Jul 28 '10 at 04:58 AM

Apeman gravatar image

Apeman
311 12 13 15

So what about full text indexes?
Jul 28 '10 at 05:02 AM Matt Whitfield ♦♦
(comments are locked)
10|1200 characters needed characters left
also empty
more ▼

answered Jul 28 '10 at 05:17 AM

Apeman gravatar image

Apeman
311 12 13 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)
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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x124
x35
x10
x9

asked: Jul 28 '10 at 02:11 AM

Seen: 2390 times

Last Updated: Jul 28 '10 at 02:11 AM