All schedulers on Node 0 appear deadlocked due to a large number o

Hi,
I've noticed the following error message in the event viewer being reported
by SQL Server for a site that we've had online for several months without
incident. However, every so often now, SQL server is locking up and it seems
to be occurring after this error message:
All schedulers on Node 0 appear deadlocked due to a large number of worker
threads waiting on MSSEARCH
I can't seem to find any information on the specifics of what this error
means (or even the error number) or any recommendations for resolving it.
I'm presuming that this could be caused by a code issue, however, since there
Full Text Search is being called by itself (in the application) when it is
used, I don't see how this could be causing a deadlock in the traditional
sense. It also is weird that this seems to happen all at once, go away when
the server is restarted and then come back after a period of time (usually
during a period of low usage, like at night or over a weekend) ...
Thanks,
JeremyHello Jeremy,
Based on my experience, this problem might be a known issue fixed in SP1.
some of xp(name started with sql_OA) in SQL has CoInitialize leak that
causes FTS related issue. Full Text threads all waiting to be signaled,
with no apparent thread to signal them, causing a SQL Server Scheduler to
become hung.
Do you have SQL 2005 sp2 installed? If not, please apply SP2 to see if the
issue is resolved. If you have any update, please feel free to let's know.
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.|||Hi Peter,
We do have SP2 installed, already ...
Thanks!
""Peter Yang[MSFT]"" wrote:
> Hello Jeremy,
> Based on my experience, this problem might be a known issue fixed in SP1.
> some of xp(name started with sql_OA) in SQL has CoInitialize leak that
> causes FTS related issue. Full Text threads all waiting to be signaled,
> with no apparent thread to signal them, causing a SQL Server Scheduler to
> become hung.
> Do you have SQL 2005 sp2 installed? If not, please apply SP2 to see if the
> issue is resolved. If you have any update, please feel free to let's know.
> 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 Jeremy,
Since you have SP2 installed, I suspect the problem might be caused by your
code related to FTS. Since FTS is deadlock due to this error, SQL Server
sessions waiting on MSSEARCH waittype, and some unresponsiveness in SQL
Server itself.
You need to restart FTS and/or SQL service to work around the problem. If
the issue persists, a system reboot is necessary. If you'd like to know the
root cause of the problem, you may need to analyze memory dumps, this work
has to be done by contacting Microsoft Product Support Services. Therefore,
we probably will not be able to resolve the issue through the newsgroups.
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
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
=====================================================
When responding to posts, please "Reply to Group" via your
newsreader so that others may learn and benefit from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi,
Why wouldn't the deadlock manager be resolving the issue, if it is just a
deadlock issue?
Thanks,
Jeremy
""Peter Yang[MSFT]"" wrote:
> Hello Jeremy,
> Since you have SP2 installed, I suspect the problem might be caused by your
> code related to FTS. Since FTS is deadlock due to this error, SQL Server
> sessions waiting on MSSEARCH waittype, and some unresponsiveness in SQL
> Server itself.
> You need to restart FTS and/or SQL service to work around the problem. If
> the issue persists, a system reboot is necessary. If you'd like to know the
> root cause of the problem, you may need to analyze memory dumps, this work
> has to be done by contacting Microsoft Product Support Services. Therefore,
> we probably will not be able to resolve the issue through the newsgroups.
> 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
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
>
> =====================================================> When responding to posts, please "Reply to Group" via your
> newsreader so that others may learn and benefit from this issue.
> ======================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Hello Jeremy,
This is not a simple deadlock situation that could be resolved by SQL
engine itself.
When the lock monitor initiates deadlock search for a particular thread, it
identifies the resource on which the thread is waiting. The lock monitor
then finds the owner(s) for that particular resource and recursively
continues the deadlock search for those threads until it finds a cycle. A
cycle identified in this manner forms a deadlock.
However, the deadlock is caused by FTS which is not a component inside SQL
engine. Therefore, it's more like a distributed deadlock that could not be
resolved by SQL engine itself.
Please understand above is just a suspect and you need to contact MS PSS to
analyze memory dump so taht you may get more clues on this issue. Thank
you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
=====================================================
When responding to posts, please "Reply to Group" via your
newsreader so that others may learn and benefit from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Peter,
Thanks for that clarification. Out of curiosity, since this appears to be
able to hang FTE for all databases (and the SQL server itself, in entirely),
do you know how hosting providers (with multiple clients each writing
uncontrollable queries) handle this issue?
Is there a particularly good way to detect this problem automatically and
restart FTE (since the process still looks to be in a normal state, even
while it is actually hung ...)
Thanks,
Jeremy
""Peter Yang[MSFT]"" wrote:
> Hello Jeremy,
> This is not a simple deadlock situation that could be resolved by SQL
> engine itself.
> When the lock monitor initiates deadlock search for a particular thread, it
> identifies the resource on which the thread is waiting. The lock monitor
> then finds the owner(s) for that particular resource and recursively
> continues the deadlock search for those threads until it finds a cycle. A
> cycle identified in this manner forms a deadlock.
> However, the deadlock is caused by FTS which is not a component inside SQL
> engine. Therefore, it's more like a distributed deadlock that could not be
> resolved by SQL engine itself.
> Please understand above is just a suspect and you need to contact MS PSS to
> analyze memory dump so taht you may get more clues on this issue. Thank
> you.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
>
> =====================================================> When responding to posts, please "Reply to Group" via your
> newsreader so that others may learn and benefit from this issue.
> ======================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Hello Jeremy,
Thank you for your reply.
When the issue occurs, you should be able to find Sysprocesses shows lots
of queries waiting on MSSEARCH. To temporarily work around the issue, you
may want to develope some monitor tools to check the status of
Sysprocesses. If you find too many Sysprocesses are waiting for MSSEARCH,
you may try to restart FTS service to see if the issue is not solved. If
not, it should notify admin to manually do some operations.
As I mention, to find the root cause of the issue, it's suggested that you
contact MS PSS for dump analysis. If you have any further qusetions or
concerns on this, please feel free to let's know. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
=====================================================Please note that the newsgroups are staffed weekdays with a goal to provide
ONE BUSINESS DAY RESPONSE to all posts.
If this response time does not meet your needs, please contact CSS for more
immediate assistance:
http://support.microsoft.com/default.aspx?scid=fh;EN-US;OfferProPhone#faq607
<http://support.microsoft.com/default.aspx?scid=fh;EN-US;OfferProPhone>.
Feedback on your service and satisfaction can be posted to:
from the web interface: Partner Feedback
from your newsreader: microsoft.private.directaccess.partnerfeedback.
We look forward to hearing from you!
======================================================When responding to posts, please "Reply to Group" via your
newsreader so that others may learn and benefit from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Jeremy and Peter,
Any progress on this issue? One of my colleagues just stumbled upon
the same bug/feature. Should we open contact PSS?
Thanks!
On Mar 26, 5:54 am, pet...@.online.microsoft.com ("Peter Yang[MSFT]")
wrote:
> Hello Jeremy,
> Thank you for your reply.
> When the issue occurs, you should be able to find Sysprocesses shows lots
> of querieswaitingonMSSEARCH. To temporarily work around the issue, you
> may want to develope some monitor tools to check the status of
> Sysprocesses. If you find too many Sysprocesses arewaitingforMSSEARCH,
> you may try to restart FTS service to see if the issue is not solved. If
> not, it should notify admin to manually do some operations.
> As I mention, to find the root cause of the issue, it's suggested that you
> contact MS PSS for dump analysis. If you have any further qusetions or
> concerns on this, please feel free to let's know. Thank you.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> =====================================================> Please note that the newsgroups are staffed weekdays with a goal to provide
> ONE BUSINESS DAY RESPONSE toallposts.
> If this response time does not meet your needs, please contact CSS for more
> immediate assistance:http://support.microsoft.com/default.aspx?scid=fh;EN-US;OfferProPhone...
> <http://support.microsoft.com/default.aspx?scid=fh;EN-US;OfferProPhone>.
> Feedback on your service and satisfaction can be posted to:
> from the web interface: Partner Feedback
> from your newsreader: microsoft.private.directaccess.partnerfeedback.
> We look forward to hearing from you!
> ======================================================> When responding to posts, please "Reply to Group" via your
> newsreader so that others may learn and benefit from this issue.
> ======================================================> This posting is provided "AS IS" with no warranties, and confers no rights.