Differential backup way to big
-
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.
-
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
Greg
-
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
-
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
-
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
Greg
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply