?
Solved

SQL Server 2005 Mirroring time out issue

Posted on 2010-11-10
5
Medium Priority
?
1,330 Views
Last Modified: 2012-05-10
Hi,

We are currently using SQL Server 2005 to mirror 16 databases using synchronous mirroring with automatic failover. Every few months the mirror server and the witness server time out against the primary server causing some but not all of the databases to failover.

I've had performance monitor running when this issue has occurred and have not seen any spikes or unusual values which could suggest a problem on the primary server (including network interface counter). I also have Idera SQL diagnostic Manager monitoring the primary server and this has not shown any issues on the Databases or any sudden increases in requests etc.

The first error code seen in the SQL Server logs on the primary server is
Error: 1479, Severity: 16, State: 2.

The first error code seen in the SQL Server logs on the mirror server is
Error: 1479, Severity: 16, State: 1.

This suggests a network problem however there was an engineer RDP'd onto the primary server at the time and didn't experience any connection issues. We also haven't seen any problems or errors on the switches connecting the servers.

Is any one else having this kind of issue with mirroring? Any suggestions on what it could be and how to fix it?

We are using SQL Server 2005 Enterprise build 9.0.3054 on the primary and mirror servers
and SQL Server 2005 Express build 9.0.3042 on the witness server
0
Comment
Question by:ccfcfc
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 10

Expert Comment

by:Humpdy
ID: 34100056
Hi,

I have a similar setup.
Are you using any Virtualisation ?

I had to install this hotfix on both my server.
http://support.microsoft.com/kb/937745

maybe you can search your logs to see if you got any of these errors.

Are the heavy transaction systems, with a lot of throughput ?

I would also recommend patching up your SP to a min of
Microsoft SQL Server 2005 - 9.00.4035.00 (X64)   Nov 24 2008 16:17:31   Copyright (c) 1988-2005 Microsoft Corporation  Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)


Also, please see this
http://support.microsoft.com/kb/947462

You can increase the timeout, default is 10 seconds,
I wouldn't recommend this but you could do it for test purposes to rule out the network.
If you do change it over 10 seconds, any time the SQL Service is restarted, it defaults back to 10 seconds so keep that in mind.
0
 

Author Comment

by:ccfcfc
ID: 34100338
Hi,

We're not using any virtualisation on any of the 3 servers.

Yes I'd say its quite a heavy transaction system, around 2800 batch requests/sec and 4000 Transactions/sec on the primary server.

I've been waiting for internal resource to get SP3 installed as this has been recommended by microsoft to fix other issues we've had.

I had thought about increasing the timeout but had concerns this was just masking the issue.  
0
 
LVL 10

Accepted Solution

by:
Humpdy earned 2000 total points
ID: 34100376
You're right, I think it is masking the issue,
we had to do it here, just to strike it off the list as the network been the source of the problem.

I think the hotfixes and patched up correctly will work.
I had all of the above errors and once patched up they went away.

We have 17 db's in mirroring, and I'd say only 2 or 4 max would flip over. It was very annoying.
0
 

Author Comment

by:ccfcfc
ID: 34100900
That does sound exactly like the problem we are having and what I thought would be the solution but my manager wanted a second opinion to help push for the resources to be allocated

Thanks for your help
0
 
LVL 10

Expert Comment

by:Humpdy
ID: 34100936
good stuff, no worries
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article is an update and follow-up of my previous article:   Storage 101: common concepts in the IT enterprise storage This time, I expand on more frequently used storage concepts.
The article will include the best Data Recovery Tools along with their Features, Capabilities, and their Download Links. Hope you’ll enjoy it and will choose the one as required by you.
To efficiently enable the rotation of USB drives for backups, storage pools need to be created. This way no matter which USB drive is installed, the backups will successfully write without any administrative intervention. Multiple USB devices need t…
This tutorial will walk an individual through the process of installing the necessary services and then configuring a Windows Server 2012 system as an iSCSI target. To install the necessary roles, go to Server Manager, and select Add Roles and Featu…

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question