Database Mirroring - unable to mirror 59th database

As the subject suggests, I am unable to mirror my 59th database.
(2) database servers
HP ML370 G4
Dual Xeon 3.4Ghz, 8GB RAM
Windows 2003 R2 Enterprise
SQL Server 2005 Standard - patched to 2153
(1) watch server
HP DL 360 G4
Xeon 3Ghz, 2GB RAM
Win2k3 Standard
SQL Server Express - patched to 2153
I was able to restore the database and the transaction log to the desired
mirror. Then I indicated what the partner server should be (via T-SQL).
Then I switched to my SSMS query window that was connected to the primary
server and executed the commands to set the partner, mirror, and alter the
partner timeout.
The database was mirrored, but then I noticed a sign of trouble within the
Database Mirroring Monitor. One of the databases that was already being
mirrored switched status to "(Principal, Disconnected)"
I executed the following query to check the status:
select mirroring_state_desc, count(*)
From sys.database_mirroring
group by mirroring_state_desc
Here are the results:
mirroring_state_desc
-- --
NULL 60
DISCONNECTED 1
SYNCHRONIZED 58
I wanted to make sure that the database mirroring monitoring job (called by
SQL Server Agent) was not part of the problem, so I stopped monitoring by
calling this stored procedure: sp_dbmmonitordropmonitoring
and I waited... No change in status.
I decided to stop mirroring on the newly added database. I used the
following command to stop mirroring:
ALTER DATABASE foo SET PARTNER OFF
and the other database (the one that had a status of Principal, Disconnected
synchronized and came back online.
In researching the problem I discovered the following posts:
http://forums.microsoft.com/MSDN/sho...41900&siteid=1
http://groups.google.com/group/micro...d680cf d4d127
It seems as though others are having the same issue. How can I mirror more
databases on one server? This particular server has 120 databases and I
would like to get that number to 200 before we even start thinking about a
new server.
Keith
Microsoft recommends you limit the number of databases you are mirroring to
10 or so.
Based on the resource (memory and threads) utilization by database
mirroring, it is a best practices recommendation to mirror not more than 10
databases in one instance. The recommended limit of 10 is only approximate
and not an exact number. The limit can vary depending upon your application
and workload.
From -
http://www.microsoft.com/technet/pro...est_pract.mspx
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Keith_MSDN" <Keith_MSDN@.newsgroup.nospam> wrote in message
news:71B7CAFA-EAC2-4A1E-A781-0C8D479218AC@.microsoft.com...
> As the subject suggests, I am unable to mirror my 59th database.
> (2) database servers
> HP ML370 G4
> Dual Xeon 3.4Ghz, 8GB RAM
> Windows 2003 R2 Enterprise
> SQL Server 2005 Standard - patched to 2153
> (1) watch server
> HP DL 360 G4
> Xeon 3Ghz, 2GB RAM
> Win2k3 Standard
> SQL Server Express - patched to 2153
> I was able to restore the database and the transaction log to the desired
> mirror. Then I indicated what the partner server should be (via T-SQL).
> Then I switched to my SSMS query window that was connected to the primary
> server and executed the commands to set the partner, mirror, and alter the
> partner timeout.
> The database was mirrored, but then I noticed a sign of trouble within the
> Database Mirroring Monitor. One of the databases that was already being
> mirrored switched status to "(Principal, Disconnected)"
> I executed the following query to check the status:
> select mirroring_state_desc, count(*)
> From sys.database_mirroring
> group by mirroring_state_desc
> Here are the results:
> mirroring_state_desc
> -- --
> NULL 60
> DISCONNECTED 1
> SYNCHRONIZED 58
>
> I wanted to make sure that the database mirroring monitoring job (called
> by
> SQL Server Agent) was not part of the problem, so I stopped monitoring by
> calling this stored procedure: sp_dbmmonitordropmonitoring
> and I waited... No change in status.
>
> I decided to stop mirroring on the newly added database. I used the
> following command to stop mirroring:
> ALTER DATABASE foo SET PARTNER OFF
> and the other database (the one that had a status of Principal,
> Disconnected
> synchronized and came back online.
>
> In researching the problem I discovered the following posts:
> http://forums.microsoft.com/MSDN/sho...41900&siteid=1
> http://groups.google.com/group/micro...d680cf d4d127
> It seems as though others are having the same issue. How can I mirror
> more
> databases on one server? This particular server has 120 databases and I
> would like to get that number to 200 before we even start thinking about a
> new server.
> --
> Keith
|||Hello Keith,
I understand that when you tried to configure more than 58 databases to
mirroring, it failed with disconnected status. If I'm off-base, please
let's know.
Based on my research, this issue seems to be related to virutal memory
allocation issue. Database mirroring sessions consume much virutal memroy
per session. You may want to check if you could find "Failed Virtual
Allocate Bytes" in SQL error log. If so, you may want to increase
memtoleave area by setting -g512 as start parameter to see if this could
workarond the issue. Please see the following article for details on
memeorytoleave area.
http://msdn2.microsoft.com/en-us/library/ms190737.aspx
You may also want to consider 64-bit as well, and This would only be an
issue on x86 due to memory limitation of X86 platform.
If this workaround does not meet your requirement, or you have any
concerns, please get SQL Error log, and send it to me at
petery@.microsoft.com, we will look into this problem further. We look
forward to your reply. Thank you!
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscripti...ult.aspx#notif
ications
<http://msdn.microsoft.com/subscripti...s/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscripti...t/default.aspx>.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Thanks Peter. Yes, I want to mirror more than 58 databases on one server.
Upgrading to 64 Bit version of SQL (and Windows) isn't an option at this
time.
I looked within the SQL Server Log on the primary, mirror, and witness and I
did NOT find the Failed Virtual Allocate Bytes error. I even used
AgentRansack to search all the log file in the directory. I could not find
any hits for failed virtual (or virtual).
When searching the log files on the mirror I noticed that There is one SQL
Dump in the directory. Let me know if you want me to email the information.
The .mdmp file is 10MB.
I will try increasing memtoleave via the -g startup param. I assume that I
will need to modify this value on the primary and the mirror, is that
correct? This solution will require downtime, so I need to get the ball
rolling on scheduling a brief outage.
Keith
"Peter Yang [MSFT]" wrote:

> Hello Keith,
> I understand that when you tried to configure more than 58 databases to
> mirroring, it failed with disconnected status. If I'm off-base, please
> let's know.
> Based on my research, this issue seems to be related to virutal memory
> allocation issue. Database mirroring sessions consume much virutal memroy
> per session. You may want to check if you could find "Failed Virtual
> Allocate Bytes" in SQL error log. If so, you may want to increase
> memtoleave area by setting -g512 as start parameter to see if this could
> workarond the issue. Please see the following article for details on
> memeorytoleave area.
> http://msdn2.microsoft.com/en-us/library/ms190737.aspx
> You may also want to consider 64-bit as well, and This would only be an
> issue on x86 due to memory limitation of X86 platform.
> If this workaround does not meet your requirement, or you have any
> concerns, please get SQL Error log, and send it to me at
> petery@.microsoft.com, we will look into this problem further. We look
> forward to your reply. Thank you!
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Community Support
> ==================================================
> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscripti...ult.aspx#notif
> ications
> <http://msdn.microsoft.com/subscripti...s/default.aspx>.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> <http://msdn.microsoft.com/subscripti...t/default.aspx>.
> ==================================================
> This posting is provided "AS IS" with no warranties, and confers no rights.
>