Logs not freeing up space for a database in alwaysOn group after failover

Question

question

noobdba asked Dec 16, '20

Logs not freeing up space for a database in alwaysOn group after failover

There are two replicas in alwaysOn. An outage shut primary down. So, the secondary became the primary after the previous one came back up. There were a few databases that were in suspended state over night and one being sharepoint portal content database.

When data movement was resumed, the databases synced. All is green in dashboard. The log sent time from primary content db and log received time on secondary is more or less the same time. The logs are backed up every hour.

However, the used % keeps growing. It was at 3% used out of 11 GB log space. In about four hours it has come to 6.5%. About a couple of hours back completed a full backup of the dabatase (followed by logs) and it hasn't gone down.

Any insight would be highly appreciated.

transaction-log always-on alwayson space issue

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2020-12-16T23:07:43Z

noobdba answered Dec 16, '20

Fixed by itself.

select * from sys.sysprocesses where blocked<>0

returned no results.

Ran "select * from sys.dm_hadr_database_replica_states" to see if status would be reported differently from what was on the dashboard. It looked fine.

So, a recent log backup actually freed the space (might have been like the 6th or 7th log backup. there was also a db backup prior to the last two log backups).

Not sure what was up. The db is being backed up from primary. The secondary db (which was primary prior to failover/outage) was suspended overnight and resumed data movement earlier in the morning.

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley ♦♦ commented · Dec 17, 2020 at 08:41 AM

Glad that the issue has resolved itself. By what you've described it sounds like there could have been an long-running or open transaction - the log file can only free up space if the file contains portions that are not marked as part of the active part.
If this happens again, there's no need to try and guess what is preventing the log from releasing free space - you can see the reason by looking at log_reuse_wait_desc column in sys.databases

0 ·

Answer 2 · 2020-12-17T22:49:30Z

noobdba answered Dec 17, '20

@Kev Riley

Thank you.

Happened again. This time, initiated the backup from a different vendor (moved to a different product this afternoon). The alwaysOn preference is set to backup from the secondary. The backup and logs are being backed up through secondary since this afternoon. But the logs for some of the sharepoint databases in primary shows %used growing. [interesting thing is the backup client uses secondary to backup even though secondary is configured as non readable]

The log_reuse_wait_desc shows NOTHING in both primary and secondary.

Not getting why its not freeing up space this time.

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question