Why is My Transaction Log Growing in My Availability Group? (Dear SQL DBA Episode 36)

A database transaction log is expanding, even though the DBA is running log backups and doesn’t see an open transaction? What’s going on with this Availability Group?

Links to timestamps in this 30 minute episode if you want to skip ahead

  • 00:49 What’s new @ SQL Workbooks
  • 03:22 Info on SQLPASS Summit 2017 Content Survey & a heads-up for aspiring precon speakers
  • 05:35 This week’s question about t-log growth

Subscribe to my YouTube channel, or check out the audio podcast to listen anywhere, anytime. Links from this episode are in this post below the video and in the YouTube description.

Links from this week’s episode

SQLPass Summit 2017 Content Survey (enter your email for a chance to win free registration): https://www.surveymonkey.com/r/KFS2ZJW

If you are interested in submitting a pre-conference session for SQLPASS Summit 2017, fill out the form on Wendy’s blog post here: http://www.pass.org/Community/PASSBlog/tabid/1476/entryid/839/Changes-to-the-PASS-Summit-2017-Program-Pre-Conference-Call-for-Interest-and-a-Community-Survey.aspx

Lazy log truncation (SQL Server PFE blog by Lisa Gardner) : https://blogs.msdn.microsoft.com/sql_pfe_blog/2013/06/27/lazy-log-truncation-clearing-of-sql-transaction-log-vlf-status-deferred

sys.dm_hadr_database_replica_states: https://docs.microsoft.com/sql/relational-databases/system-dynamic-management-views/sys-dm-hadr-database-replica-states-transact-sql

Chasing Ghost cleanup in Availability Groups (by Amit Banerjee): https://troubleshootingsql.com/2014/07/25/chasing-the-ghost-cleanup-in-an-availability-group/

, , , ,

2 Responses to Why is My Transaction Log Growing in My Availability Group? (Dear SQL DBA Episode 36)

  1. tim April 6, 2017 at 11:40 am #

    Have a better one log growing on both. Trans log being backed up by job, not failing. Problem is this was happening on RO node not active, never really failed just never really backed up prod. Checkbox on AG group properties told it to use that for backup not the real active node

    • Kendra Little April 6, 2017 at 2:24 pm #

      I may need another cup of coffee. Do you mean the transaction log backup wasn’t running at all?

Leave a Reply