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?