July 6, 2007 at 3:37 am
Hi Friendz,
i have sheduled auto backup to after every 30 minutes..this task works perfectly...but after few dayz i seen my database size increased very big abt 5 Gb. it was actualy 1.70 GB. this is unbelievable..my database growth set 10% of size.. so now i disable the autoback Up. please help me to solve this problem.
thankz,
Dilip virani
July 6, 2007 at 4:01 am
hi
can u give more details regarding the
recovery model, type of backup and how much data insertion/modification goes on in your db.
are you taking log backups? have you done any bulk insertion .
"Keep Trying"
July 19, 2007 at 3:10 am
do you append the backup with the previous backup.
July 19, 2007 at 6:16 am
Check the size of the log file.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
July 23, 2007 at 10:53 pm
Hi...actually every time it growth by 10% when auto backUp runs..so this problem occured..even my log file is not much size n i selected option "Overwrite"..so please any 1 have solution, plz post it..
July 24, 2007 at 7:27 am
SO do you mean to say that you are appending data to the same backup file daily. if so then the size might increase as you find.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
July 25, 2007 at 1:00 am
Two different solution.
1) Create backup file timestamped in that way it will create new file everytime instead of appending all of them to one file.
2) Always truncate log using backup log, this will reduce the file size when backed up.
Pinal Dave
sqlauthority.com
July 25, 2007 at 5:39 am
What tool are you using to do an Autobackup? A job scripted out in T-SQL or the DB Maintenance Plan backup stuff? Or a third party tool like Litespeed?
This makes a difference. For instance, the DBMP should actually create a brand new file with a year/month/day/time appended at the end of each file, so the only "growth" that should be happening on the backup file is the actual growth that's coming from the original database.
But if you're using T-SQL, you might indeed be appending to the backup file instead of dropping the old backup device and creating a new one or overwriting the old one.
And is your problem that your database size is increasing exponentially or that the backup file is increasing exponentially? I don't quite understand from the way you've worded your original question.
July 25, 2007 at 11:03 pm
Hi...
i m always using Enterpise manager for back Up. i select Option for Overwrite always....
there is one more option shedule backUp...from where i sheduled autobackUp....
July 26, 2007 at 4:21 am
Virani,
Rather than opening up a backup window from right-clicking the database and choosing "Backup", you should either use the Database maintenance Plan to create and schedule automatic backups or create a job using a T-SQL query. Either one would be much better than what you're doing now and would probably avoid the backup file growth issue you've been running into.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply