Vacuum and Analyze Behavior During Force Failover in HA Database

Question

What should be done if Vacuum and Analyze show as NULL in a High Availability (HA) database after a force failover? PS - Based on common issues that we have seen from customers and other sources, we are posting these questions to help the Azure community

Answer 1

Greeting!

When performing a force failover in a high availability (HA) database, the Vacuum and Analyze operations may appear as NULL in the new primary database. This issue arises because while the vacuum operation is recorded in the Write-Ahead Log (WAL) files and replayed onto replica servers, user statistics are not updated in the same manner.

 

After failover, the standby server (now the primary) might show missing user stats, even though the vacuum processes were applied. These missing stats should not affect the execution plans, but if you require updated statistics for performance tuning, it may be necessary to run a full Vacuum and Analyze on the new primary database.

 

To summarize:

1. Vacuum operations on the primary server are replayed onto the standby server via WAL files.
2. User statistics are not updated on the standby server during failover.
3. Missing user stats do not affect execution plans, but running a full Vacuum and Analyze post-failover can help ensure up-to-date statistics for optimal performance.

 

Please do not forget to  "up-vote" wherever the information provided helps you, as this can be beneficial to other community members.

Regards

Geetha