How to reduce database size in SQL server?
irl last edited by
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.
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
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
BackUpdatabase, 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
BackUpdatabase to proactively prevent it from growing as much.