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

Manohar 0 Reputation points
2023-05-15T04:19:44.25+00:00

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

User's image

  1. 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

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,079 questions
{count} votes

2 answers

Sort by: Most helpful
  1. AniyaTang-MSFT 12,341 Reputation points Microsoft Vendor
    2023-05-15T07:21:49.8733333+00:00

    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

    0 comments No comments

  2. Erland Sommarskog 103.5K Reputation points MVP
    2023-05-15T22:02:47.4833333+00:00

    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.

    0 comments No comments