Cannot shrink log file 2 because the logical log file located at the end of the file is in use

pdsQsql · February 1, 2022, 9:46pm

We have Sql Server 2016 and set up Always ON with 2nd replica (Primary and Secondary)
Currently looks like Log file filling up, getting disk pace full.
I tried to resume the secondary database which was in "Not Synchronizing/Suspect" status after adding disk space and worked fine for 1 day and again it's starting to fill up the space (Log file)and we are out of disk space.
We have Primary is still Synchronized.
We are running log backup every hour on Primary and FULL Backup nightly.
I tried to Shrink the Log file but getting following error:

Cannot shrink log file 2 (SQL1_log) because the logical log file located at the end of the file is in use.

1) When I ran
SELECT name, log_reuse_wait_desc FROM sys.databases

Other Databases showing log_reuse_wait_desc = "Nothing" except my Primary DB showing "AVAILABILITY_REPLICA"

DBCC LOGINFOShows lot of almost (1000+) VLF files with Status = 2

DBCC SQLPERF(LOGSPACE)

Database Name	Log Size (MB)	Log Space Used (%)	Status
master	1.992188	67.74509	0
tempdb	1023.992	5.879113	0
model	71.99219	22.20632	0
msdb	19.61719	12.31332	0
TSQL1	69162.62	83.41731	0

When I am running OPEN TRAN ==> Nothing getting any Blocking/Open Session ID so I can't Kill anything

Can I know the Best option?

robert_volk · February 2, 2022, 2:53am

You need to remove the non-synchronizing replica(s) from the availability group, then make at least one new transaction log backup before you try to shrink the log files again.

If you have limited disk space for your transaction logs, you may want to set a maximum file size for the log file, leaving some free space on the disk. If you're using availability groups, each of your replicas (primary and secondary) should have exactly the same disk setup: drive letters, drive sizes, file placement, etc. Any discrepancy can interfere with the AG's proper functioning.

Once you get the log file shrunk and ensure the secondary's disks are configured properly, then you should add the replica back to the availability group. Synchronize as you please (auto seeding, full sync or manual) but make sure you have an intact log chain. Try to avoid switching to SIMPLE recovery in order to shrink the log files, instead make multiple log backups and restore them manually on the secondary instead.

If your secondary fails again to synchronize and the log grows, you need to determine what's causing it to fall behind (network interruption, high log generation rate, disk failures on secondary, etc.) and make sure to fix it. It's not worth having an AG secondary that can't sync properly if it causes your log to grow too big on the primary.

pdsQsql · February 2, 2022, 4:14am

Thanks Robert for your response.
First - Shrinking log file is not working, it's throwing an message:

Cannot shrink log file 2 (SQL1_log) because the logical log file located at the end of the file is in use.

Space is limited but I have added almost  40 GB disk space but log is chewing up.
My Both Primary and Secondary Replica having similar Disk configuration.
I am taking log backup every 15 min.
You are right that "It's not worth having an AG secondary that can't sync properly if it causes your log to grow too big on the primary." but somehow it's growing.
Can you tell me how I can check properly 'high log generation rate'?
There is not Disk Failures.

robert_volk · February 2, 2022, 5:26am

Are you doing any index maintenance, like rebuild or reorganize? Those will generally create a lot of log activity and can easily overwhelm an AG secondary. If yes, turn off whatever jobs or schedules that are running index maintenance.

Again, YOU MUST REMOVE THE SECONDARY REPLICA(S) FROM THE AVAILABILITY GROUP. Or at least remove the database from the AG. The replica is preventing the log from clearing properly, and until that replica is removed it will continue to grow. You cannot shrink the log while the replica is trying to synchronize.

pdsQsql · February 2, 2022, 2:33pm

Thanks Robert.
I have already disabled jobs.
What's the exact steps if should follow to "REMOVE THE SECONDARY REPLICA(S) FROM THE AVAILABILITY GROUP"?
I am looking here pre and post steps also if you don't mind, Appreciate your help!

robert_volk · February 2, 2022, 2:42pm

Open the Availability Groups in Object Explorer, expand the Replicas folder. Find the secondary replica(s) in the list, right click and choose "Remove" (can't remember the exact wording). Once that's removed, perform your transaction log backups and run DBCC LOGINFO to see if the VLFs are in status = 0. The perform the SHRINKFILE.

Once you get the log file shrunk, monitor for the next hour or so to see if the log is growing again. If yes, run sp_whoisactive or whatever monitor you have to find out what's causing the log to grow.

If the log is no longer growing and remains stable, you can manually restore the log backups to the replica using WITH NORECOVERY, but do that BEFORE adding the replica back to the AG. Once the replica is added, you can join the database to that replica by right clicking the database on the secondary.

pdsQsql · February 2, 2022, 2:53pm

Thanks Robert for quick response.
I was looking into my Secondary server ==> Always On High Availability ==> Available Groups ==> Sql2_AG (Secondary) ==> Under Availability Replicas ==> SQL2_DR (Secondary) RC ==> I couldn't see remove option

robert_volk · February 2, 2022, 3:05pm

You have to remove the replica from the primary.

Edit: I mean, you have to connect to the primary in order to remove the secondary replica.

pdsQsql · February 2, 2022, 4:24pm

Thanks Robert once again.
In your response to "you can manually restore the log backups to the replica using WITH NORECOVERY,"
Is it How many logs I need to restore into Secondary?
How to check how many logs needs to applied?
Once I applied using "WITH NORECOVERY" option the I will add Replica back to AG, Once the replica is added, join the database?

jeffw8713 · February 2, 2022, 5:28pm

Review the AG configuration - if that is not set to automatic seeding, consider setting it up that way as it will take care of restoring the database to the secondary for you.

You should also consider backing up your transaction log much more frequently. Backing it up every hour is obviously not frequently enough. I would back it up at least every 15 minutes if not more frequently - and have set some databases to every 5 minutes (less during some processing - for example setting it to every minute during index rebuild operations).

With that said - you don't need to remove the database from the AG to shrink the log file. If the secondary is not sychronized - you need to resolve that issue because that is what is preventing the transaction log from being truncated (not shrunk). One way to resolve that is to remove the database from the secondary - but if you can get that database synchronized you could then do the following:

Shrink the transaction log
Backup the transaction log
Repeat until desired size has been reached

In the shrink - specify the size. Do not shrink down to the minimum size as that will just cause it to grow again which will have an impact on system performance.

pdsQsql · February 2, 2022, 8:24pm

Thank you Jeff for your kind response.
I am backing up now Transaction log to every 15 min even though we don't have that much heavy transactions but still no effects, whenever adding disk space it's all chewing up and I can see it's all in Log files, Data file size remain same.
I I add disk space in both the servers (Primary and Secondary) then I am able to resume it and it's synchronizing (Primary already in Synchronized state)

Appreciate further advice!

jeffw8713 · February 2, 2022, 8:53pm

Open the AG Dashboard and review the send/redo queues. See if that is backed up - if those are backed up then the log cannot be truncated and it will continue growing.

If that is the case - then you need to review the network and make sure it is robust enough to handle the amount of traffic you are trying to send.

pdsQsql · February 3, 2022, 12:27am

Jeff, Thanks.
Redo Queue Size, Redo Rate Columns are empty in AG Dashboard.
I can see LSN columns and End of Log LSN and Last Commit LSN ae same number in Primary.
In Secondary Replica, Last sent time, Last Received shows today 12 PM.
Anything else?

pdsQsql · February 3, 2022, 3:07pm

Good Morning!
Appreciate any further feed back, still looking the solution.

Thank you for your help!

jeffw8713 · February 3, 2022, 8:21pm

Something is preventing the transaction log from being truncated - so you need to find out why that is happening.

 Select d.log_reuse_wait_desc From sys.databases d

Can you confirm that you are not using COPY_ONLY for your log backups?

pdsQsql · February 3, 2022, 8:55pm

Jeff,
Select d.log_reuse_wait_desc From sys.databases d ==> AVAILABILITY_REPLICA

Log backup we are taking regualr Log backup and running it to 15 min.

jeffw8713 · February 4, 2022, 3:23pm

So - the log cannot be truncated because the redo queue isn't caught up and the transactions hardened on the secondary. Until that occurs - SQL Server cannot truncate the log and reuse that space.

pdsQsql · February 6, 2022, 7:14pm

Thank You everyone for your time and valuable suggestions which helps me to resolve the issue right now.
I removed Secondary from AG, I did restore FULL and TLog backup from Primary database, restore into Secondary and ran ALTER DATABASE SqlT SET HADR RESUME, Frequent Tlog backup and couple hobs I have doubt which I disable were helped.
I didn't shrink the Log file.