0

Context

We have a Backup DB, where we normally store BackUps of tables from production DB before doing any updates/deletes. If anything goes wrong, we can restore the data from that table created in BackUp DB.

Problem

The size of Backup DB is rapidly increasing and I need a way to reduce its size.

Steps so far

I tried deleting old tables and shrinking BackUP DB but shrinking takes too much of time.

2

2 Answers 2

0

Have you looked into table / index compression to see if your tables can be better compressed in the BackUp database?

You can use the system stored procedure sp_estimate_data_compression_savings to roughly calculate how much space you can save by compressing your tables / indexes.

Also, if you don't need to save older data in your BackUp database, you should delete it when possible (perhaps after you're done making your data changes to the main table and it's verified as ok?). By doing so, you'll free up space that can be re-used in the BackUp database to proactively prevent it from growing as much.

0

Is this a one-time event or does it happen again? I.e., is this "copy" database used over time to store old copies of data? If so, then why would you want to pay dearly to reclaim storage when you end up needing this storage again the next day or week? The reasoning is the same as for why we don't shrink data or log files in general.

Yes, there are of course compression options you can use so this copy don't need as much space. They have already been suggested (clustered columnstore index possibly with archival compression and data compression). Evaluate those options and then just be fine with the disk requirement for your copy-database. :-)

2
  • It is a repeated process as we use this DB to copy table information from production DB.
    – Bishal
    Commented Jan 18, 2022 at 15:44
  • Then just leave it to the size it is, and consider using whatever compression technique you feel comfortable with (clustered columnstore index or data compression). I.e., don't shrink the database files between your repeated runs (except for the initial shrink to a more reasonable size after changing to use a compression technique). Commented Jan 18, 2022 at 16:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.