Ensuring your log files are in good shape when using SQL Server with AlwaysOn Availability Groups is essential and should be an integral part of your database deployment & management strategy. Since AlwaysOn requires full recovery model to be enabled prior to configuration, your logs must be backed up regularly in addition to the database itself.
If you’re reading this, it’s more than likely you’re trying to find an easy way to clear down your growing log files. I’d recommend taking a little time to think about why it’s happening in the first place before you do anything else. Perhaps your logs aren’t backing up properly, your preferred backup replica is configured incorrectly or one of your secondary replicas is in error or playing up, Whatever the reason, diagnose it before going any further using the steps below.
The best place to start is by examining the state of your transaction log files in order to determine the reason that your logs are filling up and space isn’t being cleared. You can do this by issuing dbcc loginfo against the database with issues then cross reference the reason ID(s) against Microsoft’s Factors That Can Delay Log Truncation list (from sys.databases).
So you’re not using HADR?
If you haven’t implemented a high availability solution such as AlwaysOn or database mirroring, a temporary fix (if your RPO allows) is to change your data recovery model to Simple in order to avoid backing up transaction logs at all. This isn’t recommended for any production environment where data loss is a concern. Again, do not attempt to change this if your database is part of an AlwaysOn Availability Group.
And if you are?
OK, so we’ve already established that why our logs aren’t backing up by running dbcc loginfo. Let’s run the following in SSMS against all the databases causing you problems so we know what we’re working with prior to attempting to resolve the issue
USE [yourDatabase]
DBCC SQLPERF(LOGSPACE)
If your logs are massive and growing *and* you’re taking regular backups, it’s worth checking that the SQL Server instance you’re taking backups on is set as the preferred backup replica in SQL Server. An incorrect setting in here can often delay the truncation of transaction logs.
Let’s free up some space
Ok, now we know which databases (and logs) are causing the problems, we can start to try and shrink them. If the results of your dbcc loginfo show that a replication issue is a potential problem, check the health of your availability group via the dashboard to ensure your replicas are in good shape. If not, you could remove the offending servers from the group until you’ve resolved the issue.
Next, take a backup of your log files. If space isn’t a concern, back these up to a physical drive. If you’re having issues with space (which is more than likely if you’re reading this), dump it to NULL. Please do this at your own risk as NUL is a special place in the file system. It’s the nul device, the outside bin or black hole of the file system.
BACKUP LOG [yourDatabase] TO DISK=‘NUL:’
Next, if required, let’s start to shrink your files. Again, run these at your own risk and ensure you have a full backup in place beforehand:
DBCC SHRINKFILE (yourDatabase_Log, EMPTYFILE);
Finally, get your backups back to normal and then go and explore what was *actually* causing your issue in the first place.
A good place to start is to ensure your backup solution is taking regular full backups, as well as frequent differentials & transaction logs.
Good luck!