January 29, 2010 at 8:55 am
I am trying to understand why my transaction log backup created just after a full database backup is so large. I do a full backup at 12:31AM, Differential backups every 8 hours and transaction logs every two hours. The cycle repeats itself every day and the results shown below occur every day. All the other transaction logs are consistent in size. Below is a file listing showing the question.
01/27/2010 10:15 PM 1,433,088 CycleTime_backup_201001272215.trn
01/28/2010 12:00 AM 19,392,000 CycleTime_backup_201001280000.dif <-- Diff Backup --
01/28/2010 12:15 AM 1,433,088 CycleTime_backup_201001280015.trn
01/28/2010 12:31 AM 124,249,600 CycleTime_backup_201001280030.bak <-- Full BAckup
01/28/2010 02:15 AM 34,797,056 CycleTime_backup_201001280215.trn <-- Why is this so big?
01/28/2010 04:15 AM 1,629,696 CycleTime_backup_201001280415.trn <-- Transaction log --
01/28/2010 06:15 AM 1,629,696 CycleTime_backup_201001280615.trn
01/28/2010 08:00 AM 18,343,424 CycleTime_backup_201001280800.dif
01/28/2010 08:15 AM 1,564,160 CycleTime_backup_201001280815.trn
01/28/2010 10:15 AM 1,564,160 CycleTime_backup_201001281015.trn
01/28/2010 12:15 PM 1,498,624 CycleTime_backup_201001281215.trn
01/28/2010 02:15 PM 1,498,624 CycleTime_backup_201001281415.trn
01/28/2010 04:00 PM 19,392,000 CycleTime_backup_201001281600.dif
January 29, 2010 at 9:07 am
01/28/2010 12:15 AM 1,433,088 CycleTime_backup_201001280015.trn
01/28/2010 12:31 AM 124,249,600 CycleTime_backup_201001280030.bak <-- Full BAckup
01/28/2010 02:15 AM 34,797,056 CycleTime_backup_201001280215.trn <-- Why is this so big?
The transaction log backup taken at 2010-01-28 02:15 contains all transaction since the LAST transaction log backup taken at 2010-01-28 00:15.
How much activity occurs during this 2 hour window? Are there scheduled jobs, including index rebuilds, nightly processing, etc that occur during this time?
January 29, 2010 at 9:15 am
I would lean towards Lynn's thoughts. Typically maintenance plans kick off a midnight, so it's possible that you have something occurring around that time. Note that the full backup does nothing to the log, so anything since the last log backup is in the next log backup.
January 29, 2010 at 1:23 pm
There is a Full database backup taken at 12:31AM, which includes rebuilding and reorganizing the indexes and updating the statistics. This occurs between the transaction log backups at 12:15AM & 2:15AM. The large transaction log backup must be larger than the others because of the index rebuilding during the full backup. Is there any problem to do doing these backups on the hour. That means the full, differential and transaction log could all be scheduled on the same hour. Exampe if a full is done at midnight and a diffential every 8 hours and a transaction log every four hours, but starting on the hour, some of these backups would occur at the same time, or is it better to offset the times as in this example. (Full at 12:30, differentials every 8 hours on the hour and transactions every 2 hours but starting 15 minuates after the hour). Thanks for the explainations.
January 29, 2010 at 3:20 pm
They can run together, but I usually offset them to avoid resource contention issues.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply