3

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:

  1. Find out oldest partition to delete
  2. Drop Archive table if exists
  3. Get date to delete partition (Using table --- sys.partition_range_values Where boundary_id = 1)
  4. Create Archive tables
  5. Switch partition 2 to Archive tables
  6. Drop Archive tables
  7. Merge partitions
  8. Remove file and filegroup
  • Do you have any database snapshots for this database? What does SELECT * FROM sys.databases WHERE source_database_id IS NOT NULL return? – Scott Hodgin Jul 3 at 18:40
  • It returns 0 rows. – SqlDBA Jul 3 at 18:49
  • Update:- It's resolved. Job did purge the data from the oldest partition however it failed on last step of SSIS package where it removes file and filegroup so I deleted file and filegroup by executing T-SQL of last step of the package. – SqlDBA Jul 9 at 18:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.