Why Are My Mirrored Databases Changing States on Their Own? Setup:


SQL Server 2005 w/SP4 64-bit Standard for all three servers
Windows Server 2008 R2 Enterprise w/SP1 64-bit for all three servers
01 is designated as the Principal
02 is designated as the Mirror
03 is designated as the Witness


My issue is that there are Principal and Mirrored DBs on both servers and it is causing Maintenance Plans and SQL scripts to fail.

My questions:


Why are the DBs failing from one to the other when 01 should have all Principal DBs?
How do I stop this behavior
What is the least invasive process of changing the Principal DBs on 02 back to 01?
Can you please look at SQL Server errorlog and see if you could find some relevant information on both Principal and mirror. Generally if SQL Server CPU utilization goes high above 50 % there can be failover. This is specifically caused by heavy load on SQL Server this is documented in Below Microsoft Web resource See 'recommendation for Configuring partner Server' in link : http://msdn.microsoft.com/en-us/library/ms366349%28v=sql.100%29.aspx

This can be avoided by Alter partner time out


  Alter database db_name set partner timeout 20


Edit: After User provided more details


  Database mirroring connection error 4 'An error occurred while receiving data: '64(The specified network name is no longer available.)'.


As per this message its clear that network connecting principal and mirror flipped I dont know for how much time but this caused the failover. What happens is Mirror server keeps sending pings to Principal whether it is alive or not and suppose Principal goes down and now mirror will send 10 pings to principal(By default) and if it does not gets any response it will initiate failover with help of Witness( its little more complex with witness ,it involves quorum)

If it was for moment you can increase partner timeout( not a good practice though) but you should Immediately speak to network team and ask them RCA for this glitch as this caused failover. My course of action would be to meet them personally ask them to run bunch of commands to check response time