December 7, 2009 at 9:17 am
We have a database on SQL2005. It is on FULL recovery mode and there is a nightly FULL backup. However, the transaction log just keeps getting bigger.
Ignoring the obvious issues about why it is on full recovery mode if it only gets a full backup every night and no transaction log backups, it has raised a question in my mind about my understanding of the backup process.
I thought the following:
1: A transaction log backup, apart from taking a backup of the transactions since the last full/log backup, also allowed those 'old' transactions to be overwritten so the same space for the log could be used and it would not grow. (assuming a constant rate of transactions).
2: That a full backup effectively did a transaction log backup at the same time as it backed up the data and this would also allow the previous transaction log events to be overwritten and the transaction log space re-used?
Am I wrong in my understanding or am I right, and should be looking elsewhere for this inflated transaction log file?
It is the CMS database for Business Objects XI in case that is relevant.
TIA
Rob.
December 7, 2009 at 9:39 am
robert.jackson-943581 (12/7/2009)
1: A transaction log backup, apart from taking a backup of the transactions since the last full/log backup, also allowed those 'old' transactions to be overwritten so the same space for the log could be used and it would not grow. (assuming a constant rate of transactions).
Correct
2: That a full backup effectively did a transaction log backup at the same time as it backed up the data and this would also allow the previous transaction log events to be overwritten and the transaction log space re-used?
Incorrect.
The only backup that truncates a transaction log and allows for the space to be reused is a transaction log backup. Neither full nor differential backups will affect the transaction log
Please read through this - Managing Transaction Logs[/url]
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
December 7, 2009 at 9:58 am
Hi. Thx for the quick response. I read that link earlier when I picked it up off a different thread. Very informative and helpful.
However, it did not seem to answer my question, (which you now have) but reassured me about some others. Everything I had read mentionined it started a new log chain, which I took to mean it would effectively restart the avaialble log.
So, if transaction backups were being done, for example this theoretical scenario:
- Full backup daily 6:30am (run time 5 minutes)
- Logbackups hourly (on the hour)
The 7am backup would cover transactions from 6am to 7am and that when recovering from that mornings full backup, it would only use 'half' that 7am transaction log plus any subsequent ones. However, if recovering from the day befores full back we would just use all the transaction logs (from that days 7am log backup).
That makes sense.
Thanks Again
December 7, 2009 at 10:16 am
robert.jackson-943581 (12/7/2009)
However, it did not seem to answer my question, (which you now have) but reassured me about some others. Everything I had read mentionined it started a new log chain, which I took to mean it would effectively restart the avaialble log.
A full or differential backup starts (as in creates a base for) a log chain if the log chain has been broken by an explicit truncate or a switch to simple recovery. It doesn't affect the log, it just gives a starting point for the first log backup after a broken log chain and a backup to start the restore from.
The 7am backup would cover transactions from 6am to 7am and that when recovering from that mornings full backup, it would only use 'half' that 7am transaction log plus any subsequent ones. However, if recovering from the day befores full back we would just use all the transaction logs (from that days 7am log backup).
Yup.
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