asked
How to reduce data in huge SQL database
Dear all, I have a small problem with data in SQL database, for now, my backup increased in 195 GB, and for some days will be much higher, please advice what should I do to reduce or to decrease size on database. Thank you in advanced for your ideas.
What exactly is your problem? Can you identify which of the scenarios discussed below is the one that's causing you problems? Then we can point you in the right direction.
Your backup is (roughly) the same size as your actual data, being a page-by-page copy of the database. If you have the right version / edition of SQL Server, you can use native [Compressed backups][1] to reduce the size of your backup. Alternatively, there are third party tools out there that will also do backup compression, for example [Red Gate's SQL Backup][2], [Quest/Dell/NetVault's Litespeed for SQL Server][3] (more expensive), and [Idera's SQL Safe Backup][4]. I'm sure that there are others out there. Generally, the performance of these products is within a few % of each other, and the decision usually comes down to either price, or relationship with suppliers. Check that you are also remembering to perform transaction log backups, as this is something that is overlooked more often than not, and has the potential to really chew up hard disk space. [1]:
http://msdn.microsoft.com/en-us/library/bb964719(v=sql.100).aspx [2]:
http://www.red-gate.com/products/dba/sql-backup/ [3]:
http://www.quest.com/litespeed-for-sql-server/ [4]:
http://www.idera.com/productssolutions/sqlserver/sqlsafebackup
I have personally used Red Gate's SQL Backup and I have always been extremely happy with it.
Alternative answer, in case you really are asking about reducing the data in your database. You'll need to work with your business to identify data that they no longer need. However, this can expose you to all sorts of legal / auditing / data retention requirement issues, as well as potential for deleting something that you find you later need... Not to say it can't be done, but get the management to sign off on it all the way up to the top - after all, you'll be the one getting blamed if someone can't retrieve their report, or SOX or the Financial Services Authority come knocking!
Adding one other thing to Thomas's answers you may want to consider/look into. You could use database compression (row or page level) to reduce the size of the mdf file. There are some draw backs to using though so you will need to determine if positives out weigh the negatives.
This is a good point, and one that I had considered adding, but got distracted by Real Life.
There are already great answers, but let me point out that you can split the data into multiple databases which perhaps live on different servers. This probably would not work well if you frequently execute queries that need access to the whole data set. But if much of your data is historical and rarely queried you could probably break that out into a separate. You could consider making that archive database read only and you could consider moving it onto older harder with a lower performance and thuse a lower price.
16 People are following this question.
