question

Apeman avatar image
Apeman asked

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
administrationmaintenancedropdatafiles
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Matt Whitfield avatar image
Matt Whitfield answered
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
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Apeman avatar image
Apeman answered
Thanks for your answer, Unfortunately, sys.partition_schemes als returns no data.
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

So what about full text indexes?
0 Likes 0 ·
Apeman avatar image
Apeman answered
also empty
9 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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
1 Like 1 ·
Well thanks for voting me down, was only trying to help. Will stop now.
0 Likes 0 ·
Sorry, it was not my intention to vote you down, corrected that
0 Likes 0 ·
Oh, ok! Did you find what it was?
0 Likes 0 ·
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?
0 Likes 0 ·
Show more comments
Blackhawk-17 avatar image
Blackhawk-17 answered
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.
9 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@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
2 Likes 2 ·
I see you got voted down too. I thought it was a good shout!
0 Likes 0 ·
@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.
0 Likes 0 ·
@Oleg - this is another one sir - I would vote for it if it were an answer!
0 Likes 0 ·
@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?
0 Likes 0 ·
Show more comments

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.