SQL deadlocks multiple times per day when Windows SharePoint Timer Service is enabled
SQL deadlocks occurring quite often recently which is bringing the entire SharePoint site down for all my users. Get 5586 SQL deadlock errors logged in the server logs. I have been able to attribute the deadlocks to the SP Timer service. Serious. During the midst of these SQL deadlocks, if I disable the SP Timer service...everything goes back to normal immediately. I have confirmed this multiple times. And....if I re-enable the service, the deadlocks immediately come back. Obviously, I need this service Enabled for multiple reasons. Any idea on how to begin troubleshooting this one? How can I determine which timer job is the source of this mess?
November 10th, 2009 4:32pm
Hey, I am guessing ur running sql server 2005 on the backend. Try this andsee if it works. Check if yourSQL Client Configuration protocols are disabled. For this go to Start -> Run and type cliconfig. On this window in the General tab u shud see 'TCP/IP' and 'Named Pipes' enabled and visible on the right side listbox. If it is empty for some reason do enable them. Also check if the "Enable Shared memory protocol" checkbox is disabled and if so enable it as well. Reboot and check if it helps
November 10th, 2009 5:03pm
Yes...SQL 2005. Everything is configured as you wrote. What the heck is the Timer service doing to kill my SQL server?
November 10th, 2009 9:51pm
Have you looked at the health check advice Microsoft provides about this specific error an the steps to correct it? http://technet.microsoft.com/en-us/library/cc561042.aspx Regards John Timney
November 10th, 2009 11:29pm
I appreciate that link. I have actually looked at that article already and went through each of the steps (except for the last one regarding "corruption"). Everything looks to be OK according to the possible causes mentioned in that article. The article mentions a corrupted db, but doesn't explain how your would detect such a thing. I wonder if that is just a catch all for any other possible problems.
November 10th, 2009 11:50pm
I think you can do that in SQL manager, I usually ask the DBA who runs the DBCC CHECKDB commands. Another thing may be thread contention, theres an odd issue with IIS 6 & 7 servers that has that symptom, but not usually that specific error number, rectified in SP2 for MOSS you might want to take a look at it, as unrelated as it seems. http://support.microsoft.com/kb/946517 Regards John Timney
November 11th, 2009 12:15am
For SQL server deadlock issue, we should first find out what SQL statements is locking on what resource with the SQL 2005 profiler Deadlock graph Profiler trace event. For detail, please refer to: http://blogs.msdn.com/bartd/archive/2006/09/09/Deadlock-Troubleshooting_2C00_-Part-1.aspx http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=Troubleshoot%20Deadlocking%20in%20SQL%20Server%202005%20using%20Profiler
November 12th, 2009 7:09am
Hi,What was the resolution of this problem? I'm witnessing a similar problem in a production environment at one of the cusotmers and it looks like the SharePoint site is hung for 15-30 minutes at arbitrary (at least we don't see a relationship yet) time intervals.Thanks in advance,-Yevgen Malyshev
February 10th, 2010 5:39am