Rich Benner deals with a troublesome partition:
What are stubborn partitions in SQL Server and how do you delete them? This was an interesting issue I recently had to deal with on a client site that I thought our readers might find interesting.
The tables in use here are partitioned. The partition field is based upon a date field and we have a partition per month. There is a monthly maintenance job which creates our new partitions. The job should also delete the oldest partitions. This job has been failing to delete an old partition as the data file contained within is not empty. It’s stubborn!
If we try to remove this file we get the error “The File cannot be removed because it is not empty,” as you can see:
Read on for some diagnosis of the problem, as well as the solution Rich developed.