log file of primary replica database is full

Chaitanya Kiran 776 Reputation points
2021-10-06T15:02:57.063+00:00

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.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,291 questions
{count} votes

3 answers

Sort by: Most helpful
  1. Erland Sommarskog 106.2K Reputation points MVP
    2021-10-06T21:35:54+00:00

    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,

    0 comments No comments

  2. CathyJi-MSFT 21,121 Reputation points Microsoft Vendor
    2021-10-07T00:15:52.647+00:00

    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.


  3. Olaf Helper 43,081 Reputation points
    2021-10-07T08:34:28.117+00:00

    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?

    0 comments No comments