size of differential backups to big

[johnhugh]

Hello,

I'm working on SQL Server Standard 2005.
My database is 4.5GB. I do a full backup which comes to a backup file of 2GB. If I do a differential backup immediately after my full backup my differetnial backup file is still 1.5GB.
I would expect the differential backup file to only be a couple of MB since nothing has changed.
What can I do to reduce the size of my differential backup file?
Recovery model is set to full.

 

[SQLBill]

First, why are you doing a differential right after you did a full backup? It's not necessary.

Second, did anything run on the database in between the two backups? A huge update? Maintenance job (such as reindexing)? You could have uncommitted transactions left after your full backup which are then 'picked up' by the differential.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[johnhugh]

Hi,

I ran the diff backup right after a full backup to show that the size of the diff backup does not seem to depend on when the full backup was run.

Nothing ran on the database in between the 2 backups.

 

[johnhugh]

Anyone else?
Really appreciate your help.

 

[ramam1]

What flags/options are you using on the backup command? Diff command?

 

[johnhugh]

Hi Ramam,

I configure my backups in the management studio.

The scrip it give sme is this for my full backups:

BACKUP DATABASE [sfbdat] TO  DISK = N'C:\temp\Backup\full.bak' WITH NOFORMAT, INIT, SKIP, NOREWIND, NOUNLOAD,  STATS = 10

And my differential backups are done like this:

BACKUP DATABASE [sfbdat] TO  DISK = N'C:\temp\Backup\diff.bak' WITH  DIFFERENTIAL , NOFORMAT, INIT, SKIP, NOREWIND, NOUNLOAD,  STATS = 10