January 11, 2010 at 8:12 am
I have scheduled a full backup after the rebuild index job, so that I don't get a huge transaction log backup. But a transaction log after the full backup was very huge.
My assumption is that after the full backup, sql server (2000) should cleanup transaction log, but its not doing that. Any ideas on that?
Thanks
January 11, 2010 at 8:16 am
A full backup does not truncate the transaction log. Never has. A transaction log backup will contain the transactions since the last log backup.
The only time when a log backup contains only the transactions since the last full backup is when the log chain was broken (switch to simple or truncate log) and the full backup in question is the first one after that.
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 11, 2010 at 8:21 am
san43 (1/11/2010)
I have scheduled a full backup after the rebuild index job, so that I don't get a huge transaction log backup. But a transaction log after the full backup was very huge.My assumption is that after the full backup, sql server (2000) should cleanup transaction log, but its not doing that. Any ideas on that?
Thanks
Your assumption that a full backup removes data from the transaction log is wrong.
You should scheduled transaction log backups to run at short intervals to help keep the transaction log size under control, and to reduce the risk of data loss on a system failure. Every 15 minutes, 24x7 is a good starting point.
If you are re-indexing large tables, you may still see a large transaction log, since that happens in a single transaction. Instead, you can do index defragmentation, because it uses smaller transactions.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply