I was working on a table partition management task yesterday. The table is partitioned on a datetime column, and each partition has one and only one dedicated filegroup, and each filegroup is dedicated to one and only one partition and contains only one file in the filegroup.

After I cleaned out the records in the left most partition and then merge it with the next right partition, the filegroup originally dedicated to the left-most partition can be dropped. Of course, I have to remove the file inside the filegroup first before I can drop the filegroup. However, when I tried to remove the file by running

-- the file_data_20090801 is the only file in the filegroup fg_data_20090801, which was used by the left-most partition
ALTER DATABASE MyDatabase REMOVE FILE  file_data_20090801 

I get the following error:

Msg 5042, Level 16, State 1, Line 1
The file 'file_data_20090801' cannot be removed because it is not empty.

I am pretty sure there is no record in that file and nothing on the filegroup either, but to make sure I am correct, I run the following script to check

USE MyDatabase;

SELECT * FROM sys.allocation_units a
INNER JOIN sys.filegroups fg
ON    fg.data_space_id = a.data_space_id
AND  fg.name = 'fg_data_20090801';

I did not get anything back, so this confirms that there is nothing inside the filegrouup / file.

The interesting thing is that this same table exists in different dbs on different servers, however, the error 5042 only occurs on a few servers. I was totally lost, so I resort to google to try to figure out whether there was anyone encountered the same error when doing the partition management. But I did not find anything direct. I thought to myself, OK, if I cannot drop the file, I at least should make it smaller to save the disk space, so I run

DBCC SHRINKFILE ( file_data_20090801, 10) ; -- original size is around 2 GB

After this, I thought "let me try to drop the file again", so run

ALTER DATABASE MyDatabase REMOVE FILE  file_data_20090801

Surprise, this time, the file was dropped.

With this exercise, I feel that SQL Server may have some bug in its table metadata management though I cannot tell exactly what it is. This table (with two nvarchar(max) columns), originally is notorius for not being able to release its space after records are deleted, we usually keep only 3 days data in this table, and any data more than 3 days old is deleted by a nightly job using a simple sql statement

Delete MyTable where DateTimeStamp_column < dateadd(day, -3, getdate())

We log the table size every day, and never see the table size come down, even when rows dropped by more than half sometime, as a result, the table can grow to 270+ GB, and we have to do a special maintenance work twice every year, i.e. truncate table. (Truncate action always works in that it releases space occupied by the table)

Because of this table's characteristics, we decided to partition the table, so we can drop old data physically (by removing the file), however, the initial error 5042 really made me nervous and wonder whether our partition strategy is the answer to the problem. Even with a work-around solution found, I still feel there must be something MS needs to review on the table metadata managment part in the database engine.