Differential database file size

Question

Differential database file size

raghu_nandan1

SSC Eights!

Points: 966
More actions
March 20, 2003 at 7:20 am

#159638

I have a full back up of a database on every Sunday and the backup file size is about 10 gigs. Then I have every weekday differential backup of the same database but by Thursday Differential Backup file size has grown to 40 gigs (even though database size is roughly 10 gigs). could some one please help me trim the differential file size. I could recover the db on a different server with no problem but I am worried about the file size.
Thanks
Raghu

Raghu

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

SQLBill SSC Guru Points: 51440 More actions · Answer 1

This could be an easy one. A differential backup is a backup of ALL of the changes since the last FULL backup. So, your Monday differential (diff) backup is of all the changes since Sunday. Your Tuesday diff backup is all of the changes since Sunday, your Wednesday diff backup is all of the changes since Sunday......etc... Getting it? By Thursday, there have been a LOT of changes/updates/inserts/deletes etc. to the database so all of those changes equal 40 GB of data.

Try doing a Full backup each night and a differential every four hours.

Also, are you doing Transaction Log backups?

-SQLBill

Rodrigo Acosta SSC-Insane Points: 21261 More actions · Answer 2

Could by but the differential backup doesn't log the changes of data in transactions, it only saves changed data pages. When a full backup is made, there is one page in the file that has one bit for every page of data in the database. So the full backup, turns all the bit to 0, after changes are applied to the db, the pages (or extents, i don't remember) that contain the modified data

are set to 1. So every differential backup, backups the data pages with the bit equal to 1. If you modified always the same rows of the table, the differential backup should be with the same size.

raghu_nandan1 SSC Eights! Points: 966 More actions · Answer 3

Thanks for the replies...can I do something about it like compress the file (within Enterprise Mgr) also I am backing up my transaction logs every hour...

I cannot backup during working hours as machines load data into the database and the way our third party software works (not labview) is that each machine has it's own dedicated load-table and it locks the table during the run.

Raghu