We are in SQL Server 2014 and using Always on Availability Group and all I got was ‘Disk is FULL and one log file has consumed entire disk space’. Where ever you go, log file full issues follow J .
The below resolution steps have helped me to fix the log file full issue in my environment. This may not help in every situation and for every environment. Please do proper testing on your test servers before using the same method in production environments. Shrinking the log will have a direct effect on your Index performance and point in time recoveries so please DO NOT follow below steps without knowing their complete impact.
Basic points to remember while using Always on Availability Group:
1. We cannot change the recovery model of database involved in AAG. AS AAG works on FULL recovery only.
2. We need to shrink the log file of the database of the AAG Primary node only.
Shrinking of the log file is not recommended at all because until the root cause has been figured and fixed the issue keeps recurring. In my case also as an immediate resolution, the log file has been shrunk.
First, identify what is causing the log file not to be re-used. It was ‘LOG_BACKUP’ in my case.
Select log_reuse_wait_desc,* from sys.databases
You can use DBCC LOGINFO as well and if you see the status as ‘2’ then the virtual log files cannot be re-used. Means we cannot shrink the log.
I took log backup of the database on the secondary node where usually backup jobs are configured. If you have backups running on primary perform the log backup in primary node.
BACKUP LOG <DATABASE NAME> TO DISK=’D:\BACKUPS\LOG\’
Once log backup completes, shrink the log file:
DBCC SHRINKFILE(FILENAME,SIZE(MB))
Now the file got shrunk.
Make sure regular backups are happening for the database especially when involved in AAG. In my scenario, the log backups were running once in a day and obviously, that has been the issue.
Please share how you have fixed the log file full issue while using Always On Availability Group in the comments section below.
Thanks VV!!
#Log file full, #Always On Availability log file full, Always On Availability log file
