DB size - Full & Differential backups
-
Hi
I have 2 issues. I work on SQL 2005.
1) The size of my database increased from around 315mb to 640mb in a day. there is NO huge updation of data or anything like that happening in the database. we just create/modify procedures and functions. database was created from the backup of a sql 2000 database (compatability level 80). I found that the size of the Log file increased dramatically.
2) I have scheduled a full backup once a week and a differential backup every day.The size of the differential backup which is generally 15-16 MB increased to 85mb. I used DBCC ShrinkFile to reduce the size of the log file but the size of the differential backup is still around 25mb (should have been less than 10mb coz the full backup was taken today).
hope u people out there have some pointers.....
Thanks In Advance
"Keep Trying"
-
Some suggestions:
If you have a backup from before this increased growth, you could load it, compare it with your current db and try to figure what has increased. You say no big update or such was happening, but something must have happened...
Have you created indices? An index can take up huge amounts of diskspace.
If you talk about databasesize, is this only the MDF file? Or does it include the LDF(transactionlog) as well? Can it be someone has changed the recovery model? If you have recovery on SIMPLE, you could truncate the log.
If the recovery is FULL, realize that procedures and functions can easily create rapid increase of your T-log when a large batch is executed. If you do not backup your log(at night?), then it will not be truncated and keep growing.
It is not much, hope you it helps something.
Greetz,
Hans Brouwer -
You should also be aware that Full and Differential backups do not truncate the log file if your database is in bulk-logged or full recovery mode. If this is the case, you need to be sure you are also taking transaction log backups between your full and differential backups.
-
Agree with Lynn.
Can't you schedule log backup as a job?
-
Hi Hans
Thank You for your suggestions.
Have you created indices? An index can take up huge amounts of diskspace - There are indexes in the database but i have not created indexes on or before the day the size of the database increased.
If you talk about databasesize, is this only the MDF file? Or does it include the LDF(transaction log) as well? Can it be someone has changed the recovery model? If you have recovery on SIMPLE, you could truncate the log. - The size of the log file increased. The .MDF file did not show any dramatic increase in size.
If the recovery is FULL, realize that procedures and functions can easily create rapid increase of your T-log when a large batch is executed. If you do not backup your log(at night?), then it will not be truncated and keep growing. - Yes the recovery model is full and we are creating and modifying procedures. I dont think a large batch could have been executed but again i cant say am 100% sure.
I truncated the LOG file in the morning and the differential backup was taken in the evening. But the size of the diff backup was on the higher side but not as high as it was when the database size increased. (Do u get this)
Thanks Once again
"Keep Trying"
-
Thanks For ur suggestions Lynn..
db is in full recover mode.
"Keep Trying"
-
Thanks for ur points ... my backup is scheduled as a job.
"Keep Trying"
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply