September 13, 2010 at 9:44 am
[p]I have a DB server that has several databases mirrored to a sister DB. All the databases have been mirrored now for almost a year, except for one. I have a couple of larger db on there that keep going into paused status. One of them I am able to resume the mirror and it catches up just fine. The largest of the db's does not let me resume the connection. I am at a loss as to why I would lose this connection to this db while the other db's are still connected and mirroring just fine. The logs give no information as to why the db was paused or why the connection cannot continue. [/p]
[p] The configuration looks tight, however if I go through the wizard it will not allow me to confirm the mirror connection (the mirror is unchecked and greyed out). Anyone know what would cause this and what I can do to resolve or troubleshoot this? It seems to be happening on a 3-5 week cycle.[/p]
September 13, 2010 at 11:52 am
I found a solution that I will have to investigate once I can get the mirror DB up and can edit its growth parameters. It talks about the log file exceeding the growth rate when there are network downtimes. Even during a downtime of a couple of minutes the unsent trans log might be exceeding the 10% growth rate that is set. I will test this and respond back after I am able to do some testing.
http://sqlblogcasts.com/blogs/blakmk/archive/2008/06/26/mirroring-issues.aspx
September 13, 2010 at 3:19 pm
In this situation I have been able to resume mirroring by bouncing SQL on the mirror.
How many databases are you mirroring? You may have reached the limit for your infrastructure. Flip some over to log shipping if you can.
You can reset growth factors on the primary, you won't be able to amend it on the mirror. Growth factors are best set in MB rather than percentages.
Index rebuilds could cause this by flooding the network, so its worth trying to stagger this workload.
---------------------------------------------------------------------
September 13, 2010 at 3:49 pm
I am unfamiliar with the term bouncing. What do you mean by this.
I am mirroring 14 db's ranging in size from 40mb to 200GB.
After looking around it does appear to be happening around the same time as the index rebuilds. (Or at least it was in this case.) I will also change the rebuilds to a staggered rebuild schedule see if this helps as well, good insight. It also appears to only happen on the db's that are larger than 2GB. So it is making more sense with the rebuilds. Unfortunately I didn't set these up I am just fixing. With that in mind it might be a good idea to go through the schedules and optimize where I can.
None of the prior DBA's here had any type of redundancy other than backup restores and snapshots (which were disabled). But an 8 hour restore is painful when they are mission critical systems. Ouch. What I am using now is the mirror with bak backups for a dual redundancy and DR plan. Any other suggestions?
September 13, 2010 at 4:03 pm
Please note that MS recommends not to use more than 10 mirroring dbs per instances. Also there is only one DB Mirroring endpoint per instance.
Thank You,
Best Regards,
SQLBuddy
September 13, 2010 at 4:05 pm
mgchris (9/13/2010)
I am unfamiliar with the term bouncing. What do you mean by this.
to bounce - to stop and restart a service, therefore the service goes down, then up again, just like a bounce...................
I am mirroring 14 db's ranging in size from 40mb to 200GB.
It is feasible you are hitting the upper end of what mirroring can handle, particularly if this is a 32bit system. check the logs on the mirror server for any out of memory messages. With he alter database command you can also increase the network timeout value (default is 10 seconds). See 'alter database set timeout'
After looking around it does appear to be happening around the same time as the index rebuilds. (Or at least it was in this case.) I will also change the rebuilds to a staggered rebuild schedule see if this helps as well, good insight. It also appears to only happen on the db's that are larger than 2GB. So it is making more sense with the rebuilds. Unfortunately I didn't set these up I am just fixing. With that in mind it might be a good idea to go through the schedules and optimize where I can.
check out also scripts that only rebuild indexes that are actually fragmented. BOL has one in the sys.dm_db_index_physical_stats section.
None of the prior DBA's here had any type of redundancy other than backup restores and snapshots (which were disabled). But an 8 hour restore is painful when they are mission critical systems. Ouch. What I am using now is the mirror with bak backups for a dual redundancy and DR plan. Any other suggestions?
Only look at logshipping as an alternative for some of the databases.
Ensure your system objects are replicated as well.
---------------------------------------------------------------------
September 13, 2010 at 4:15 pm
[p]Thanks for the 10 per instance. I looked all over for this. It appears that I should really look at a combination of mirror and log shipping. Thanks. [/p]
I will need to take the non-critical db's and institute a log shipping schedule to alleviate resources and potential conflicts.
Thanks for the help. It's always nice to have a second mind to help resolve these issues.
September 13, 2010 at 4:17 pm
Sounds like a plan. Good Luck.
Thank You,
Best Regards,
SQLBuddy
September 13, 2010 at 4:26 pm
The 10 per instance is not a hard and fast rule, its a 'recommendation' only and MS were being conservative.
for me the limit was hit on 24 databases, others have claimed much higher. It depends on load and infrastructure. With 64bit expect even higher limits.
---------------------------------------------------------------------
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply