How to remove a partition completely

Hello,

I'm testing partitions and all works well except removing partitions. My steps for removing the oldest partition of a partitioned table:

a) CREATE TABLE I'm creating a new temporary table with the same structure and indexes as the partitioned one - in the same filegroup as the first partition ==> OK

b) ALTER TABLE I'm switching out the oldest partition to the new table => OK

c) ALTER PARTITION FUNCTION I'm merging the oldest boundary value == OK

I have checked, that all old data rows are "removed" from the partitioned table. Then I drop the temporary table and remove the file from the file group.

Checking Database Properties > Filegroups shows there are 0 files in the file group. When I try to remove the file group I get an error

Quote: Drop failed for FileGroup 'fgtrans000000'. (Microsoft.SqlServer.Smo)
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
The filegroup 'fgtrans000000' cannot be removed because it is not empty. (Microsoft SQL Server, Error: 5042)

Thank you

Eckard

I managed to solve the problem:

When the partition function is defined AS RANGE RIGHT, then merging the oldest partitions means removing the second oldest file group from the partition scheme! Since the oldest file group is reused I could not remove the file group. It's descibed in the documentation, ok.

But that was not what I excepected. I thought the oldest file group would be removed. It's symmetric with the other end: If I split the newest partition the new file group gets inserted before (!) the newest one if I get RANGE RIGHT or LEFT wrong.

IMHO this should not depend on the RANGE RIGHT or LEFT definition.

Eckard

sql