Solved

All schedulers on Node 0 appear deadlocked due to a large number of worker threads waiting on LCK_M_S.

Posted on 2010-09-11
5
1,507 Views
Last Modified: 2012-05-10
Hey everyone,

We've been having an issue with our database for the past week that has been making our sharepoint site virtually unavailable around the same time every night.  I've exhausted everything I can think of that would be causing this, so I turn to you for help.  

We're running a small sharepoint server farm, 3 servers.  One MOSS 2007 Front-end, one SQL Server (2005 enterprise), and a third server to handle crawls and service searches.  These are all VM's run through VMWare.  All three servers are configured pretty much the same, with a few minor changes tailored to each servers role.  The basics are this:  4 vCPU's, 16GB of RAM (except the crawl/search server which has 6), and Windows Server 2003 sp2 enterprise.  

First, the we receive the error:
09/11/2010 05:40:14,Server,Unknown,All schedulers on Node 0 appear deadlocked due to a large number of worker threads waiting on LCK_M_S. Process Utilization 1%.

This goes on for anywhere between 3-7 hours, until we get this error:

09/11/2010 06:13:51,spid116,Unknown,The instance of the SQL Server Database Engine cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users. Ask the database administrator to check the lock and memory configuration for this instance<c/> or to check for long-running transactions.                  


at which time the servers are restarted by our night crew.  When the server comes back up it goes through the CHECKDB for the databases and begins a recovery of our content database.

Attached is a trace log from Sql Server Profiler.  I'm baffled and don't know where to go from here.  We've completely turned off the crawl/search functions, ran Database Engine Tuning Analyzer, set a memory limit (10GB) for the SQL engine, and anything else we could think of or could find online to no avail.  Any ideas?
Trace.xml
0
Comment
Question by:1intelsys
  • 2
  • 2
5 Comments
 
LVL 11

Expert Comment

by:JoeNuvo
ID: 33656135
from your given error message. Since there is deadlock.
try run profiler for deadlock.

log following event
(under Locks group)
 - Deadlock graph
 - Lock: Deadlock
 - Lock: Deadlock Chain

pick following item
 SPID, ObjectID, StartTime, TextData, DatabaseName, ApplicationName, ServerName, BinaryData, LoginName

if deadlock happen, profiler should got something appear.
then you can right click at "Deadlock Graph" and choose to "Extract Event Data..."
which is XML file, u can try to read it to see what is the command cause deadlock
0
 
LVL 3

Accepted Solution

by:
1intelsys earned 0 total points
ID: 33688859
Finally figured out what the problem was.  While I was on vacation the setting for "Max Worker Processes" was set to 0.  The deadlocks were from the bottlenecks caused by an insufficient number of worker processes.  The server seemed to get stuck when two timer jobs tried to run simultaneously, causing everything to be put into a waiting status.  Set the "Max Workers Processes" to 255 (4 cpu's) and all appears to be well.
0
 
LVL 9

Expert Comment

by:irinuc
ID: 33689016
Hmm, i though SQL 2005 default setting is 0 and only on specific configurations increasing it is helpful
http://technet.microsoft.com/en-us/library/ms187024(SQL.90).aspx 

(I am only commenting to this, because I encounter SQL deadlocks on various operations and I would be very courious to know if doing this change would help)
0
 
LVL 3

Author Comment

by:1intelsys
ID: 33689465
My knowledge of SQL Server 2005 is pretty basic, but as soon as I changed that setting it resolved my problem of the server going down each night, and I stopped seeing the error I mentioned in the subject line.  The article you referenced stated that a value of 0 would automatically configure the max worker thread value of 256, however when I changed it manually to 255 it fixed my problem.  All I can gather from that is that for some reason the value of 0 was no longer defaulting to 256, giving me a much lower value for that setting.  
0
 
LVL 9

Expert Comment

by:irinuc
ID: 33689522
Thank you; this is good to know; if I may ask only one other question: what is the moss version you are running?
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Sharepoint 2013 Library List View Limitations 9 52
Problem with SqlConnection 4 160
Help with SQL - TOP 10 by date and by group 13 34
SharePoint Online Security 5 48
I thought I'd write this up for anyone who has a request to create an anonymous whistle-blower-type submission form created using SharePoint 2010 (this would probably work the same for 2013). It's not 100% fool-proof but it's as close as you can get…
These days socially coordinated efforts have turned into a critical requirement for enterprises.
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now