November 27, 2008 at 2:58 am
Dear All,
Clarification Required for Below Scenario
We have SQL Server 2005 with SP2 on Windows Server 2003 Enterprise Edition with SP2.
No of CPU: 2
Physical Memory: 3 GB
We have enabled -g switch with 500 MB bcoz assemblies are called by application every now and then.
We will get the following error message and transaction will not get responds from SQL Server and timeout.
Error before Restart
All schedulers on Node 0 appear deadlocked due to a large number of worker threads waiting on MSQL_XP. Process Utilization 0%.
And mini dump file has been created with following error
SQL Server Dump Message
2008-11-19 19:40:07.12 Server **Dump thread - spid = 0, PSS = 0x00000000, EC = 0x00000000
2008-11-19 19:40:07.12 Server ***Stack Dump being sent to C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\SQLDump0063.txt
2008-11-19 19:40:07.12 Server * *******************************************************************************
2008-11-19 19:40:07.12 Server *
2008-11-19 19:40:07.12 Server * BEGIN STACK DUMP:
2008-11-19 19:40:07.12 Server * 11/19/08 19:40:07 spid 0
2008-11-19 19:40:07.12 Server * Private server build.
2008-11-19 19:40:07.12 Server *
2008-11-19 19:40:07.12 Server * Non-yielding Scheduler
2008-11-19 19:40:07.12 Server *
2008-11-19 19:40:07.12 Server * *******************************************************************************
2008-11-19 19:40:07.12 Server * -------------------------------------------------------------------------------
2008-11-19 19:40:07.12 Server * Short Stack Dump
2008-11-19 19:40:07.13 spid57 Task (Worker 0x053000E8) was forced to yield 14 times: DBNAME.dbo.procedure Name(Procedure which will cal the assemblies)
2008-11-19 19:40:07.16 Server AppDomain 2 (master.dbo[runtime].1) unloaded.
2008-11-19 19:40:07.26 Server Stack Signature for the dump is 0x0000028A
What MSQL_XP Wait type means
Occurs when a task is waiting for an extended stored procedure to end. SQL Server uses this wait state to detect potential MARS application deadlocks. The wait stops when the extended stored procedure call ends.
Error after Restart
We are getting following error when we restart the server. CLR is not able register properly due to memory pressure. So we are forced to restart once again to get DB back.
Failed to initialize the Common Language Runtime (CLR) v2.0.50727 with HRESULT 0x80004005. You need to restart SQL server to use CLR integration features.
Need Clarification
The problem is already communicated to develeper and they are working on the procedures. Hope driving this problem in below way may give root cause if the procedure or assemblies called by them are really not a root cause of problem.
1 What is meaning of NODE 0 in the Error Message? Does it mean one of the particular CPU in the server? (Bcoz all the error message are having only NODE 0) Whether can we say particular CPU in the server is having problem in yielding the threads?
2 After the restart, why CLR is not able to register properly. Why all the dll loaded in the memory is not cleared. Can we say SQL Server will clear only buffer pool memory and not the MTL Portion of the Memory during the restart? Then how CLR is able to register after the second restart. Kindly note that till we restart the services second time we are keep on getting the CLR Initialization failure error.
Hope scenario has been clarified clearly and Expecting your valuable inputs. Sorry for writing big message
Regards
Kokila K
November 27, 2008 at 1:27 pm
Hi,
1. Node 0 represents one of your CPUs, yes. However, it is not the CPU yielding, it is the CLR that is not yielding.
2. This is more mysterious, I have noticed that sometimes when you restart SQL Server, Private Bytes and Working Set doesn't seem to "reset" (or if it is just the counters). Not sure what is going on and I didn't dig into this but what you can do is to monitor the following counters:
Process: Private Bytes - sqlservr.exe (ALL committed memory; MTL and bpool)
Process: Working Set - Committed memory that maps to physical RAM
SQL Server Memory Manager: Total Server Memory - bpool
A reason for this problem could be that a badly written CLR app is running in a very tight loop without making any API calls. It could also be that there is a resource problem on the server such as memory being paged out or IOs getting stuck.
As you already figured (unless there is a major resource problem on the server), it falls back on the developers of the CLR.
/Elisabeth
elisabeth@sqlserverland.com
MCITP | MCT
http://sqlblog.com/blogs/elisabeth_redei/
http://linkedin.com/in/elisabethredei
November 28, 2008 at 7:28 am
Kokila,
Please find the below post .
http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=1015696&SiteID=1
Is there any Third Party Database Related Software installed like Log Rescue or Monitoring agent?
Regards,
Raj
June 18, 2012 at 2:00 pm
How does this fall back to developers?
Msg 6512, Level 16, State 27, Line 1
Failed to initialize the Common Language Runtime (CLR) v2.0.50727 with HRESULT 0x80004005. You need to restart SQL server to use CLR integration features.
This error happens a lot on the web and nobody has an answer. It looks like memory dropped out from time to time. But if we have no way holding it in the working set, what does Microsoft expect us to do? buy unlimited amount of memory?
Jason
http://dbace.us
😛
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply