question

VijayKumar768 avatar image
0 Votes"
VijayKumar768 asked ErlandSommarskog commented

AlwayOn DB sync

Hi Team,

is it possible to perform transactional log backup on Primary while secondary DB in initializing/In Recovery in SQL Serrver 2016 AlwayOn Availability?

Because logspace used is 99%.



sql-server-generalsql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

Yufeishao-msft avatar image
0 Votes"
Yufeishao-msft answered ErlandSommarskog commented

Hi @VijayKumar768,

You taking log backups on the primary does not change anything. When the log is required to be kept for a secondary, backup does not truncate the log. The secondary is simply running recovery on the database, that's all it does, applying the log received from the primary. The Write Ahead Protocol guarantees that whatever the secondary is 'recovering' is going to be identical to whatever the primary has in the database.

Here are the relevant explanations:
https://dba.stackexchange.com/questions/89670/how-alwayson-availability-group-secondary-replica-catches-up-with-primary-after

logspace used is 99%

Try to reboot each server in Availability Group in turn. If it does not work, you can try to do the shrink on the primary

https://dba.stackexchange.com/questions/73850/shrink-transaction-log-while-using-alwayson-availability-group


If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.





· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Try to reboot each server in Availability Group in turn. If it does not work, you can try to do the shrink on the primary

If 99% of the logspace is used, a shrink on the log file will have little effect, as there is nothing that can be shrunk.

0 Votes 0 ·

99% means that the log file is nearly full and the log will autogrow if more space will be required.

And as soon as you do a log backup, the space will be release and the % of free space will go down to ~0%.

0 Votes 0 ·

And as soon as you do a log backup, the space will be release

Maybe. For log space to be released, a number of conditions to be fulfilled. For instance, in an AG with a secondary which is behind, the log cannot be truncated on the the secondary has caught up.

0 Votes 0 ·