Shrink Large replicated database using partitioning

• berki

  Mr or Mrs. 500

  Points: 510

  More actions

  March 27, 2016 at 12:10 pm

  #399925

  Hi,

  For transactional replication we restored our production DB which is over 5Tb to the reporting server and started replication. It runs well, but a huge part of the database, about 4.9Tb are BLOB data that we'd like to remove as we don't report on it.

  We've tried to delete and shrink the DB, but it takes months and no space is reclaimed. We've tried doing it in batches and with TruncateOnly.

  We've read that we could use partitioning to move all the reporting tables to a new partition, all 250+ tables about 300Gb, we want to retain some BLOB data from the table but not all. Can we partition the table with BLOB data then drop the partition with the remaining BLOB data. If so are there any guides on how to do this?

  Would it be better to copy all the data we want to retain to a new database, pause replication, detach the database and attach the new database with the same name?

  Thanks

• WorkasDBA

  SSCommitted

  Points: 1684

  More actions

  March 29, 2016 at 10:33 am

  #1868321

  For the clear understanding, you are replicating from a database with BLOB data to a reporting database that may not require BLOB data.Is that true?.