AlwaysOn Secondary backup (Full & Log) - Primary T-Log not shrinking
-
Hi All,
We recently changed to AlwaysOn, running SQL Server 2012, 2014 and 2016 clusters in AlwaysOn config.
Our backups are running successfully on synchronous Secondary Replica (Preferred Backup is Secondary). This includes "Full Copy-Only" and regular T-Log backups.The issue is the Primary Transaction logs on all 3 AO's are now as big as, or bigger than, the DB mdf files. Although the T-logs are 98% or more free space.
DBCC LogInfo on any one of them gives me similar to this...
As you see here, the log is not wrapping. The T-Logs are all 98%+ free space but they keep growing with the active VLF's at the end, therefore SHRINK has no affect.
I'm not totally surprised because of the Full Copy-Only. It's my understanding that backups on the Secondary are "normal", but how do I now tame the T-Log files?
Much thanks!
-
Are you also doing copy only log backups by any chance?
-
The copy-only full backups won't make any difference because full backups don't truncate the transaction log anyway. As anthony says, check that your log backups aren't copy-only.
What's the log_reuse_wait_desc in sys.databases?
(Also it sounds as though you're expecting transaction log backups to shrink the log file, which they won't do; they only truncate it.)
-
You're suffering from "Lazy Log Truncation". It got me last month too... The article linked is a Microsoft PFE report / note about what's going on, with pictures...
Thomas Rushton
blog: https://thelonedba.wordpress.com - Roger Mac - Tuesday, September 19, 2017 6:36 PMHi All,
We recently changed to AlwaysOn, running SQL Server 2012, 2014 and 2016 clusters in AlwaysOn config.
Our backups are running successfully on synchronous Secondary Replica (Preferred Backup is Secondary). This includes "Full Copy-Only" and regular T-Log backups.The issue is the Primary Transaction logs on all 3 AO's are now as big as, or bigger than, the DB mdf files. Although the T-logs are 98% or more free space.
DBCC LogInfo on any one of them gives me similar to this...
As you see here, the log is not wrapping. The T-Logs are all 98%+ free space but they keep growing with the active VLF's at the end, therefore SHRINK has no affect.
I'm not totally surprised because of the Full Copy-Only. It's my understanding that backups on the Secondary are "normal", but how do I now tame the T-Log files?
Much thanks!
if your log backups are occurring on the secondary it can take a short time to filter through.
What is the latency you are seeing here-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
- anthony.green - Wednesday, September 20, 2017 1:54 AM
Nope, not doing Copy-Only log backups.
- Beatrix Kiddo - Wednesday, September 20, 2017 1:58 AM
log_reuse_wait_desc = "NOTHING".
And yes, I was incorrectly expecting the log backup to affect log file size. However, performing a ShrinkFILE on the log produced no change in log file size. It remained at 149 GB with 98% space free (the database .MDF is 151 GB).
- Perry Whittle - Wednesday, September 20, 2017 3:39 AMRoger Mac - Tuesday, September 19, 2017 6:36 PMHi All,
We recently changed to AlwaysOn, running SQL Server 2012, 2014 and 2016 clusters in AlwaysOn config.
Our backups are running successfully on synchronous Secondary Replica (Preferred Backup is Secondary). This includes "Full Copy-Only" and regular T-Log backups.The issue is the Primary Transaction logs on all 3 AO's are now as big as, or bigger than, the DB mdf files. Although the T-logs are 98% or more free space.
DBCC LogInfo on any one of them gives me similar to this...
As you see here, the log is not wrapping. The T-Logs are all 98%+ free space but they keep growing with the active VLF's at the end, therefore SHRINK has no affect.
I'm not totally surprised because of the Full Copy-Only. It's my understanding that backups on the Secondary are "normal", but how do I now tame the T-Log files?
Much thanks!
if your log backups are occurring on the secondary it can take a short time to filter through.
What is the latency you are seeing hereI noticed the log file was the same size as the database mid-afternoon. 8 to 10 hours later nothing changed.
-
ok, so I ran a Full backup on the AlwaysOn Primary, Success. Then ran a Log backup on the Primary. Success... not so fast, although the log backup on the Primary AG said all the DB's logs were backed up, I could not find the log backup files anywhere (the location is the same as the Full backups which I found and even a copy&paste of the location from the Full script to the Log script didn't help).
Eventually, in SSMS I right-clicked the DB name and selected "Backup" and did a log backup that way, again copy & paste of the location and this time the log backups were where I expected. NOW when I did a ShrinkFile of the T-Log it reduced from 149 GB to 39 GB. That will do for tonight.
SO, It appears the Secondary Full (copy-only) + Secondary Log (not copy-only) backups do not mark the Primary Log VFL's for reuse?
Are they supposed to? - Roger Mac - Wednesday, September 20, 2017 4:21 AMPerry Whittle - Wednesday, September 20, 2017 3:39 AMRoger Mac - Tuesday, September 19, 2017 6:36 PMHi All,
We recently changed to AlwaysOn, running SQL Server 2012, 2014 and 2016 clusters in AlwaysOn config.
Our backups are running successfully on synchronous Secondary Replica (Preferred Backup is Secondary). This includes "Full Copy-Only" and regular T-Log backups.The issue is the Primary Transaction logs on all 3 AO's are now as big as, or bigger than, the DB mdf files. Although the T-logs are 98% or more free space.
DBCC LogInfo on any one of them gives me similar to this...
As you see here, the log is not wrapping. The T-Logs are all 98%+ free space but they keep growing with the active VLF's at the end, therefore SHRINK has no affect.
I'm not totally surprised because of the Full Copy-Only. It's my understanding that backups on the Secondary are "normal", but how do I now tame the T-Log files?
Much thanks!
if your log backups are occurring on the secondary it can take a short time to filter through.
What is the latency you are seeing hereI noticed the log file was the same size as the database mid-afternoon. 8 to 10 hours later nothing changed.
have you tried running 2 log backups in quick succession
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
- ThomasRushton - Wednesday, September 20, 2017 2:00 AM
Interesting, but not quite.
The log file is 149 GB with 1067 VLF files.
- 4 VLF's have a status of 2 (they also have the largest FSeqNo numbers)
- 1063 VLF's have a status of 0.So I have plenty of 0 status VLF's but ShrinkFile couldn't get to them (until I was able to create, and locate, a log backup on the AO Primary). I had been running log backups on the Secondary for almost a week.
- Perry Whittle - Wednesday, September 20, 2017 4:35 AMRoger Mac - Wednesday, September 20, 2017 4:21 AMPerry Whittle - Wednesday, September 20, 2017 3:39 AMRoger Mac - Tuesday, September 19, 2017 6:36 PMHi All,
We recently changed to AlwaysOn, running SQL Server 2012, 2014 and 2016 clusters in AlwaysOn config.
Our backups are running successfully on synchronous Secondary Replica (Preferred Backup is Secondary). This includes "Full Copy-Only" and regular T-Log backups.The issue is the Primary Transaction logs on all 3 AO's are now as big as, or bigger than, the DB mdf files. Although the T-logs are 98% or more free space.
DBCC LogInfo on any one of them gives me similar to this...
As you see here, the log is not wrapping. The T-Logs are all 98%+ free space but they keep growing with the active VLF's at the end, therefore SHRINK has no affect.
I'm not totally surprised because of the Full Copy-Only. It's my understanding that backups on the Secondary are "normal", but how do I now tame the T-Log files?
Much thanks!
if your log backups are occurring on the secondary it can take a short time to filter through.
What is the latency you are seeing hereI noticed the log file was the same size as the database mid-afternoon. 8 to 10 hours later nothing changed.
have you tried running 2 log backups in quick succession
Depends what you mean by "Quick". 🙂 I did run two log backups within roughly 30 minutes, but now I don't recall if that was on the Secondary or on the Primary.
- Roger Mac - Wednesday, September 20, 2017 4:35 AM
Full backups, of any form, do not affect the log, and that is expected. Full backups have never affected VLFs
Log backups on the secondary do mark the primary's VLFs as reusable, shown in your case by the status 0 VLFs at the beginning of the log.
Why the log isn't wrapping around, however, I don't know.Monitor the VLFs for a while, see if there are any more grow incidents with status 0 VLFs in the file. If there are, it might be worth calling MS support and opening a case.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Ok, to sum up this whole adventure. 🙂
Follow-up question:
So, am I correct to think that running a regular Log backup (not copy-only) on the SECONDARY should communicate back to the Primary and "mark" (LSN numbers etc.) the Primary log as backed up? If this is true then I should be able to run a ShrinkFile on the Primary log (after a Secondary log backup) and see results. But in this case running a ShrinkFile on the Primary log had no effect.Summary:
FYI - We run Full copy-only backups on the Secondary once a day with Log backups (not copy-only) on the Secondary every 30 minutes.
What I was seeing was, with all backups occurring on the Secondary, the Primary log file has 99% free space but continued to grow creating new VLF files where the active VLF's were always at the tail end of the log file (not wrapping to reuse old "Status=0" VLF's at the beginning of the log file). Because the active VLF's (Status=2) were always at the tail end of the log file it caused my ShrinkFile on the log to have no effect on the log file size. (A Shrink can only trim empty space at the end of the file, not empty space at the beginning - think of it like an "RTrim()" function on the contents of the log file 🙂 ).I eventually thought to run a Full (not copy-only) backup on the Primary followed by a Log backup on the Primary to make sure I covered any reset/checkpoint options, and shortly after that was able to successfully ShrinkFile the log file from 149 GB to 39 GB.
I'll be watching this, and our other 8 new AO clusters, more closely going forward.
Perhaps as we move DB's to the AO clusters I just need to make sure I run a Full normal backup on the Primary (it may be in "Simple" mode somewhere during the move to the new server) before kicking off daily Full copy-only backups on the Secondary so that the Secondary Log backups are properly "set"?
Thanks to all of you for your input. - GilaMonster - Wednesday, September 20, 2017 11:07 AM
X-C-Lent info, Gail. Exactly my question/concern. I'll be watching this going forward.
Hey, does anyone have a script (PowerShell?) that can run against multiple servers and return log file info (size, % free, etc.).
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply