Seem to have an issue with a rogue spid, details as follows;
Numerous entries in SQL log relating to-
2008-09-04 10:28:13.99 spid123 WaitForChildren: Children persisting. Retry: 68144
SP_who returns details of SPID 123 - Database = Master, status sleeping, not processing in 16 days, wait type = EC
SPID 123 is sleeping and has not done any processing in 16 days
SPID 123 was selecting some data from a temporary table as follows.
select replace(cast(TextData as varchar), 'Deadlock Chain SPID = ', '') FROM #deadlocktrace where textdata like '%Deadlock Chain SPID%' union select SPID FROM #deadlocktrace where textdata like '%Deadlock Cha
Kill 123 results in
Server: Msg 6107, Level 14, State 1, Line 1
Only user processes can be killed.
Implication is that spid 123 is a system process(?) - which it isn't
My questions are, what does WaitForChildren: Children persisting. Retry actually mean? and how can I kill SPID 123?
Any suggestions gratefully received.