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

Question

hi there,

We have Sql Server 2017 and set up Always ON with 2nd replica (Primary and Secondary)
During data deletion from a database, its log filled up the disc space. And looks like the deletion is not completely done yet, as there are still some data in the table that we were trying to delete.

When I try to shrink the log, I get the 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 are showing log__reuse_wait___desc = "NOTHING"_ or "LOG_BACKUP" except for the DB is showing "AVAILABILITY_REPLICA"

1. DBCC LOGINFO
   Shows lot of almost (1000+) VLF files with Status = 2
2. when I run this query

select 
  
   ars.role_desc as [Role],
   rc.synchronization_state_desc as [Sync_state],
   rc.synchronization_health_desc as [Health_State],
   rc.last_sent_time,
   rc.last_received_time,
   rc.last_hardened_time,
   rc.last_redone_time,
   rc.last_commit_time,
   rc.redo_queue_size,
   DATEDIFF(minute,rc.last_commit_time,rc.last_hardened_time) as Replication_behind_minuts
from sys.dm_hadr_database_replica_states as rc
join sys.dm_hadr_availability_replica_cluster_states as rcs on rcs.replica_id = rc.replica_id
join sys.dm_hadr_availability_replica_states as ars on ars.replica_id = rc.replica_id
where db_name(rc.database_id) = 'SPID_DG'

order by rc.redo_queue_size desc 

I get following

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

any pointers on how to get around this?

Thank you

Answer 1

Hi @Manohar

I found a thread similar to yours, maybe you can use it as a reference.

https://dba.stackexchange.com/questions/306933/shrink-log-file-in-always-on-db

Best regards,

Aniya

Answer 2

As long as the DELETE operation is running, the log cannot be truncated, since the transaction cannot be truncated past the oldest open transaction.

The best solution in this case is probably to find another disk, and add a second log file on this disk. If the DELETE operation has already crashed, you should be able to back up the transaction log and truncate it. Or, well, that NOT SYNCHRONIZING does not bode well. Whether you should truncate the log file depends. Do you want to use that disk space for some other files?

Restarting the DELETE operation in its current form would be a folly, It would have be rewritten to delete data in reasonably-sized chunks. Five millions rows at a time is a good value, as long as there are no LOB columns.