Unable to kill CXPACKET process in SQL 7 SP3... 1

[Dilyias]

Hello,

We are running SQL7 SP3 on Win2k sp2 (2 cpus) and I have run into a process (wait type: CXPACKET, command: SELECT) that is blocking other processes, that I cannot kill. It has been up for about a week (I just got back from xmas vacation) . When I go to locks/object the spid is listed twice (parallel execution?) . Do I have any choice other than restart the sql services? This is the first time I've run into this type of thing since upgrading to SP3..

Thanks,

Eric

 

[tlbroadbent]

Here is some info from the SQL FAQ by Neil Pike.

Q. What does a wait type of CXPacket or Exchange mean?

A. You will get this only with versions of SQL that support parallel queries, i.e. SQL 7 and later. It means that one thread of the query is waiting for a message packet from another, and the one it is waiting on is either blocked by a traditional cause or has hit some sort of parallelism bug.

CXPacket means it is waiting on a data packet - i.e. the results of an internal query is being passed. Exchange means that it is waiting on a control packet - i.e. waiting for a child/sibling process to tell you that it is finished.

... You should be able to work-around the problem by adding (MAXDOP=1) as a query hint which will prevent the query being parallelized. (emphasis added)

If the spid doesn't respond to the Kill command, you'll need to stop and restart the SQL Server service. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.