Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Mirroring Strange Behaviour Question Expand / Collapse
Author
Message
Posted Tuesday, November 20, 2012 1:50 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 4:46 AM
Points: 598, Visits: 1,626
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
Post #1386728
Posted Tuesday, November 20, 2012 4:16 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:13 PM
Points: 6,466, Visits: 13,919
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"
Post #1386811
Posted Tuesday, November 20, 2012 4:21 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 4:46 AM
Points: 598, Visits: 1,626
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.
Post #1386817
Posted Tuesday, November 20, 2012 4:37 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:13 PM
Points: 6,466, Visits: 13,919
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"
Post #1386831
Posted Tuesday, November 20, 2012 5:00 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 4:46 AM
Points: 598, Visits: 1,626
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
Post #1386847
Posted Tuesday, February 26, 2013 1:46 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 4:46 AM
Points: 598, Visits: 1,626
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.
Post #1423922
Posted Sunday, June 16, 2013 10:39 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, July 29, 2013 12:02 AM
Points: 1, 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.
Post #1463990
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse