We have a SQL Server Agent job that executes package to purge partition file and filegroup older than 25 month. The job was running fine however it failed this month with following error:
The operation cannot be performed on a database with database snapshots or active DBCC replicas
I checked the SQL Server logs and there weren't any DBCC process running during time. I cannot figure out why it is throwing out database snapshot/active DBCC replicas error.
I'm using SQL Server 2012 - clustered instance.
SSIS Package steps:
- Find out oldest partition to delete
- Drop Archive table if exists
- Get date to delete partition (Using table --- sys.partition_range_values Where boundary_id = 1)
- Create Archive tables
- Switch partition 2 to Archive tables
- Drop Archive tables
- Merge partitions
- Remove file and filegroup