SQL Server AlwaysOn – Alert database Not Synchronizing / Suspect

Few days ago, through our alerting at the Service Desk, we receive an alert about a database in an AlwaysOn cluster with the message:

CRITICAL: Database xxxx [replica: xxxxxx] [role: SECONDARY] state is SUSPECT, synchronization health is NOT_HEALTHY, synchronization state is NOT SYNCHRONIZING [suspended: true] [failover ready: false] [joined: true]

After creating the ticket, I go to the environment and see with SSMS that on the secondary, the database is in the state Not Synchronizing / Suspect…

Like usual, I go to the errorlog and see this error:

It seems that we have a file on a share which is not accessible, and which is putting the database in a suspicious state. If you look at the database on the primary, you can see that someone has created a data file on a share…

SQL Server has allowed you to create files on shares for years.

When you’re in an HA system with AlwaysOn AG and with automatic seeding(the problem coming from this point), adding a file on the primary will be replicated on the secondary and so in our case, it will be necessary to create the file on the same share with the same name, which isn’t possible.

The second consequence is that the Tlog file doesn’t get emptied and continues to grow despite the backup logs, because the pending synchronisation anticipates the commit… until the big alert that the Tlog disk is full…

The moral of the story is simple: before making a structural change to a database, think about how it works and the consequences. 

In any case, we’ll always be here to help! 😉