This Q&A was previously posted on the Stack Exchange Network of websites.
This content is licensed under the Creative Commmons Attribution-Sharealike (CC BY-SA) license. As the author of these answers, this archive serves as a canonical location for permalinks, which will outlive Stack Overflow. See all my answers by looking at the Stack Exchange post category.
Will Transaction Log backup to Nul break Always On Availability Group 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?
asked 2015-08-31 by user3270509
Answer
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 production databases to NUL:, unless you don’t care about point-in-time recovery the data in your database. Usually, if you need high availability, you need somewhat granular recovery. 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.
answered 2015-12-28 by Andy Mallon