How to reduce database size in SQL server?



  • I have a scenario that there is a Backup DB where we normally store BackUps of tables from production DB before doing any updates/deletes so that if anything goes wrong, we can restore the data from that table created in BackUp DB. But, the size of Backup DB is rapidly increasing and I need a way to reduce it's size. I tried deleting old tables and shrinking BackUP DB but shrinking takes too much of time.

    Please suggest.

    Thank you!



  • Have you looked into https://docs.microsoft.com/en-us/sql/relational-databases/data-compression/enable-compression-on-a-table-or-index?view=sql-server-ver15 to see if your tables can be better compressed in the BackUp database?

    You can use the system stored procedure https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-estimate-data-compression-savings-transact-sql?view=sql-server-ver15 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.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2