August 13, 2008 at 12:25 pm
I've got sql server 2005 version 3152.
for the last few days around 9:30 a query brings the system to it's knees. the following error is in sql log:
All schedulers on Node 0 appear deadlocked due to a large number of worker threads waiting on LCK_M_IS. Process Utilization 0%.
after this error hundreds of logins failed errors (I assume due to lack of available worker threads). eventually the cluster manager restarts the sql service, I assume it can't login and confirm the service is running.
This server has over 100 dbs and 100 users from multiple hosts and servers. Hundreds of connections at any one time with 10-20 active sessions at any given time.
any suggestions how to identify the exact query/queries that kill it?
I've run trace and forwarded on a number of queries to my developers, but they think the queries are fine.
any help or suggestions are appreciated.
thanks
August 13, 2008 at 3:39 pm
I found the below mentioned link that describes the same problem as yours:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=436762&SiteID=1
The most likely cause of the message you recieved is a blocking issue, caused by a client app holding a lock for a long time.
In SQL 2005 you can enable a profiler event to capture blocking events that exceed a specified duration. This set of command will set the interval to 30 seconds
sp_configure 'show', 1
reconfigure
go
sp_configure 'blocked process threshold', 30
reconfigure
go
You will need to use profiler to capture the blocked process report.
Or if you prefer you can use the method outlined in this article to
monitor blocking: http://support.microsoft.com/kb/271509/en-us
Wait Type that you have mentioned:--
Occurs when a task is waiting to acquire an Intend Share lock.
See the sys.dm_tran_locks topc for a lock compatibility matrix.
MJ
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply