Differential backup way to big

Question

Differential backup way to big

mgraulich

Ten Centuries

Points: 1294
More actions
October 4, 2006 at 11:57 am

#61554

SQL 2000/Windows 2003
I have a full backup I take on Sunday afternoons at 12PM... About 70GBs total. Takes 4 hours. Monday morning I have a diff backup run and the size of that is 13GBs. As the week goes on the diff backup becomes almost half the size of the original full backup. Any optimization job that is run (dbcc reindex etc) is run prior to the full backup so MS shouldn't consider that after the full backup. These differentials are init so there is no history. This db is being log shipped. No replication. I am lost here.

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

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

Greg Charles SSC-Forever Points: 45403 More actions · Answer 1

I would expect the differential backup size to grow as changes are made in the database. You don't mention the amount of data changing activity that occurs in the database throughout the week, but the more changes made since the last full backup, the bigger the differential backup.

If you're concerned about the size of the differentials, you could consider taking full backups more often.

Greg

mgraulich Ten Centuries Points: 1294 More actions · Answer 2

We are certainly not a 24/7 shop. No one is on the system from the sunday backup to monday morning. No way I would expect 13 GBs of changed data. We are union shop. 9-5 M-F

SQLBill SSC Guru Points: 51440 More actions · Answer 3

Remember, Differential backups are not the same as TransLog backups. Each differential backup is a backup of ALL the changes since the last full backup.

So, Tuesday's diff is all the changes since Sunday @ 12PM. Wednesday's diff is all the changes since Sunday @12PM, etc.

Changes include all updates, inserts, and deletes. So that can add up to quite a bit.

-SQLBill

Greg Charles SSC-Forever Points: 45403 More actions · Answer 4

You could run a trace from Sunday afternoon to Monday morning and see what is running on the server during that time. Maybe there's something that you don't know about yet.

Greg