Unable to shrink the log file
-
I have this on going issue where the log file of one of the database is constantly growing. I am trying to shrink it down to a reasonable size but not able to.
Environment details:
ON Prem:
SQL1 Primary
SQL2 Secondary
SQL3 Secondary
SQL4 Secondary
DR site
SQL15 Primary
SQL16 Secondary
SQL17 Secondary
SQL18 Secondary
There is also a DAG between 1 and 15. 15 being the forwarder.
I checked and there is no open tran, took the log backup couple of time already, AG dashboard looks healthy
Any help/advise is highly appreciated.
"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]
-
If you have one database with a log_reuse_wait_desc of LOG_BACKUP and the rest with AVAILABILITY_REPLICA then I think that's your answer. You need to make sure your AG databases have a SYNCHRONIZED status, and that the other database has had its log backed up.
John
-
Is the AG been configured across the DC or same. Did you see any latency and what is the mode sync or async? As long as the pair replica getting data this will go away.
log backup - change schedule to frequent T-log backup.
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/ -
I have been monitoring the redo_queue_length on 18 for past hour and the size is not decreasing. Its staying around '3341.....'. I am really not sure what to look for or how to troubleshoot because I read somewhere on google to look for long running queries which I don't see any. No blocking etc. How do I resolve this issue?
"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]
-
Ag has been configured the same across all servers.
ON Prem:
SQL1 Primary synch
SQL2 Secondary synch
SQL3 Secondary Asynch
SQL4 Secondary synch
DR site
SQL15 Primary synch
SQL16 Secondary synch
SQL17 Secondary Asynch
SQL18 Secondary synch
log backup runs every 15 minutes
"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]
-
AVAILABILITY_REPLICA - wait means SQL Server is waiting to send log data to one of your AG group. Better check AG dashboard with selected columns, you can understand where it is pending.
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply