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
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
Thanks for your answer, Unfortunately, sys.partition_schemes als returns no data.
also empty
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
Well thanks for voting me down, was only trying to help. Will stop now.
Well, that is very strange then. Sorry I wasn't paying enough attention and ran my query on my 2K8 installation... What does `DBCC SHRINKFILE ([file_id], EMPTYFILE)` produce?
No, it wouldn't have already been empty - but there must have been some ghost pages in there. I've edited my answer to include that info for anyone coming to this question in the future.
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.
@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
I see you got voted down too. I thought it was a good shout!
@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.
@Oleg - this is another one sir - I would vote for it if it were an answer!
@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?
