September 18, 2020 at 2:06 pm
I have an issue with a log file which I am trying to solve. FYI. I don't see any long running, open transaction which would fill the log file to 70GB.
Total 12 servers:
ON prem 4 servers: SQL01, SQL02,SQL03,SQL04 (On prem) SQL01 is primary, rest of the servers are secondary.
DR site, 4 servers: SQL11, SQL12, SQL13, SQL14. There is a distributed availability group between SQL01 and SQL11. Things are great, no issues but we created another 4 more server (SQL21,SQL22,SQL23,SQL24) yesterday and created distributed availability group between SQL11 and SQL21. Now the log file size is close to 70 GB. I ran a log backup on SQL01 twice already, tried resizing the log file size to 20 GB but the size is still 70 GB and growing. Any advise?
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
September 18, 2020 at 2:15 pm
You might want to take a look at VLFs ( Virtual log files) in the transaction log.
What are SQL Virtual Log Files aka SQL Server VLFs
A Busy/Accidental DBA’s Guide to Managing VLFs
Is your network fine? are transactions replicating fine to the other replicas? remember that in an always on before commiting the transaction in the primary replica, it has to commit the transactions in all the secondaries and if you have network bottleneck your transactions might be taking longer to replicate and to be commited in the primary replica.
If you don't have issues as mentioned before you might want to take a look at virtual log files.
regards,
September 18, 2020 at 2:22 pm
I just checked log_reuse_wait_desc and it shows 'AVAILABILITY_REPLICA'. I know now what's causing this.
Thanks for the help.
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
September 18, 2020 at 2:26 pm
Is the database synchronizing on each node?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply