log file of primary replica database is full

Question

log file of primary replica database is full

coolchaitu

SSChampion

Points: 10332
More actions
October 6, 2021 at 3:03 pm

#3938113

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.
October 12, 2021 at 6:43 am

This was removed by the editor as SPAM

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Mr. Brian Gale SSC-Insane Points: 24703 More actions · Answer 1

I think the easiest way to fix a "log file full" is to increase the size of the log file, but that assumes you have disk to do so.

When you say that the log backup did not work, do you mean the backup failed OR it didn't help with the log size?

Might need to look for running transactions that are filling the log. If the transaction is currently running, a backup of the log won't get that portion of the log file back if I remember right. If you have a query that is VERY long running OR a transaction that was not committed, it may be causing the log file to grow until it is 100% full.

This site has a good writeup on tlog growth - https://www.mssqltips.com/sqlservertip/4666/long-running-transactions-cause-sql-server-transaction-log-to-grow/

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

Eddie Wuerch SSChampion Points: 12532 More actions · Answer 2

Check the AG replicas. The Primary can't flush the log until the transactions have been replicated to all AG secondary replicas.

Eddie Wuerch
MCM: SQL

Jeff Moden SSC Guru Points: 1003896 More actions · Answer 3

My question would be... Do you actually need AG or would a Clustered Server be adequate? I know too many people that are using AG just because "it's too cool for school" and actually have no good reason to use it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

coolchaitu SSChampion Points: 10332 More actions · Answer 4

coolchaitu

SSChampion

Points: 10332

October 7, 2021 at 12:57 pm

#3938434

There is no space for taking log backup

Jeff Moden SSC Guru Points: 1003896 More actions · Answer 5

coolchaitu wrote:

There is no space for taking log backup

Ah... got it. Hopefully someone that uses AG might know why it filled up the log file and how to take care of this issue. I don't but this will server as a "bump" for your question.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jack Corbett SSC Guru Points: 184393 More actions · Answer 6

The only way to resolve this issue is to take a log backup. The log cannot be shrunk until a backup takes place. Once you do a log backup you can shrink the log, and then you MUST have a job that regularly backs up the log or you will get to the same place again.

Your other option is to remove the database from the AG, change the database to SIMPLE recovery, shrink the log, change the database to FULL recovery, take a full backup, restore the full backup on the secondary, re-add the database to the AG, setup a log backup job so you don't encounter the issue again.

As you can see regardless you need to have regular log backups occurring to keep the log from growing and filling the drive. Log backups are also a key component of any disaster recovery plan.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question