1

I've got a SQL Server 2016 AG set up with only two replicas (primary and secondary). Both synchronous, non-readable secondary, and backup the primary only.

Transaction Log backups occur on the primary replica via scheduled SQL Jobs that execute on both replicas every half hour. Full backups occur nightly. FULL recovery model.

The issue is over time the secondary replica log file eventually grows to be larger than the data file, while the primary remains fine. The only thing I do that helps is a manual failover, which seems to kick off the log backup jobs on the (now old) secondary replica since its readable again. Any tips on how to handle this?

Improve this question
9
  • It might be useful to include the exact version of SQL Server 2016 you're running (SELECT @@VERSION;). – Josh Darnell Jan 19 at 22:08
  • The log on the second database should grow and shrink with the primary, as the growth/shrink are logged events that get redone on secondary. – AMtwo Jan 19 at 23:50
  • 1
    Is your AG healthy? Have you ever failed over to secondary to confirm that it's working properly? This seems like the database on secondary either isn't joined to the AG, isn't joined properly, or sync/redo are not healthy – AMtwo Jan 19 at 23:52
  • @AMtwo I had the same thought about the AG being healthy, but the OP mentioned they periodically do failovers to get the log file back under control "The only thing I do that helps is a manual failover, which seems to kick off the log backup jobs on the (now old) secondary replica since its readable again." – Josh Darnell Jan 20 at 0:30
  • 1
    @AMtwo Yeah, something is definitely weird! I agree it's not a backup issue, which is why I'm hoping the log_reuse_wait_desc on the secondary will provide a clue 🤷‍♂️ I was wondering if a long-running snapshot query on the secondary would block log truncation on the secondary without blocking redo, but I'm drawing a blank, and don't have an AG handy to test with at the moment 😀 – Josh Darnell Jan 20 at 14:35
3

Since the log file is only growing on the secondary replica, something is preventing transaction log truncation on that database (but not on the primary).

You'll need to check the value of log_reuse_wait_desc in sys.databases on the secondary replica to see why the transaction log isn't being cleared there.

Since log_reuse_wait_desc is currently showing "NOTHING" you should check on the health of the AG itself. Open up the AG dashboard (in SSMS, under "Always on High Availability" right-click the AG and choose "Show Dashboard). Check to see if there are any errors or warnings, and confirm that the secondary replica is listed and is healthy / synchronized.

Additional, check out the SQL Server error log on the secondary to see if there are any errors or warnings related to the AG or the database in question. This is a weird one!

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.