Sql-server – Applying (log backup) LSNs to the secondary node of an Availability Group

If the secondary is up and running, when the log block is flushed to disk (either because it is full or a commit), the record gets pushed to the log writer on the primary and to the log scanner (log reader) process on the primary simultaneously. Then the log scanner communicates with the secondary and the secondary then pulls the transaction from the log scanner on the primary to the secondary and processes the log record. The primary log writer doesn't push transactions across, it just communicates with the secondary, it only does that to see if it is up so that it knows it doesn't have to mark the replica as NOT SYNCRONIZED.

When the secondary is not up, then the log writer cant communicate with the secondary so it marks it as NOT SYNCHRONIZED and stores the records in the tran log on the primary. If you look at sys.databases.log_reuse_wait_desc column it should show AVAILABILITY_REPLICA which means the primary is hanging on to all the records.

Once the secondary is up, it will communicate with the primary to request a log scan, it then processes the transactions and communicates with the primary using progress messages to indicate the hardened LSN, presumably the primary is then adjusting its MinLSN, which in turn means the records prior to MinLSN will get deleted as checkpoints happen and hence VLFs will get truncated releasing space when you do a log backup.

But yes short answer is, if your secondary is down you need as big a log file as you need for as long as it is down. Once it is backup and synched at some time you may need to remove the db from the always on group to shrink the log if it is humungous and you dont want it that big.

If I understand you correctly, your major problem is the log files during the several hours of backup. From your opening statement I understand that the 1.5 TB database normally runs in SIMPLE recovery, and thus no log backups to do.

Disclaimer: I have never done log shipping on this scale.

Of course, you should ask whether you can get more space allocated for your log files. If you can, then great.

However, I think a small modification of your plan, providing that you already run in SIMPLE recovery model and/or the risk of SIMPLE recovery model for a few hours is worth it, would ease some of your worries.

1. Keep (or set) DB in SIMPLE recovery model.
2. Take FULL backup (5-6 hours) on primary
3. Restore FULL backup to secondary (leaving in NORECOVERY)
4. Set DB in FULL recovery model
5. Take DIFF backup on primary
6. Restore DIFF backup to secondary (still in NORECOVERY)
7. Initialize log shipping using 'The database is already initialized'

The apparent advantages are:

1. No log files to backup during the large FULL backup.
2. Switching to FULL before beginning the DIFF backup will give you the needed log to start with and its longest growth is probably during the DIFF backup.

Regarding when a log backup can start:

https://technet.microsoft.com/en-US/library/ms190729(v=SQL.105).aspx

This says: "A new log chain starts either with the first full database backup following database creation or after switching from the simple recovery model to the full or bulk-logged recovery model."

So, I still believe that this will work as outlined. (Not identical, but I have used a differential backup to cover a gap when log files were lost, so as to establish a new origin for the log backups.)

(Remember my disclaimer, of course.)