Availability Group Database - Resize of LOG file causes Transaction Log use to grow significantly

Question

Post reply

Availability Group Database - Resize of LOG file causes Transaction Log use to grow significantly

tking 39890

Old Hand

Points: 349
More actions
December 17, 2018 at 9:56 am

#416562

SQL Server 2016 - 13.0.4541.0
Availability group setup with 2 replicas
Most databases reside within the one AG.
Development server with relatively minimal use at this time.
I noticed that the transaction log on one of our databases had grown during an operation on one of our DEV servers. I proceeded to attempt to reduce the log file size for the database, but when I reran the Disk Usage report, it still showed the same Transaction Log Space Reserved and now the Transaction Log Space Usage has grown substantially. The database is in FULL recovery mode, so I waited until the log backup job had run, but the Transaction Log Space Usage has not changed as it typically does.
Any thoughts on why a change to the transaction log file size would trigger a transaction that remains open for the database and how to prevent this from occurring? At this point, I'm using nearly 50% of the transaction log file with these bogus transactions without a easy way to clear them out that I'm aware of. Any additional advice on how to clear out these transactions?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Tom Norton Grasshopper Points: 12 More actions · Answer 1

Have you run the following: select name, log_reuse_wait_desc, recovery_model_desc
from sys.databases

Michael L John One Orange Chip Points: 27056 More actions · Answer 2

Is the database in question part of the AG, and is it properly synchronized with the secondaries?

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

tking 39890 Old Hand Points: 349 More actions · Answer 3

Tom - Yes, I have tried to run that query and see nothing listed.

Michael - Yes it is part of the AG and synchronization shows that it is all good.

Further information, in order to clear out the transactions, I had to perform a manual "CHECKPOINT" twice in a row, then perform the transaction log backup. A single CHECKPOINT then a backup did not clear it out.

Thomas Rushton SSC-Insane Points: 22649 More actions · Answer 4

See also "Lazy Log Truncation" - transaction logs for AG databases don't behave the same way as they do for non-AG databases.

Thomas Rushton
blog: https://thelonedba.wordpress.com

muthukkumaran Kaliyamoorthy SSCoach Points: 19026 More actions · Answer 5

tking 39890 - Thursday, December 20, 2018 4:44 AM
Tom - Yes, I have tried to run that query and see nothing listed.
Michael - Yes it is part of the AG and synchronization shows that it is all good.
Further information, in order to clear out the transactions, I had to perform a manual "CHECKPOINT" twice in a row, then perform the transaction log backup. A single CHECKPOINT then a backup did not clear it out.

If it nothing, you can shrink to reasonable size. The log file will not reduce automatically unless we shrink it. Make it to decent size and schedule frequent T-log backup to reuse the VLFs. Check the AG backup setting and make sure, you are running a T-Log backup without a copy_only.

Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/

tking 39890 Old Hand Points: 349 More actions · Answer 6

Thomas - Thank you for that link. I was unaware of the lazy log truncation. However, different from what the poster noted, my transaction log actually grew in size due to these transactions which were lingering, but not in a way visible.

I do have a regular backups that are taken: weekly full, daily diffs, and every 5 minute transaction logs. None of the backups would clear these rogue transactions, until I did the double CHECKPOINT commands followed by the transaction log backup.