SQL Server AlwaysOn AG automatic initialization (13)

other things you need to know.

• Transaction log cannot be truncated during automatic seeding

Automatic seed setting delay log truncation. If the database load on the primary replica is high, a significant number of logs are generated, and there may be a risk of filling up the log files. Of course, most of the time, log files are set to autogrow, there is no set limit, and you want to monitor the usage of the disk space where the log files are located during automatic seeding.

The Log_reuse_wait_desc column of the sys.databases view reflects the reason for deferred log truncation. However, when a transaction truncation is delayed due to automatic seeding, the value of the Log_reuse_wait_desc column is "Nothing", which means that log truncation is not prevented, and the log is actually truncated when it executes, which is misleading.

• is_compression_enabled column values in the Sys.dm_hadr_physical_seeding_stats table are incorrect when compression is enabled

The Sys.dm_hadr_physical_seeding_stats view shows the status of the currently automatic seeding activity. The is_compression_enabled column of the view reflects the value in automatic seeding when using trace flag 9567 to start compression or not to use compression. Then, when compression is enabled, the value is 0, which is incorrect.

• To remove all databases for the secondary replica before recreating the automatic seeding availability Group

When you recreate an automatic seeding availability group, if the secondary replica's database is not deleted, the primary replica tries to connect to the secondary replica first, if the primary replica does not recover and does not make a log backup, connects to the secondary replica, synchronizes the operation of the primary replica, automatic seeding is complete, and if the primary replica has been re-restored or a log backup Will not connect to the secondary replica. The test scenario is as follows:

The primary replica deletes the table, the secondary replica connects to the secondary replica, and the delete operation is synchronized;

Primary replica Delete table, backup log backup, secondary replica synchronization failed, query sys.dm_hadr_automatic_seeding, get 223 error: Database with Name already Exists.

SQL Server AlwaysOn AG automatic initialization (13)