Unable to shrink transaction log - logical log file located at the end of the file is in use
-
Situation: Database is the active member of a two-database availability group. Underlying MSFC had a long-term, undiagnosed issue and the transaction log of the database grew to a significant size and is now in danger of filling up the drive.
Action: I have tried shrinking the transaction log (both TRUNCATEONLY and reorganise to a specific size) but both report:
- Cannot shrink log file 2 (log) because the logical log file located at the end of the file is in use.
Using sp_who2 I cannot see any sessions in the database, DBCC OPENTRAN reports no open transactions but sys.dm_tran_active_transactions reports:
The transaction_begin_time lines up with the start time of the instance, which I restarted to see if that would solve the problem.
I can't use SIMPLE recovery mode since this database forms part of an availability group. I don't want to remove the database from the availability group (although I will if that's the only option available to me).
Can anyone advise how I can shrink the transaction log before the drive fills up?
Thanks.
-
RESOLVED:
It turned out that the availability group was the issue. Looking at sys.databases I saw that the log_reuse_desc was AVAILABILITY GROUP so it was replication that was preventing the log from being freed.
I tried to resume data movement but it reported that the secondary database was SUSPECT. So:
- remove the database from the availability group
- shrink the transaction log
- backup the primary database and transaction log
- restore the database and transaction log backups onto the secondary instance with NORECOVERY
- re-add the database back into the availability group
Sorted.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply