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/showpost.aspx?postid=441900&siteid=1
http://groups.google.com/group/microsoft.public.sqlserver.server/browse_thread/thread/9211508f9b08072d/1de7d680cfd4d127?lnk=st&q=&rnum=2&hl=en#1de7d680cfd4d127
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.
--
KeithMicrosoft 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/prodtechnol/sql/2005/technologies/dbm_best_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/showpost.aspx?postid=441900&siteid=1
> http://groups.google.com/group/microsoft.public.sqlserver.server/browse_thread/thread/9211508f9b08072d/1de7d680cfd4d127?lnk=st&q=&rnum=2&hl=en#1de7d680cfd4d127
> 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/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/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/subscriptions/support/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/subscriptions/managednewsgroups/default.aspx#notif
> ications
> <http://msdn.microsoft.com/subscriptions/managednewsgroups/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/subscriptions/support/default.aspx>.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Hello Keith,
Yes. You shall use it on both primary and the mirror. As for dump file,
will you send me an email with the latest sql error log file at
petery@.microsoft.com, and I may provide a ftp workspace via your email
address so that you could upload the dump file. I will check if the issue
is related to database mirroring,
Since dump analysis has to be done by contacting Microsoft Product Support
Services. Therefore, we probably will not be able to resolve the issue
through the newsgroups. If we could solve the issue efficently in
newsgroup, I recommend that you open a Support incident with Microsoft
Product Support Services so that a dedicated Support Professional can
assist with this case. If you need any help in this regard, please let me
know.
For a complete list of Microsoft Product Support Services phone numbers,
please go to the following address on the World Wide Web:
http://support.microsoft.com/directory/overview.asp
If you have any update or questions, please feel free to let's know.
Thanks.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||Peter, I modified the startup parameters on all three servers - primary,
mirror, and witness as you recommended (adding -g512 - separated from the
other params by a semicolon). I am still unable to add any more databases to
the mirroring setup on these boxes.
I just tried configuring a small database (13MB). When I issued the ALTER
DATABASE command on the mirror to specify the partner the command just ran
and ran and ran and ran. In fact, it is still running as I type this
message. It has been running for 38 minutes and counting.
I just tried calling Microsoft Support because I want to open a support case
for the problem that we are experiencing with mirroring. The response that I
received from the Microsoft employee who took my information was "We are only
offering support on database mirroring technologies to TAP customers."
I find this statement unbelievable. Database mirroring should be fully
supported by Microsoft now that SQL Server 2005 SP1 is out and the feature is
fully enabled without trace flags. I asked the support person (the one who
told me about the support for database mirroring) for their name - just in
case you need to do any followup with them.
Do you mind if I contact you at your Microsoft email address that you
provided so that we can get the ball rolling on finding a fix for this issue?
Thanks
--
Keith
"Peter Yang [MSFT]" wrote:
> Hello Keith,
> Yes. You shall use it on both primary and the mirror. As for dump file,
> will you send me an email with the latest sql error log file at
> petery@.microsoft.com, and I may provide a ftp workspace via your email
> address so that you could upload the dump file. I will check if the issue
> is related to database mirroring,
> Since dump analysis has to be done by contacting Microsoft Product Support
> Services. Therefore, we probably will not be able to resolve the issue
> through the newsgroups. If we could solve the issue efficently in
> newsgroup, I recommend that you open a Support incident with Microsoft
> Product Support Services so that a dedicated Support Professional can
> assist with this case. If you need any help in this regard, please let me
> know.
> For a complete list of Microsoft Product Support Services phone numbers,
> please go to the following address on the World Wide Web:
> http://support.microsoft.com/directory/overview.asp
> If you have any update or questions, please feel free to let's know.
> Thanks.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
>
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
> ======================================================>
>|||Hello Keith,
Thank you for your feedback. Please contact me at petery@.microsoft.com so I
could follow up the issue with you and I also think this shall be supported
by MS PSS. I look forward to your reply.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================