The database transaction log file increasing for the DBs Participating In AlwaysOn Availability Groups

Issue

After Migrating High Available SQL Server 2012 Cluster Instance to SQL Server 2014  AlWays on Availability Group  transaction Log file keep growing and within couple of weeks almost the hard disk is full. Earlier My Db was in Simple recovery mode , I changed it to Full recovery mode to add it on Availability Group because of that the transaction log keeps increasing

Solution

It is  a very common problem the setting database recovery mode to FULL and then forgetting to backup the transaction log (LDF file). Let me explain how to fix it.

If you ready loose  a some data between backups, just set the database recovery mode to SIMPLE, then forget about LDF - it will be small. the solution for most of the cases. But for SIMPLE Recovery mode will not supporting by Availability group. In this case you have to take Transaction Log backups.
Recommended to have  a full backup per day. If you do a full backup per day, The frequency of the log backup will determine how much data you are allowed to lose in case of a failure. If you run your log backup every 15 minutes, expect to loose up to the last 15 minutes of data that changed. 15 minutes was good frequency for my environment.


AlwaysOn Availability Groups allows the offloading backups to a secondary replica. If you setup a log backup on secondary replica it will be truncating both primary and secondary logs. no need to have it on both nodes.

Where should backups occur? select the automated backup preference for the availability group, one of:

Prefer Secondary 

Specifies that backups should occur on a secondary replica except when the primary replica is the only replica online. In that case, the backup should occur on the primary replica. This is the default option.

Secondary only

Specifies that backups should never be performed on the primary replica. If the primary replica is the only replica online, the backup should not occur.

Primary

Specifies that the backups should always occur on the primary replica. This option is useful if you need backup features, such as creating differential backups, that are not supported when backup is run on a secondary replica.

Any Replica

Specifies that you prefer that backup jobs ignore the role of the availability replicas when choosing the replica to perform backups. Note backup jobs might evaluate other factors such as backup priority of each availability replica in combination with its operational state and connected state.

To take the automated backup preference into account for a given availability group, on each server instance that hosts an availability replica whose backup priority is greater than zero (>0), you need to script backup jobs for the databases in the availability group. To determine whether the current replica is the preferred backup replica, use the sys.fn_hadr_backup_is_preferred_replica function in your backup script. If the availability replica that is hosted by the current server instance is the preferred replica for backups, this function returns 1. If not, the function returns 0. By running a simple script on each availability replica that queries this function, you can determine which replica should run a given backup job.

If you use the Maintenance Plan Wizard to create a given backup job, the job will automatically include the scripting logic that calls and checks the sys.fn_hadr_backup_is_preferred_replica function. However, the backup job will not return the “This is not the preferred replica…” message. Be sure to create the job(s) for each availability database on every server instance that hosts an availability replica for the availability group.
: