microsoft sql server error 1412 always on

This article guides to resolve,

Table of Contents

Why we receive SQL Server Error Message 1412 alwayson in sql server?

Sometime database’s part HA stopped synchronizing. When we try to resume data movement we get

SQL Server Error Msg 1412 or microsoft sql server error 1412 always on – The remote copy of database “DB Name” has not been rolled forward to a point in time that is encompassed in the local copy of the database log.

There are many reasons caused this issue for alwayson in sql server. In most cases this error occurred because of Log backup or availability replica.

You can check this with below query:

select name, log_reuse_wait_desc from sys.databases

You will receive output like this.

If synchronization is pending in above query because of availability replica databases on secondary server in always on will be SUSPECT or Pending Recovery State.

To check last synchronization status and current synchronization status use below queries.

If Synchronization is failed it will show that in below query output.

USE [master];
SELECT TOP 100 start_time, completion_time, is_source, current_state, failure_state, failure_state_desc, error_code FROM
sys.dm_hadr_automatic_seeding ORDER BY start_time DESC.

Below query can provide output of synchronization process and amount of data need to be transferred and amount of data transferred already.

USE [master];
SELECT * FROM sys.dm_hadr_physical_seeding_stats;

You can check below column name for validation of data.
transferred_size_bytes : Total data size transferred

database_size_bytes : Total Data size need to be transferred

Always on troubleshooting in SQL server:

If synchronization is pending in above query because of availability replica databases on secondary server in always on will be SUSPECT or Pending Recovery State.

This may occur due to low disk space issue.

Follow below steps to add database back again in HA.

1.Remove database from availability group.

2.Stop transaction log backup if you are going to add database in HA using    Automatic seeding. It also helps if SQL database size is huge, and your t log backup schedule is 10 min or 15 min. This will cause restore problem in T log backup.

3.Shrink log file if required.

4.Remove database from secondary server. This process will take time if database size is huge. Database recovery will be completed till the time you can drop database. Check progress in SQL server error logs.

5.Once database drop you can add database in Availability Group from SSMS availability wizard.

6.Large database will take time to get into synchronized state. Verify status using below query

USE [master];
SELECT * FROM sys.dm_hadr_physical_seeding_stats;

7.Once database added in HA and statue is synchronized you can enable log backup

microsoft sql server error 1412 always on | Always on Error – The remote copy of database “DB Name” has not been rolled forward to a point in time that is encompassed in the local copy of the database log.