June 7, 2017 at 6:10 am
In our live environment we had Alwayson configured but for some reason it was causing instability to database and we got lots of timeout expired errors also
Unable to access availability database 'xxxx' because the database replica is not in the PRIMARY or SECONDARY role. Connections to an availability database is permitted only when the database replica is in the PRIMARY or SECONDARY role. Try the operation again later
The DB admin stopped synchronization and errors are gone but somehow today all of a sudden the database is unreachable we can't connect to it.
He did a restart and db turned into recovery pending mode.
I tried to check error logs on sql and found the following error
Unfortunately i don't have now access to windows error logs but if needed i will try to get them.
Unfortunately we had to restart from the last differential backup and lost about one hour of live work but we really need to know why this problem happened and what should have we done to fix it we had to make restore as there was no time and live system was down
The SQL Server Network Interface library could not register the Service Principal
Name (SPN) [ MSSQLSvc/xxx-xx.xxx.xx ] for the SQL Server service. Windows return code: 0xffffffff, state: 43.
Failure to register a SPN might cause integrated authentication to use NTLM instead of Kerberos
Also when i ran command to bring database online i got the following error.
Msg 5181, Level 16, State 5, Line 1
Could not restart database "xxxx". Reverting to the previous status.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
Msg 35262, Level 17, State 1, Line 1
Skipping the default startup of database xxxx' because the database belongs to an availability group (Group ID: 65537). The database will be started by the availability group. This is an informational message only. No user action is required.
The old database(recovery pending) is still in that state, don't know what should we do about it.
Regards
Nader
June 7, 2017 at 7:21 am
have you tried removing the secondary database from the availability group and re initialse it
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
June 8, 2017 at 11:47 pm
This was removed by the editor as SPAM
June 11, 2017 at 12:32 am
Thank you i already restored the last differential backup but wanted to know the reason for problem.
The old problematic database is still in recovery pending mode we left it like that to try to find out reason for issue.
Regards
Nader
June 11, 2017 at 5:04 am
nadersam - Sunday, June 11, 2017 12:32 AMThank you i already restored the last differential backup but wanted to know the reason for problem.
Can you answer Perry's question?
Perry Whittle - Wednesday, June 7, 2017 7:21 AMhave you tried removing the secondary database from the availability group and re initialse it
Perry's the AG expert around here.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 11, 2017 at 5:38 am
Thanks for you reply Gail
The problem is i am not a DB Admin but my company relies on me on matters related to performance tuning, the Alwayson setup was not done by me and i am not very much aware of it so i was afraid to do anything that could cause any other problems on live database server.
Will doing that have any consequences knowing that we restored the last backup of db with a new name and removed it from Alwayson.
Regards
Nader
June 11, 2017 at 8:37 pm
Hi Nadersam,
You also have to check the Windows Server Failover cluster. Is your Cluster core group resources online or offline?
If it is offline, you have to bring it online.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply