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

on March 30, 2017

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.

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/