Database Mirroring timeout setting

Question

Post reply

Database Mirroring timeout setting

Ryan007

SSCrazy Eights

Points: 8275
More actions
October 20, 2011 at 3:11 am

#249686

Hi, I have setup database mirroring with witness server. To prevent unnecessary failover because of network slow or other issue, I changed the timeout setting as
ALTER DATABASE <Database Name>
SET PARTNER TIMEOUT 120
Which I understand if connection is broken between principal and mirror, principal database will wait for 120 second and after that only automatic failover will happen. Is that true what I understand?
If this is true, it does not happen in my case. Failover happens before120 second. Please let me know if anybody have idea on this.
Ryan
//All our dreams can come true, if we have the courage to pursue them//

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

derek.colley SSCrazy Eights Points: 8040 More actions · Answer 1

That's right, yes. From Microsoft:

TIMEOUT integer

Specifies the time-out period in seconds. The time-out period is the maximum time that a server instance waits to receive a PING message from another instance in the mirroring session before considering that other instance to be disconnected.

You can specify the TIMEOUT option only on the principal server. If you do not specify this option, by default, the time period is 10 seconds. If you specify 5 or greater, the time-out period is set to the specified number of seconds. If you specify a time-out value of 0 to 4 seconds, the time-out period is automatically set to 5 seconds.

Important

We recommend that you keep the time-out period at 10 seconds or greater. Setting the value to less than 10 seconds creates the possibility of a heavily loaded system missing PINGs and declaring a false failure.

For more information, see Possible Failures During Database Mirroring.

When you say it fails over before 120 seconds, how soon before? What's the estimated period of time and under what circumstances does it occur - i.e. how are you deliberately testing it - cutting off communication?

---

Note to developers:
CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
So why complicate your code AND MAKE MY JOB HARDER??!:crazy:

Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.

Ryan007 SSCrazy Eights Points: 8275 More actions · Answer 2

Failover is happening within a minute. I am testing by stopping the SQL server services of principal instance.

Ryan
//All our dreams can come true, if we have the courage to pursue them//

Perry Whittle SSC Guru Points: 234013 More actions · Answer 3

instead of stopping services test by interrupting the networking on the principal. You could do this by disabling the network card

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

"Ya can't make an omelette without breaking just a few eggs" 😉

OnTheNumbers Old Hand Points: 390 More actions · Answer 4

A little thread drift:

I know you can alter the timeout value for a particular database.

Can someone confirm if it is possible to change the default for all mirrored dbs (including future mirrored dbs)

1azeem.baig Valued Member Points: 61 More actions · Answer 5

Hey, I saw your post on http://www.sqlservercentral.com/Forums/Topic1193453-391-1.aspx, and I thought you might have some insight for me. The previous Admin left no documentation, and changed a SQL timeout to 15 minutes. Excuse me if I don't properly explain, but he set the timer because SQL was having disconnection issues. SQL1 would then failover to SQL2, and the issue would resolve itself when it failed back over.

I'm completely unaware of how to change the timeout. Where would I go? If you could also explain to me a little more information about failover clusters. It's my first job as an admin.