June 17, 2009 at 8:01 am
Coming in the past few mornings and seeing one or more databases in 'disconnected' mirroring state.
On the mirror server, the event log has the following two errors.
An error occurred in the service broker message dispatcher. Error: 701, State: 123.
The database mirroring connection terminated. Out of memory sending message for database "TCP://[CAC-SRVR]:5021".
On the priniciple server, I see the following error:
The mirroring connection to "TCP://[PFC-MIRROR]:5021" has timed out for database "CACPLUS" after 10 seconds without a response. Check the service and network connections.
Bouncing the SQL service on the mirror server fixes the problem, but it keeps coming back. Both servers have plenty of free RAM, so I'm not sure what would cause this. Has anyone ever seen anything like this?
The Redneck DBA
June 17, 2009 at 8:17 am
I also just noticed a bunch of these in yesterday's log: Downgraded restore buffers from 4096K to 2048K. (hundreds of them).
I see another post on this site with that exact error, but in the case they discussed there, there were SSIS packages and such running. Mine is a dedicated mirror server with nothing else going on.
The Redneck DBA
June 17, 2009 at 8:21 am
check these links, these are helpful:
http://msdn.microsoft.com/en-us/library/ms190913.aspx
Maninder
www.dbanation.com
June 17, 2009 at 8:36 am
Thanks for the links. That first one is interesting. I saw a couple of others saying they were having backup troubles on the principal server with database mirroring that turned out to be something else. And others that have reported problems with web apps and SSIS packages, etc. causing trouble. (And interestingly I've actually run into a similar problem with Idera's software in the past, but we aren't currently running it on any servers now).
In my case, the principal servers are just fine. The actual errors I'm seeing are all on the mirror server. Looks like the only errors the principal servers have are just reporting that the mirroring session is dead. And our mirror server has nothing but SQL installed on it, and is only being used for mirroring...no SSIS packages, etc. running on it.
What's annoying about it is we've been mirroring for months now, and this problem is just now surfacing. I can't for the life of me think of anything that's changed on the principal or mirror server.
The Redneck DBA
June 17, 2009 at 8:43 am
For teh timeoout error do this:
in Query window: on principal server do this:
ALTER DATABASE "Db name here" SET PARTNER TIMEOUT 30
the dbname witout Quotes:
Maninder
www.dbanation.com
June 17, 2009 at 9:17 am
Mani Singh (6/17/2009)
For teh timeoout error do this:in Query window: on principal server do this:
ALTER DATABASE "Db name here" SET PARTNER TIMEOUT 30
the dbname witout Quotes:
But isn't the timeout error seen on the principal server just a symptom of the issues the mirror server is having?
The Redneck DBA
June 17, 2009 at 9:43 am
What are the SQL Server Service accounts (local or domain accounts)
Are the DB compatibility modes same for both the databases.
What is the service pack levels and SQL server Editions:
What i am betting on is too many virtual log file in your database in question:
Can you do a DBCC LOGINFO(DBNAME) and post the result.
Also post the Database growth factors and initial sizes etc.
Also the sizr of your trans log...
Maninder
www.dbanation.com
June 17, 2009 at 9:44 am
I personally think the answer is: controlling your transaction log files and in turn the trans backps..
Maninder
www.dbanation.com
June 17, 2009 at 10:00 am
Mani Singh (6/17/2009)
What are the SQL Server Service accounts (local or domain accounts)Are the DB compatibility modes same for both the databases.
What is the service pack levels and SQL server Editions:
What i am betting on is too many virtual log file in your database in question:
Can you do a DBCC LOGINFO(DBNAME) and post the result.
Also post the Database growth factors and initial sizes etc.
Also the sizr of your trans log...
I'll try to answer everything...
Service accounts are domain accounts. And since I have other databases also being mirrored besides the one that had issues, I don't see how they could be the issue...in fact I don't know how you would be able to start mirroring at all without proper permissions.
Compatibility mode is 80. (How could they be different on the principal/mirror?)
Both have SP3, Enterprise edition.
The .mdf file grows in 1MB increments, the .ndf files and .ldf files are set to 10% growth. Initial size for the log file shows it was 2MB.
But we do hourly log backups, that file is actually never much bigger than 15-20MB.
As for the DBCC LOGINFO - returns 161 rows. Do you really want all that data?
The Redneck DBA
August 9, 2010 at 4:46 am
i'm having the same problem too eventhough i've increased the timeout to 1200 seconds
August 9, 2010 at 6:06 am
Jason Shadonix (6/17/2009)
Mani Singh (6/17/2009)
What are the SQL Server Service accounts (local or domain accounts)Are the DB compatibility modes same for both the databases.
What is the service pack levels and SQL server Editions:
What i am betting on is too many virtual log file in your database in question:
Can you do a DBCC LOGINFO(DBNAME) and post the result.
Also post the Database growth factors and initial sizes etc.
Also the sizr of your trans log...
I'll try to answer everything...
Service accounts are domain accounts. And since I have other databases also being mirrored besides the one that had issues, I don't see how they could be the issue...in fact I don't know how you would be able to start mirroring at all without proper permissions.
Compatibility mode is 80. (How could they be different on the principal/mirror?)
Both have SP3, Enterprise edition.
The .mdf file grows in 1MB increments, the .ndf files and .ldf files are set to 10% growth. Initial size for the log file shows it was 2MB.
But we do hourly log backups, that file is actually never much bigger than 15-20MB.
As for the DBCC LOGINFO - returns 161 rows. Do you really want all that data?
Hi, you said thasat you regularly backup the log but how often do you backup the database? And is it Full or incremental?
Adam Zacks-------------------------------------------Be Nice, Or Leave
August 9, 2010 at 6:18 am
I'd forgotten all about this thread, sorry.
There were a couple of things that I found and tweaked that fixed our issue without adjusting any mirroring settings.
I finally figured out that another DBA had modified our index rebuild job to do a full rebuild on every index, followed by a reorganize whether it needed it or not. This was causing so much data to be sent from the principal to the mirror (Across a WAN) that mirroring just couldn't keep up. I modified the job to exclude rebuilding indexes on tables with not very many rows, and only do rebuilds when needed, otherwise just reorganize. That drastically reduced the amount of data being sent across the network.
We also found (don't have the source off the top of my head, but I think it was just the BOL entry for database mirroring) that 32-bit mirror servers don't do well with more than 10 or so databases, so we upgraded the mirror server to 64-bit.
Between the two changes all of our errors went away. Once in a blue moon if a massive update / index rebuild occurs the databases will fall into synchronizing status for 15-20 min, but in our environment that is acceptable. (We are using asynchronous mirroring).
The Redneck DBA
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply