0

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 and we are out of disk space. We have Primary is still Synchronized. We are running log backup every 15 minutes on Primary. 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"

  1. DBCC LOGINFO Shows lot of almost (1000+) VLF files with Status = 2

  2. DBCC SQLPERF(LOGSPACE) enter image description here

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

Thank you in Advance for your help!

2
  • Is your secondary replica in Asynchronous-Commit Availability Mode? Have you checked the Availability Group Dashboard to see if your secondary is far out of sync from the primary? Sounds like that could be part of your issue possibly.
    – J.D.
    Feb 2, 2022 at 19:58
  • Thank you J.D.I have Synchronous commit. Currently Secondary is Not Synchronizing as Disk space issue as after adding disk space it's runs for a while and then disk space is going out as Log is growing with lots of VLFs I can see.
    – pds
    Feb 2, 2022 at 20:41

1 Answer 1

0

At first make sure that you have enough space for log on secondary (should be the same like on primary)

After that you can run below query and take a look on last column 'replication_behind_minuts' it should be near 0 and also check redo_queue_size this don't need to be 0 but should be low number.

select 
   rcs.replica_server_name as [Replica], 
   ars.role_desc as [Role],
   db_name(rc.database_id) as Dbname,
   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
order by rc.redo_queue_size desc 

After the numbers are ok check the

SELECT name, log_reuse_wait_desc FROM sys.databases

It should show 'nothing' or 'log_backup' and all should be good. If you still see 'AVAILABILITY_REPLICA' it means that SQL still applying logs to the log file (probable replica is still behind) You need to make sure that you have enough space to get all logs from primary.

7
  • Thanks Marcin! I checked and it shows null.
    – pds
    Feb 3, 2022 at 15:16
  • Please see the following result from the query: Replica Role Dbname Sync_state Health_State last_sent_time last_received_time last_hardened_time last_redone_time last_commit_time redo_queue_size Replication_behind_minuts TSQLDR SECONDARY TSQL1 NOT SYNCHRONIZING NOT_HEALTHY 2/2/22 12:36 PM 2/2/22 12:36 PM 2/2/22 12:36 PM 2/2/22 9:20 AM NULL 0 NULL TSQL1 PRIMARY TSQL1 SYNCHRONIZED HEALTHY NULL NULL NULL NULL 2/3/22 9:10 AM NULL NULL
    – pds
    Feb 3, 2022 at 15:17
  • 'replication_behind_minuts' Shows 'Null', redo_queue_size shows '0'
    – pds
    Feb 3, 2022 at 15:25
  • Did you run this on primary? It should be executed on primary and you need to take a look on information about the secondary server. Is it showing not synchronizing and not healthy? Do you have free space on disk for log file?
    – Marcin
    Feb 3, 2022 at 16:24
  • Thanks Marcin. I ran on Primary and i also posted results for secondary - replication_behind_minuts' Shows 'Null', redo_queue_size shows '0'. We are running out of disk space whenever we add the extra disk space due to rowing logs and not catching up/syncing from primary to secondary.
    – pds
    Feb 3, 2022 at 17:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.