Very large and slow differential backup
-
Posted - 10/26/2006 : 01:13:09 I am using SQL SERVER 2005 SP1 and I have a production database (12GB).
My database has a huge table taking about 65% of its size. A lot of insert- statements are executed in this table each day. I developed two SQL SERVER Integration packages, the first backups my database on Saturday - 12 AM (full backup) and the second backups my database every night-12 AM (Differential backup) except Saturday. Both SSIS packages store the backup file to a USB-attached hard disk drive.
The full backup lasts about 3 hours (although the there is almost no traffic to the database, no command execution) and the file has a size of about 11 GB.
The problem is that the differential backup lasts about 2 hours and 30 minutes and the size of the produced file is 9GB !!!!
What causes the differential backup to be so large?
The large table of my db(65% of db size) grows every day but not all of its pages (this table usually gets a lot of insert and select statements).
In this case, the differential backup bakcups only the pages of the table that changed or all the pages of the table?
Do you believe that using a USB-hard disk drive (instead of saving the backup to local hard disk drive 
is the main reason for the backup duration? What may be the reasons that the backups last almost 3 hours since there is almost no traffic to the server and to the database?PS: I have removed the Check database Integrity of the backup tasks so there is no latency because of this option
-
To answer the second question first, I'm betting the USB connector is the issue for the slowness of the backup. USB isn't quite up to speed with other disk drive connectors. It works for small files, but even then, if you do a timing test between a local drive and a USB drive, you'll notice a small file still takes longer on USB than it does on IDE/SCSI. I have to wonder if USB is a serial like device or a parallel device. It's "suppposed" to be parallel...
As far as your first question goes, a differential backup doesn't just backup the inserts on your large table. It's backing up everything on the database that changes. Inserts, Deletes, and an insert & delete for every update. If you have a lot of activity on your DB, it's no wonder your differential is so huge.
At my workplace, we stick with hourly Transaction Log backups and daily Full backups. Our DB is approximately 21 GB with a lot of activity during the day, so this works best for us instead of attempting a differential. And we have tried differential. It just doesn't work effectively on DBs with a lot of activity (as far as we're concerned).
Hope this helps.
-
i had a similar issue on some very large archive databases we have a few months back and we use Veritas which uses the SQL API's anyway. Sun, our support contact couldn't help.
I did some research and the best answer I came up with is that updates are affecting most pages and extents which means the entire page or extent needs to be backed up in a diff backup.
-
There is another option. Make some of your filegroups READ ONLY, then backup only the filegroups that are READ/WRITE. @=) (see Partial Backups in BOL)
Or do put your large table on its own filegroup and backup that filegroup separately from everything else.
Posted - 10/26/2006 : 01:13:09 
is the main reason for the backup duration? What may be the reasons that the backups last almost 3 hours since there is almost no traffic to the server and to the database?Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply