August 18, 2014 at 10:42 pm
I was discussing the behavior of this with another DBA (no-no's of its use aside, we are trying to understand an existing setup).
I thought, this would not clear out log records where a log reader replication agent has not yet marked them as read.
He thinks, this will clear out log records, even where a log reader replication agent hasn't gotten to them yet.
I looked up a bunch of blog posts and, surprise surprise, I can get both answers depending on what I type in.
Anyone know?
August 19, 2014 at 12:37 am
No, it will not clear out the log or mark the transactions inactive.
Just to be sure (as you have found answers suggesting otherwise), I test this with the "Sync With Backup" replication option set to both True and False on a published database.
The 'backup log with TRUNCATE_ONLY' statement will run without error. The following regular log backup will then fail to run, reporting the following error:
Msg 4214, Level 16, State 1, Line 2
BACKUP LOG cannot be performed because there is no current database backup.
However, if you run a new full backup, then a regular log backup on the published db, then the transactions from before the 'log backup WITH TRUNCATE_ONLY' will propegate to the subscriber; so no loss of transactions, no matter what sync_with_backup is set to. Helpful link, Strategies for Backing Up Transactional Replication http://msdn.microsoft.com/en-US/library/ms152560%28v=sql.90%29.aspx
August 19, 2014 at 2:24 am
codykonior (8/18/2014)
I thought, this would not clear out log records where a log reader replication agent has not yet marked them as read.He thinks, this will clear out log records, even where a log reader replication agent hasn't gotten to them yet.
You are correct.
Backup log with truncate only is almost equivalent to taking a log backup and deleting the file. It is equivalent to switching to simple recovery model, running a checkpoint and switching back to full immediately.
A log backup will (in full/bulk logged) mark VLFs as reusable when they are no longer needed. Not needed for active transactions, replication, mirroring or any of the other things that use the log. If a VLF is needed for replication, a log backup will not mark it as reusable.
Adding Truncate_Only doesn't change that part of its behaviour.
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
August 19, 2014 at 2:38 am
Cool.
We did find the cause if the issue. Log records were being marked inactive by the software doing the log reading, not by the log_truncate command.
"How? Why?"
It's Oracle GoldenGate. It issues a repl_done command even before it has read the log records. Why? Because it expects to be able to read the records from the log backups. We don't keep those because disk space is at such a premium.
It has an option not to mark log records read until it has actually read them but it's not set by default. What a concept.
August 19, 2014 at 3:48 am
Ok..... I'll mark that one down as another tool to avoid.
p.s. If you don't keep the log backups, then you can't restore to point in time. In that case, assuming that's acceptable, why even keep the DB in full recovery?
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
August 19, 2014 at 5:13 am
No reason, it can be switched to simple mode; the people who set it up in the past did not have very stringent requirements for information when doing these things. It was one size fits all!
(PS: Imagine 200+ enterprise servers set with data files to grow in 1MB increments. Yup.)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply