May 23, 2012 at 10:29 am
Following is the snapshot of differnential backups in one of our production servers. As you can see size of diff backup has gone to 100 MB and again it reached to 50 on 2 AM timestamp. As per the logic the size should not decrease unless there is a full backup. In this case we have only weekly full backup and can see same size difffernce daily. If any of you have seen similar issues or if you know the reason, please update me. Database is hosted on SQL2005 instance with SP1.
05/21/2012 08:00 PM 50,417,152 Tracker_Data_backup_201205212000.Dif
05/21/2012 10:00 PM 50,417,152 Tracker_Data_backup_201205212200.Dif
05/22/2012 12:00 AM 100,832,768 Tracker_Data_backup_201205220000.Dif
05/22/2012 02:00 AM 50,417,152 Tracker_Data_backup_201205220200.Dif
05/22/2012 04:00 AM 50,417,152 Tracker_Data_backup_201205220400.Dif
05/22/2012 06:00 AM 50,417,152 Tracker_Data_backup_201205220600.Dif
05/22/2012 08:00 AM 50,417,152 Tracker_Data_backup_201205220800.Dif
May 23, 2012 at 10:40 am
Gopi Krishnan-468302 (5/23/2012)
Following is the snapshot of differnential backups in one of our production servers. As you can see size of diff backup has gone to 100 MB and again it reached to 50 on 2 AM timestamp. As per the logic the size should not decrease unless there is a full backup. In this case we have only weekly full backup and can see same size difffernce daily. If any of you have seen similar issues or if you know the reason, please update me. Database is hosted on SQL2005 instance with SP1.05/21/2012 08:00 PM 50,417,152 Tracker_Data_backup_201205212000.Dif
05/21/2012 10:00 PM 50,417,152 Tracker_Data_backup_201205212200.Dif
05/22/2012 12:00 AM 100,832,768 Tracker_Data_backup_201205220000.Dif
05/22/2012 02:00 AM 50,417,152 Tracker_Data_backup_201205220200.Dif
05/22/2012 04:00 AM 50,417,152 Tracker_Data_backup_201205220400.Dif
05/22/2012 06:00 AM 50,417,152 Tracker_Data_backup_201205220600.Dif
05/22/2012 08:00 AM 50,417,152 Tracker_Data_backup_201205220800.Dif
Based on the above, no idea. We can't see from here what you see there. It appears that you aren't appending backups, good. What else was running on the system at the time of the 12:00 AM differential backup? Remember, the differential backup will also backup as much of the transaction log as necessary to ensure a consistent database state when restored. If there was a large open transaction at the time, this could explain the additional data backed up.
May 23, 2012 at 11:22 am
Lynn has a good guess. Overall, I wouldn't worry about this too much. A backup size difference should clue you in to something happening, but the size in and of itself doesn't tell you what happened.
May 23, 2012 at 12:58 pm
Based on the fact that it's literally twice the size of the others, I would wonder if you have two backups there instead of one? Check to see if the backup set is duplicated for some reason (usually, two backups getting fired off)
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply