SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Mirroring Strange Behaviour Question


Mirroring Strange Behaviour Question

Author
Message
Kwisatz78
Kwisatz78
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2831 Visits: 1821
Hi all

Last night one of our mirrored databases failed over and then failed back automatically. This seems strange behaviour to me as no other mirrored DB failed over with it. The messages I got in order were:

Database mirroring is inactive for database 'xx'.

Starting up database 'XX' (for all databases but the one in question)

The mirrored database "XX" is changing roles from "PRINCIPAL" to "MIRROR" because the mirroring session or availability group failed over due to role synchronization. This is an informational message only. No user action is required.

Following this was the recovery of all the other databases but this one.


Later on we got a different problem as it looks like we had a network issue and was getting the following:


The mirroring connection to "TCP://xxx.xxx.xxx:5022" has timed out for database "xxx" after 10 seconds without a response. Check the service and network connections.

Database mirroring is inactive for database 'XX'.

The mirrored database "XX" is changing roles from "MIRROR" to "PRINCIPAL" because the mirroring session or availability group failed over due to automatic failover



I am a little confused why this particular database acted different from all the others, and also what caused the initial failover. The secondary failover make more sense as there was a loss of connection to the mirror hence the DB failed back to the Principle.

Thanks for any hints
Perry Whittle
Perry Whittle
SSC Guru
SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)

Group: General Forum Members
Points: 55148 Visits: 17708
what is the timeout value you are using for this database?


select mirroring_connection_timeout from sys.database_mirroring
where database_id = DB_ID('yourdb')



-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" ;-)
Kwisatz78
Kwisatz78
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2831 Visits: 1821
They are all at 10sec, I guess I could increase it, but was really wondering why it happened in this manner in the first place.
Perry Whittle
Perry Whittle
SSC Guru
SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)

Group: General Forum Members
Points: 55148 Visits: 17708
Commonly a network error, what testing did you perform to ensure the default of 10 secs was sufficient to mitigate potential issues on your network?

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" ;-)
Kwisatz78
Kwisatz78
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2831 Visits: 1821
The only testing I did was to monitor the network interfaces for a week or so to see how much they were being utilised, and found that they were ok.

Am open to any suggestions however.

Thanks
Kwisatz78
Kwisatz78
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2831 Visits: 1821
Hi all - This has happened again, I can only assume its a network issue at that time, something thats hard to capture unless I know when its happening.

Its strange that no one on the net has ever seen the exact same errors as me though.
julien.crawford
julien.crawford
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 30
I've been seeing this for quite a while on a hokey test SQLServer VM.
It doesn't happen on the production machines though.

Thanks for being the 1st to raise this :-) I too have had difficultly tracking this down.

I'm going to increase the timeout and see what happens.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search