In SQL Server Always ON configuration - will Transaction Log backup to Nul breaks Always On configuration?

Question

Imagine we have two nodes participating in SQL 2012 AO. This is a test instance. During one of the index rebuild operation the log was grown up really big (250 GB). We are unable to back it up due to space constraint. What if we backup the Tlog to Nul (just to shrink it down) – will that break Always On?

Answer 1

AlwaysOn is a (marketing) umbrella term that covers both Availability Groups (AGs) and Failover Cluster Instances (FCIs). From context, I assume you are asking about AGs?

For both FCIs and AGs, the short answer is the same: performing transaction log backups (regardless of the destination) will not "break" your HA capabilities. However, I would urge you to NEVER EVER back up to NUL:, unless you don't care about the data in your database. Taking a log backup to NUL: (Regardless of if you were using an AG, FCI, or neither) will break your log backup chain, and prevent point-in-time recovery.

If you are using an Availability Group, SQL Server does not use transaction log backups to synchronize between nodes. It uses the transaction log itself, and therefore will not clear the transaction log if there is log data that needs to be synchronized to another node. That is to say: if your AG synchronization is behind, your transaction log will continue to fill/grow until synchronization catches up, regardless of the number of transaction log backups performed.

There are multiple reasons your transaction log might continue to grow, and AG synchronization is just one of those reasons. If SQL Server cannot reuse the transaction log because of unsynchronized transactions in the AG, the log_reuse_wait_desc column in sys.databases will show the value "AVAILABILITY_REPLICA".

Getting back to your root problem: Rebuilding an index made your transaction log get really, really big.

When you perform an ALTER INDEX...REBUILD, SQL Server creates the entire new index (a size-of-data operation), and must be able to roll back the index creation if it errors or is killed prior to completion. Therefore, you may see the log_reuse_wait_desc column in sys.databases showing as "ACTIVE_TRANSACTION" during a very large, long-running index rebuild. The rebuild itself would prevent you from reusing the log, and would cause the log to grow.