Symptoms

Diagnoses

Resolution

To resolve this issue must first pinpoint which process was in a high state, the following SQL query does this for you.

SELECT TOP 200 ID, DATALENGTH(State) AS StateSize, Version, StartDate, Originator, Folio, Status
FROM
Server.ProcInst WITH(NOLOCK)
WHERE
Status IN (1, 2)

ORDER BY
DATALENGTH(State) DESC

States in that are in the high 100,000's are normally a cause for concern.


There after depending on which process instance ID's were in a large state we use the following to further pin point which activity is failing here.

SELECT COUNT(1) AS AActivity Instance Count], A.Name
FROM
ServerLog.ProcInst AS P WITH(NOLOCK)
INNER JOIN
ServerLog.ActInst AS AI WITH(NOLOCK) ON P.ID = AI.ProcInstID
INNER JOIN
ServerLog.Act AS A WITH(NOLOCK) ON AI.ActID = A.ID

WHERE
(P.Status IN (1, 2)) AND (P.StartDate = P.FinishDate)
AND
P.ID = 1608201
GROUP BY
A.Name
ORDER BY
Activity Instance Count] DESC

Then to stop the CPU from going to 100% and causing the server to crash, you will need to stop the process instance in a high state. Querying out an update statement against the process ID and setting this to Status = 4 should allow it to be stopped.

You will then have to review the initial process in question as primary reason why CPU consumption occurs. This is normally down to multiple loops in processes or bad IPC configuration.