log file of primary replica database is full

Question

log file of primary replica database is full

Chaitanya Kiran 801

Good Morning,

We have AG configured. The log file of primary replica database is full. I tried to take log backup but it did not work. I tried to shrink but did not work. Please let me know how to resolve.

Robbie Varn 351 Reputation points

2021-10-06T15:43:09.71+00:00

What is it waiting on?
Pls run this and post the results:
SELECT log_reuse_wait_desc
FROM sys.databases
WHERE name = 'MyDatabase'
Chaitanya Kiran 801 Reputation points

2021-10-06T18:45:53.22+00:00

AVAILABILITY REPLICA

3 answers

Your answer

Robbie Varn 351 Reputation points

2021-10-06T15:43:09.71+00:00

What is it waiting on?
Pls run this and post the results:
SELECT log_reuse_wait_desc
FROM sys.databases
WHERE name = 'MyDatabase'
Chaitanya Kiran 801 Reputation points

2021-10-06T18:45:53.22+00:00

AVAILABILITY REPLICA

Answer 1

Erland Sommarskog 120.2K MVP

Could it be that there is a replica that is off-line or for some other reason is not consuming the log fed to it? That is how I would interpret the value of log_reuse_wait_desc.

It's useless trying to shrink that a file is full. A file can only shrink if there is unused space in it, and a full file apparently does not have any,

Answer 2

CathyJi-MSFT 22,371 Microsoft External Staff

Hi @Chaitanya Kiran ,

The log_reuse_wait_desc shows AVAILABILITY_REPLICA, that means SQL Server is waiting to send log data to one of your Always On Availability Group secondary replicas. There can be multiple reasons for why this particular wait type may show up.

Reason 1: Slow Network or Unreachable Network
In this case, you may want to check with your network admin and check the reasons for the slow network.

Reason 2: Long Running Transactions
Sometimes it is quite possible you will see both the primary and secondary database both synced but still you will see the log_reuse_wait_desc displaying AVAILABILITY_REPLICA. In that case, it is quite possible that on the primary server there is a long-running transaction, which is currently actively writing in the log file which is yet not shipped to secondary.

Reason 3: Resource Constraint
In a rare incident, it is quite possible that sometimes on the secondary we run out of the worker thread. In that case, you can either restart the sync or if that works, you may have to reset the sync from the beginning.

Reason 4: One secondary replica may be down altogether
Please check the SQL AG state from AG dashboard.

If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar thread.

Chaitanya Kiran 801 Reputation points

2021-10-07T03:31:17.237+00:00

Hi Cathy,

Can you please help with solution
CathyJi-MSFT 22,371 Reputation points Microsoft External Staff

2021-10-07T05:53:08.397+00:00

Hi @Chaitanya Kiran ,

Did you mean that how to check the possible reasons that I mentioned? If i misunderstood, please let me know.
Chaitanya Kiran 801 Reputation points

2021-10-07T06:03:46.4+00:00

For eg, you mentioned this as a reason:
There is a long running transaction which is currently actively writing in the log file which is yet not shipped to secondary.

So, what should I do to resolve?
CathyJi-MSFT 22,371 Reputation points Microsoft External Staff

2021-10-07T06:22:29.677+00:00

Waiting for the long running query complete or kill the session. But one we kill a session, it undergoes through the rollback process, and it might take time and resources as well to perform a rollback. Suggest you waiting the query complete. Then backup the log, shrink the log file.
Erland Sommarskog 120.2K Reputation points MVP

2021-10-07T21:29:22.683+00:00

I don't think that is very good advice, Cathy.

If the transaction log is full, and there is an open transaction that prevents log truncation, it is not very likely that that transaction can complete. Since to complete, it needs to write to the transaction log. And that will fail.

And moreover, it would have failed already, unless for some reason it is not writing to the transaction log right now. That could be because it is a distributed transaction, right now working in a different database on a different server.

Or it can be a transaction which has been left orphaned, for instance in an SSMS window.

I would definitely kill any open transaction there might be.

Although, I guess that by now Kiran has resolved the urgent issue, if so by adding more disk space. It seems unlikely that you can accept an AG to be down for 24 hours. (Unless it is a test environment.)
priyanka singh 0 Reputation points

2024-03-13T17:07:09.7533333+00:00

how we will get to know that the secondary replica is out of woker thread
priyanka singh 0 Reputation points

2024-03-13T17:08:26.86+00:00

how we will get to know that secondary replica is out of worker thread
Erland Sommarskog 120.2K Reputation points MVP

2024-03-13T22:15:34.6966667+00:00

You would see a lot of THREADPOOL waits.

Answer 3

Olaf Helper 46,546

I tried to take log backup but it did not work. I

What means "did not work"? Do you get an error message and if, which one?

Share via

log file of primary replica database is full

3 answers

Your answer