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,483 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

The vision: A MegaMenu for a SharePoint portal home page The mission: Make it easy to maintain. Allow rich content and sub headers as well as standard links. Factor in frequent changes without involving developers or a lengthy Dev/Test/Prod rel…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

772 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

10 Experts available now in Live!

Get 1:1 Help Now