Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Soumen Dey
Starting Member
20 Posts |
 Posted - 2004-10-11 : 08:38:05
|
| Hi,I have a huge database in SQL server, it keeps on growing every day. I regularly take backup , but the .bak file too is growing everyday, now it is close to Gb . Can any one suggest how should I optimize my database so thet the .bak file size reduces without any loss of data |
|
|
tuenty
Constraint Violating Yak Guru
278 Posts |
Posted - 2004-10-11 : 09:28:59
|
| look for DBCC SHRINKDATABASE in BOL and add this procedure to your DB maintanance plan*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*A candle loses nothing by lighting another candle |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2004-10-12 : 03:55:33
|
| Does DBCC SHRINKDATABASE make much difference to the backup size? I didn't think that the backup included "dead space" - but I'm happy to be enlightened!You could use horizontally-partitioned tables - e.g. the January data is in one table, and the February data another, and so on. You then make a VIEW onto these tables (in a somewhat special way so that SQL will optimise the queries and put INSERTs into the right table), and then you can choose to only backup the tables of months with "changing" data.There are also 3rd party backup utilities which will compress as they output (but I don't think they are cheap - if we are only talking about ONE gb then probably much cheaper to buy a bigger hard disk and tapedrive!) or you could perhaps hook up a WinZip type operation to the backup process so that the BAK file was Zipped after creation - but you'd still need enough disk space to store the whole BAK file, just the Backup (e.g. to tape) from there would be smallerKristen |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
|
|
|
|
|
How to handle growing database