Trouble with differential backups

joshbond

I am using SQL Backup 6.2.0.134. I create a FULL backup of my 164GB database every Friday night. Sa-Th I create a DIFFERENTIAL backup. My problem is that the first DIFFERENTIAL backup (Saturday) is 40GB. The subsequent one (Sunday) is 41GB, then 42GB and so on.

This would seem to indicate that somewhere I am making 40GB worth of changes between Friday's FULL and Saturday's DIFFERENTIAL but my next FULL will have only increased by 6-7GB (what I expect.)

I have searched and searched and I cannot find any jobs that run only between Friday & Saturday that would cause something like this. My reindex job runs and completes before Friday's FULL backup.

The only thing I have left is to try to examine the DIFF file backup and see what changes are listed (does anyone make a tool for this?) or to cross my fingers and hope that this is a known bug with my version of SQL Backup and only happens on databases larger than 150GB (quite a bit of a longshot.)

Can it be possible that I have to run a transaction log backup after my reindex job and before the FULL backup? I thought that DIFFERENTIAL backups only rely on extent changes within the database and don't read anything from the LOG. Can it be possible that unflushed transactions are causing the database to retain those extent modifications?[/b]

ChrisAVWood

Hi,

I am using 6.3.0.48 on a 800Gb SQL2000 DB and only get small differentials unless I have re-indexed some of the tables. I don't remember the difference between 6.2 and 6.3 or 6.4 that would have fixed your problem.

You didn't mention what level of SQL you were using and if its SQL2008 is the database compressed?

Chris

joshbond

I am using SQL Server 2005. The database is not compressed.

Do you perform a transaction log backup after your reindex job and before the FULL backup?

ChrisAVWood

No this DB is in the Simple Recovery Model. It's Microsoft MOM reporting respository DB.

What build are you running of SQL2005? It has been updated many times with CU's.

Chris

joshbond

It's the newest Service Pack or close to it. 9.0.4.053

I really think it might have to do with the transaction log. I am going to try to run a transaction log backup after the reindex and see if that makes a difference.

joshbond

The transaction log backup made no difference. I disabled my reindex job. So the order of events was:

Transaction log backup @ 10:45PM Full backup @ 11:00PM ~ 50GB compressed Transaction log backups every 15 minutes for 24 hrs ~ 1.8GB compressed in total Diff backup @ 11:00PM ~ 18GB compressed

It is acting like I should always have a minimum of 17-18GB (compressed) in my differential backup. Something is really, really wrong and I cannot figure it out.

petey

Could you try running a differential backup immediately after a full backup, and check the size of the resulting differential backup?

Thanks.

joshbond

I performed a DIFF backup right after my FULL and BAM! 18.0GB. Is there some way to examine a DIFF file and see what is in there?

This is craziness!

petey

That is indeed strange.

I came across the following in the SQL Server docs:

To make differential backup, restore, and recovery of full-text catalogs possible, the full-text catalog must be stored in a directory that is part of an NTFS file system. Differential backup, restore, and recovery are not supported on file systems that are not NTFS.

Are you by any chance using full text catalogs, and are they on NTFS drives?

Thanks.

petey

I'm not aware of any tools that can read differential backup files and tell you its contents. There is this article, that tells you how you can determine the number of modified extents in a database.