January 8, 2009 at 9:12 am
My SQL Server 2005 is doing full backups at 1 AM and transaction log backups every 30 minutes between 2 AM and 11:59 PM. The full backup takes about 15 minutes and completes around 1:15 AM so no overlap with transaction log backup. I created the maintenance plans using the wizard.
The full backup BAK file is about 3.5GB and the first TRN backup file is over 4GB. Subsequent TRN files are only 4MB. There is no customer activity between Midnight and 2 AM that would explain large transaction log activity. It seems like the full backup is somehow "touching" every record triggering a required transaction log entry, but that makes no sense since backups are a read action requiring no transaction log, right?
Any idea why this first TRN file is so huge?
Thanks,
Dan Murray
January 8, 2009 at 11:15 am
When's the last log backup from before the full? Full backups don't truncate the log, so you have to look at the activity from the last tran log.
Full recovery? Bulk logged recovery?
Are there any jobs running around that time of night? Specifically I'm thinking index rebuilds.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 8, 2009 at 12:32 pm
GilaMonster (1/8/2009)
When's the last log backup from before the full? Full backups don't truncate the log, so you have to look at the activity from the last tran log.Full recovery? Bulk logged recovery?
Are there any jobs running around that time of night? Specifically I'm thinking index rebuilds.
The last tran log backup is 11:30 PM, so 90 minutes before the full backup starts. The amount of minute-by-minute activity on this database is steady but small, not a high transaction volume.
Full Recovery mode
The only activity on the database is from the maintenenace plan which starts at 1 AM - Check DB Integrity, Reorganize Indexes, Update Statistics, Backup, Cleanup History logs, Maintenance Plan Cleanup. All operations complete in about 15 minutes.
Thanks,
Dan Murray
January 8, 2009 at 2:28 pm
murrayd (1/8/2009)
Reorganize Indexes,
That'll do it.
If you're rebuilding all indexes on all tables than you are 'changing' every row in the database. Index rebuilds are fully logged in full recovery (and index reorganise is fully logged in all recovery models)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply