Log backup failed on Always On configured database.
Question
Saturday, August 26, 2017 4:09 AM
Log backup failed.
Configured to take on secondary replica.
Msg 3062, Level 16, State 1, Server H, Line 1
Cannot backup from a HADRON secondary because it is not in Synchronizing or Synchronized state.
Msg 3013, Level 16, State 1, Server H, Line 1
BACKUP LOG is terminating abnormally.
Error log details:
AlwaysOn: The local replica of availability group 'XY' is going offline because the corresponding resource in the Windows Server Failover Clustering (WSFC) cluster is no longer online. This is an informational message only. No user action is required.
The state of the local availability replica in availability group 'XY' has changed from 'SECONDARY_NORMAL' to 'RESOLVING_NORMAL'. The state changed because the availability group state has changed in Windows Server Failover Clustering (WSFC). For more information, see the SQL Server error log, Windows Server Failover Clustering (WSFC) management console, or WSFC log.
AlwaysOn Availability Groups connection with primary database terminated for secondary database 'SALE' on the availability replica 'B' with Replica ID: {46b1e7f6-4b96-4656-a1eb-47a0f994a560}. This is an informational message only. No user action is required.
AlwaysOn Availability Groups connection with primary database terminated for secondary database 'ORDER' on the availability replica 'B' with Replica ID: {46b1e7f6-4b96-4656-a1eb-47a0f994a560}. This is an informational message only. No user action is required.
The availability group database "SALE" is changing roles from "SECONDARY" to "RESOLVING" because the mirroring session or availability group failed over due to role synchronization. This is an informational message only. No user action is required.
The availability group database "ORDER" is changing roles from "SECONDARY" to "RESOLVING" because the mirroring session or availability group failed over due to role synchronization. This is an informational message only. No user action is required.
State information for database 'SALE' - Hardended Lsn: '(1131:210693:1)' Commit LSN: '(1131:210680:2)' Commit Time: 'G '
State information for database 'ORDER' - Hardended Lsn: '(1817:40994:1)' Commit LSN: '(1817:40989:2)' Commit Time: 'V '
The state of the local availability replica in availability group 'XY' has changed from 'RESOLVING_NORMAL' to 'SECONDARY_NORMAL'. The state changed because the availability group state has changed in Windows Server Failover Clustering (WSFC). For more information, see the SQL Server error log, Windows Server Failover Clustering (WSFC) management console, or WSFC log.
The availability group database "SALE" is changing roles from "RESOLVING" to "SECONDARY" because the mirroring session or availability group failed over due to role synchronization. This is an informational message only. No user action is required.
The availability group database "ORDER" is changing roles from "RESOLVING" to "SECONDARY" because the mirroring session or availability group failed over due to role synchronization. This is an informational message only. No user action is required.
State information for database 'SALE' - Hardended Lsn: '(1131:210693:1)' Commit LSN: '(1131:210680:2)' Commit Time: 'S '
State information for database 'ORDER' - Hardended Lsn: '(1817:40994:1)' Commit LSN: '(1817:40989:2)' Commit Time: 'G '
A connection for availability group 'XY' from availability replica 'A' with id [DF7C99E6-5D02-4B9C-A7AA-F523A7EEAB89] to 'B' with id [46B1E7F6-4B96-4656-A1EB-47A0F994A560] has been successfully established. This is an informational message only. No user action is required.
**After this event log backups are failing. **
All replies (10)
Saturday, August 26, 2017 7:11 AM
Refer following article where 2 method are explain.
http://panigorthi.blogspot.in/2013/09/msg-3062-level-16-state-1-line-1-cannot.html
Saturday, August 26, 2017 8:12 AM
Data moment is not suspended.
This article wasn't useful here (In my scenario).
Saturday, August 26, 2017 10:24 AM
Hi As per the error message., the database is suspended it seems, can you please the status from dash board and share the pic for us
Msg 3062, Level 16, State 1, Server H, Line 1
Cannot backup from a HADRON secondary because it is not in Synchronizing or Synchronized state.
Msg 3013, Level 16, State 1, Server H, Line 1
BACKUP LOG is terminating abnormally.
Thanks, Satish Kumar. Please mark as this post as answered if my anser helps you to resolves your issue :)
Monday, August 28, 2017 5:33 PM
The reason here as I see is
AlwaysOn: The local replica of availability group 'XY' is going offline because the corresponding resource in the Windows Server Failover Clustering (WSFC) cluster is no longer online. This is an informational message only. No user action is required.
The state of the local availability replica in availability group 'XY' has changed from 'SECONDARY_NORMAL' to 'RESOLVING_NORMAL'. The state changed because the availability group state has changed in Windows Server Failover Clustering (WSFC). For more information, see the SQL Server error log, Windows Server Failover Clustering (WSFC) management console, or WSFC log
Can you check WSFC GUI and see all resources are online.
Cheers,
Shashank
Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it
Monday, August 28, 2017 6:33 PM
You had a failover to the other node. This is normal and expected behavior.
Please read this to setup your backups correctly for AG:
Tuesday, August 29, 2017 6:51 AM
I believe Tom is correct here this may be normal failover, I took the logs more seriously as recently I got such issue where WSFC was down. I believe you need to manually run log backups and see what error is coming, what is your backup preference ?
Cheers,
Shashank
Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it
Wednesday, August 30, 2017 3:57 AM
Yes Always On status for the database is SUSPENDED.
Wednesday, August 30, 2017 3:57 AM
Yes the resources are online.
Wednesday, August 30, 2017 3:58 AM
Backup Preference is on Secondary.
Wednesday, August 30, 2017 5:43 AM
Create job category/categories for all jobs you want to sync
Put all the jobs in them. You may want to note that if you manually update the category on a bunch of jobs by manually updating the sysjobs table that you have to restart the agent otherwise it will throw an error if you try and reopen the job.
The stored proc loops through all of these jobs using the following logic:
If primary replica and disabled, enable it
If primary replica and enabled, do nothing
If secondary replica and enabled, disable it
If secondary replica and disabled, do nothing
CREATE procedure [dbo].[uspDBAHADRAgentJobFailover] (@agname varchar(200))
as
begin
declare @is_primary_replicate bit
declare @job_name sysname
declare @job_enabled bit
select @is_primary_replicate=db.dbo.fn_hadr_group_is_primary(@agname)
declare job_cursor cursor for
select s.name from msdb.dbo.sysjobs s
inner join msdb.dbo.syscategories c on s.category_id = c.category_id
where c.name = @agname
order by name
open job_cursor
fetch next from job_cursor into @job_name
while @@fetch_status = 0
begin
select @job_enabled=enabled from msdb.dbo.sysjobs where name = @job_name
if @is_primary_replicate = 1
begin
if @job_enabled = 1
print @job_name+' enabled on primary. do nothing'
else
begin
print @job_name+' disabled on primary. enable it !'
exec msdb.dbo.sp_update_job @job_name = @job_name,@enabled = 1
end
end
else if (@is_primary_replicate = 0)
begin
if @job_enabled = 1
begin
print @job_name+' enabled on secondary. disable it !'
exec msdb.dbo.sp_update_job @job_name = @job_name,@enabled = 0
end
else
print @job_name+' disabled on secondary. do nothing'
end
fetch next from job_cursor into @job_name
end
close job_cursor
deallocate job_cursor
end
GO
Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/
MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence