April 2, 2009 at 11:37 am
I have SQL 2005 mirroring set up and working since long time.
I have 3 databases mirrored on one server.
I have one witness server with automatic failover facility
yesterday suddenly failover occured for one database and when i check the error
log files on mirror server i was able to see this message
Error: 1479, Severity: 16, State: 1.
The mirroring connection to "TCP://server.company.org:5022" has timed out for database "Databasename" after 10 seconds without a response.
Check the service and network connections.
The i check Database mirroring monitor and i was suspecting more transactions at that time
but surprisingly there were not many transactions before this happened.
I got same results from EXEC sp_dbmmonitorresults Databasename,9,0
Also if this is a problem with network then it should affect
all 3 databases and not only 1 right?
This happened in the past as well in my environment.
Any hints?
Thanks
April 28, 2010 at 3:41 pm
Hi, did you ever get a resolution to your issue? We are having the same exact problem.
thanks!
April 29, 2010 at 9:42 am
What I did to resolve the issue was,
Select * from msdb.sys.database_mirroring
you can see a column named mirroring_connection_timeout,
the default value in that column is 10 sec but due to intermittent
network issues in my environment, I set this value to 30 sec
using following command
alter database test
set partner timeout 30
I was able to see the improvements after this. It worked for me.
But every time after failover this value again gets set to 10 sec
so you need to change it. Let me know that works for you or u have any other
interesting thing.
April 29, 2010 at 10:31 am
dallas13 (4/29/2010)
I was able to see the improvements after this. It worked for me.But every time after failover this value again gets set to 10 sec
so you need to change it.
You could set up a job scheduled to run whenever the Agent service restarts that automatically runs that code for you. It should help.
June 8, 2010 at 9:26 pm
Brandie Tarvin (4/29/2010)
dallas13 (4/29/2010)
I was able to see the improvements after this. It worked for me.But every time after failover this value again gets set to 10 sec
so you need to change it.
You could set up a job scheduled to run whenever the Agent service restarts that automatically runs that code for you. It should help.
Hi,
Having been burnt several times already by the error in the OP, I set the timeout to 60 seconds.
Is there a downside to setting it that high?
Also, I am not clear on when the timeout value gets reset back to the default of 10 sec.
Is it when the SQL instance gets restarted?
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
June 9, 2010 at 4:20 am
Marios Philippopoulos (6/8/2010)
Having been burnt several times already by the error in the OP, I set the timeout to 60 seconds.Is there a downside to setting it that high?
I don't use mirroring, so I can't answer that question. But my guess is that the only problem would be that you'll be "down" for at least a minute before you notice *if* the source server starts having problems. Which, depending on your availability needs, might not actually be a problem.
Also, I am not clear on when the timeout value gets reset back to the default of 10 sec.
Is it when the SQL instance gets restarted?
That's a safe bet. When a cluster fails over, the services do restart. But the OP was specifically referencing a mirrored failover, so I'm assuming the same thing happens - I.E. that the services pick up on the new server...
Which gets me to thinking that maybe the value isn't actually resetting itself so much as it needs to be specifically set on both the mirrored server and the source server and maybe then it'll stick?
Best way to be sure, though, is to create a job that updates that value and the job runs on the startup of SQL Agent Service.
June 9, 2010 at 4:29 am
Brandie Tarvin (6/9/2010)
Best way to be sure, though, is to create a job that updates that value and the job runs on the startup of SQL Agent Service.
Thank you, I'll make sure to do that.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
June 9, 2010 at 10:27 am
One more question: can the default mirroring timeout be modified even when in High-Performance (Async) mode?
On BOL it is stated that it can only be changed from the 10-sec default value only in High-Safety (Sync) mode.
My db is on Async mode, and when I changed it and queried the sys.database_mirroring view I saw that indeed it had changed from the default value.
The question is, is it really in effect even in Async mode?
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
June 9, 2010 at 11:11 am
I don't use Data Mirroring so I couldn't even begin to answer that question. Sorry.
June 10, 2010 at 7:36 am
Your timeout value should NOT be getting reset when the mirror fails over. What is your SQL version/build #?
You can change the timeout value in any mode, but the value is used for determining when to trigger an automatic failover, so it does not have any effect when running asynchronously (high-performance) because automatic failover is not possible.
Marios, the downside to setting the timeout to 60 seconds is that if the database becomes unresponsive, the it will sit there for 60 seconds waiting before it performs the failover. So basically, you are adding 1 minute to the failover time if the principal database is not responding. This does not affect a failover because the server is down. If the whole server crashes (or some other hard error), a failover will be triggered immediately and not wait for the timeout.
Just so everyone understands how the timeout works: the partners ping each other once every second. The timeout value is the number of successive ping attempts that must fail before a failover occurs. So if the timeout is 10, it's not a single attempt that fails after 10 seconds. It's 10 individual attempts in a row that fail.
June 11, 2010 at 10:52 am
Robert Davis (6/10/2010)
Your timeout value should NOT be getting reset when the mirror fails over. What is your SQL version/build #?You can change the timeout value in any mode, but the value is used for determining when to trigger an automatic failover, so it does not have any effect when running asynchronously (high-performance) because automatic failover is not possible.
Marios, the downside to setting the timeout to 60 seconds is that if the database becomes unresponsive, the it will sit there for 60 seconds waiting before it performs the failover. So basically, you are adding 1 minute to the failover time if the principal database is not responding. This does not affect a failover because the server is down. If the whole server crashes (or some other hard error), a failover will be triggered immediately and not wait for the timeout.
Just so everyone understands how the timeout works: the partners ping each other once every second. The timeout value is the number of successive ping attempts that must fail before a failover occurs. So if the timeout is 10, it's not a single attempt that fails after 10 seconds. It's 10 individual attempts in a row that fail.
I noticed that my snapshots were failing when the 10-sec timeout window was exceeded, and that was affecting my reporting/standby environment.
Here is the error I got on snapshot creation:
(Message 5060) Nonqualified transactions are being rolled back. Estimated rollback completion: 100%.
I'm in async mode, but setting the timeout to something higher than 10 sec seemed like a good way to lower the risk of this happening again.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
June 11, 2010 at 11:15 am
That's not an error message. Every time you create a snapshot, it has to run crash recovery on the snapshot because it may have transactions in progress when the snapshot is created. This means any transactions that are not committed when the snapshot is created gets rolled back. Only in the snapshot, not in the live database. The live copy of the database is not affected.
If you have a lot of active transactions in the database or even 1 long running transaction in the database, the snapshot creation could take a really long time. It is absolutely normal to see a message that transactions are being rolled back when you create the snapshot.
June 11, 2010 at 12:39 pm
Robert Davis (6/11/2010)
That's not an error message. Every time you create a snapshot, it has to run crash recovery on the snapshot because it may have transactions in progress when the snapshot is created. This means any transactions that are not committed when the snapshot is created gets rolled back. Only in the snapshot, not in the live database. The live copy of the database is not affected.If you have a lot of active transactions in the database or even 1 long running transaction in the database, the snapshot creation could take a really long time. It is absolutely normal to see a message that transactions are being rolled back when you create the snapshot.
My snapshot-creation job actually failed with that message after a few minutes of running, and on the principal I got this error multiple times in the SQL ERRORLOG:
The mirroring connection to "TCP://xxxxxxxx:7024" has timed out for database "xxxxx" after 10 seconds without a response. Check the service and network connections.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
June 13, 2010 at 7:56 pm
If your snapshot failed, that's not the reason. You need to keep looking to determine the correct reason.
June 14, 2010 at 4:39 am
Are your servers within the same domain and both inside the firewall?
If not, is your firewall open to the ports being used in the mirroring?
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply