October 26, 2011 at 1:28 pm
jared-709193 (10/26/2011)
"Log truncation frees space in the logical log by deleting inactive virtual log files form the start of the logical log."
Not quite correct.
Log truncation frees space in the log by marking active VLFs that are no longer necessary for any database process (rollback, repliation, miroring, etc, etc) as inactive. Once inactive, that VLF can be reused.
VLFs are never deleted and the only 2 states are active (one or more log records within the VLF is necessary for something) and inactive (can be reused)
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
October 26, 2011 at 1:35 pm
GSquared (10/26/2011)
kstjacques: Most likely, if it's doing that, it's breaking your log chain anyway, and your log backups aren't doing you any good.Have you tested point-in-time recovery with your current backup scheme?
Actually, now that I think about it, the error you're getting is probably caused by them "truncating the log file" by changing the recovery model back and forth before they run their full backup, and then you try to run a log backup and SQL Server detects the broken chain and throws an error because of it.
Is that truncate what they're asking you to turn off?
I have tested recovery many times and what I have in place has been working like a champ. I really believe the intention of their standard log backup is for it to function like a standard native SQL Backup.... but it doesn't work.
October 26, 2011 at 1:36 pm
GilaMonster (10/26/2011)
jared-709193 (10/26/2011)
"Log truncation frees space in the logical log by deleting inactive virtual log files form the start of the logical log."Not quite correct.
Log truncation frees space in the log by marking active VLFs that are no longer necessary for any database process (rollback, repliation, miroring, etc, etc) as inactive. Once inactive, that VLF can be reused.
VLFs are never deleted and the only 2 states are active (one or more log records within the VLF is necessary for something) and inactive (can be reused)
I trust you, but that was a direct quote from the link that I posted. 🙂
Jared
Jared
CE - Microsoft
October 26, 2011 at 1:48 pm
Gus, no problem, it was just how your final sentence
'In summary, turn off log truncation. You don't need it, don't want it, and it can (will) break your backup chain'
read to me (as if you never want to truncate the log). Truncating the log without backing up will break the log chain, but on the other hand you do still want truncation when backing up.
---------------------------------------------------------------------
October 26, 2011 at 3:10 pm
GilaMonster (10/26/2011)
GSquared (10/26/2011)
Actually, it takes work to truncate a log file without running a backup on it, like setting the database to Simple Recovery, running DBCC ShrinkFile, and then setting it back to Full or Bulk Recovery.Just switching to Simple recovery will break the log chain immediately and once in Simple a checkpoint will truncate the log.
Yep.
I've even done that deliberately a time or two.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 26, 2011 at 3:11 pm
george sibbald (10/26/2011)
Gus, no problem, it was just how your final sentence'In summary, turn off log truncation. You don't need it, don't want it, and it can (will) break your backup chain'
read to me (as if you never want to truncate the log). Truncating the log without backing up will break the log chain, but on the other hand you do still want truncation when backing up.
Ah. Thought you were reacting to the first part.
Implicit truncation as part of the backup-checkpoint process wasn't my intent there. Just manual "truncation".
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 26, 2011 at 3:13 pm
kstjacques (10/26/2011)
GSquared (10/26/2011)
kstjacques: Most likely, if it's doing that, it's breaking your log chain anyway, and your log backups aren't doing you any good.Have you tested point-in-time recovery with your current backup scheme?
Actually, now that I think about it, the error you're getting is probably caused by them "truncating the log file" by changing the recovery model back and forth before they run their full backup, and then you try to run a log backup and SQL Server detects the broken chain and throws an error because of it.
Is that truncate what they're asking you to turn off?
I have tested recovery many times and what I have in place has been working like a champ. I really believe the intention of their standard log backup is for it to function like a standard native SQL Backup.... but it doesn't work.
Totally weird.
Makes me wonder what they're doing behind the scenes. Must be seriously offbeat to have this kind of effect.
Personally, I'd avoid their product if they're going to break something, blame it on Microsoft, then try to blame it on you. (Which is essentially what they're doing.)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 26, 2011 at 4:09 pm
george sibbald (10/26/2011)
read to me (as if you never want to truncate the log).
Nooo idea why that ended up in brackets :ermm:
---------------------------------------------------------------------
October 27, 2011 at 7:08 am
Have a conference call with the vendor this morning to discuss this further. wish me luck!
My coworker who is a senior DBA (mainly Oracle) of 20 years says we should not run a log backup at the same time as a full backup. She said it doesn't make sense to put the extra waits in the system.
Regardless I think the schedule should be our choice, and not forced on us because a limitation in the vendor software.
Thanks again for all of your input. it has been invaluable!
-Kim
October 27, 2011 at 8:49 am
kstjacques (10/27/2011)
Have a conference call with the vendor this morning to discuss this further. wish me luck!My coworker who is a senior DBA (mainly Oracle) of 20 years says we should not run a log backup at the same time as a full backup. She said it doesn't make sense to put the extra waits in the system.
Regardless I think the schedule should be our choice, and not forced on us because a limitation in the vendor software.
While I sort of agree about the waits and such I also much more strongly agree that since SQL natively supports this the vendor should as well and not try to pawn off the problem as they have done. It is also important to realize that log backups and full backups are not the same beast and that a full backup doesn't clear the log. Also in most cases a log backup can be done in seconds if they are frequent enough or the data changes are not significant. Where a full backup takes much longer usually.
As a side point, as a primarily MSSQL DBA of many years, I would be somewhat hesitant in relation to Oracle and would be very careful about the guidance I give. Or a short answer of just because I have been a MSSQL DBA for the better part of 17 years doesn't mean I can (or should) speak authoritatively on Oracle in most cases.
CEWII
October 27, 2011 at 9:06 am
It seems to me you should be using the vendor software to backup the logs and the database. If you mix the 2, you may have a difficult time with recovery.
Either use native SQL Server or the Vendor solution, not a mixture of the 2.
October 27, 2011 at 10:52 am
Why don't you throw in a statement like "Well, we might need to look into alternative tools then. Have you (the vendor) ever heard of RedGate products?" 😛
October 27, 2011 at 8:36 pm
Met with the vendor. It got heated for a short time.... so much so the company called me later to apologize. Then they provided me with an emergency bug fix 🙂
Thanks again for all of your input!
-Kim
October 28, 2011 at 6:29 am
Glad we could help.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 14 posts - 31 through 43 (of 43 total)
You must be logged in to reply to this topic. Login to reply